5

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

Although the stored procedure seems to be working fine in Visual Studio 2008 the actual webpage displays no results.

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

Is this the source of my problems or am I missing something else? How would I pass DBNull.Value to a stored proc if a textfield is blank?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user626873
  • 177
  • 4
  • 11

2 Answers2

10

You need to ensure that CancelSelectOnNullParameter is set to false on your SqlDataSource, and that ConvertEmptyStringToNull is true on to needed parameter. It should look something like this in markup:

<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 then one Control that can provide null value and you want to allow only one of them to be null. In that case you must set CancelSelectOnNullParameter to true and use 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;
}

That should allow you to solve your problems.

tpeczek
  • 23,867
  • 3
  • 74
  • 77
  • 1
    Thank you i completely missed that CancelSelectOnNullParameter . – user626873 Feb 21 '11 at 17:26
  • Canceling a call by default just because a parameter is null seems like crazy talk. I lost half a day trying to figure out why my stored procedure wasn't being called – Josh Russo Mar 08 '16 at 13:27
0

you can set the default value to null in your stored procedure so in case the textbox is empty you simply do not have to pass anything, to do so just write = NULL after the parameter type in the stored procedure parameters list.

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
  • You mean @date date = NULL - didnt work. Also tried setting the DefaultValue in the ASP ControlParameter property window to DBNull.Value still nothing – user626873 Feb 21 '11 at 16:03