Passing a blank field value to an ASP.NET C # stored procedure

Question

I want to return all rows from a SQL Server 2008 database table to an SQL data source if the textBox is empty. So I made a stored procedure with an if @date IS NULL .

Although the stored procedure works fine in Visual Studio 2008, the actual web page does not show any results.

I assume that I need to send the DBNull value to the stored proc if textBox.Text == string.Empty . I tried SqlDataSource1.SelectParameters.Add , but it seems that I am getting a conversion error from DBNull.Value to the string.

Is this the source of my problems or am I missing something? How to pass DBNull.Value to a saved process if the text field is empty?

Answer 1

You need to make sure that CancelSelectOnNullParameter is set to false on your SqlDataSource, and that ConvertEmptyStringToNull is true for the required parameter. In the markup, it should look something like this:

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" CancelSelectOnNullParameter="false" SelectCommand="..."> <SelectParameters> <asp:ControlParameter Name="..." ControlID="..." PropertyName="..." DbType="..." ConvertEmptyStringToNull="true"/> ... </SelectParameters> </asp:SqlDataSource> 

The problem will start if you have more than one control that can provide a null value, and you want to allow only one of them to be null. In this case, you should set CancelSelectOnNullParameter to true and use the Selecting event to add DBNull.Value:

 protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { if (String.IsNullOrEmpty(textBox.Text)) ((IDbDataParameter)e.Command.Parameters["@name"]).Value = DBNull.Value; } 

This should allow you to solve your problems.

Answer 2

you can set the default value to null in the stored procedure, so if the text field is empty, you just don’t need to skip anything to do this, just write = NULL after the parameter type in the parameter list of the stored procedure.