SqlDataSource and stored procedure call issue

Question

I've stumbled upon an issue and can't figure it out on my own. Hope someone could help me resolve it.

So, I have a simple stored procedure in a SQL Server 2005 database

CREATE PROCEDURE spTest
  @pin varchar(128)
AS
BEGIN
  SELECT @Pin as Param
END

and an asp.net page with a SqlDataSource and a GridView control in an application (VS2008)

<asp:SqlDataSource 
  ID="sds2" 
  runat="server" 
  ConnectionString="..."
  SelectCommand="spTest"      
  SelectCommandType="StoredProcedure"
  >
  <SelectParameters>
    <asp:QueryStringParameter Name="pin" QueryStringField="pin" DbType="String"/>
  </SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gv" runat="server" DataSourceID="sds2"></asp:GridView>

As you can see, the code is straightforward. Nevertheless, if I don't bother specify the pin on the url (.../Default.aspx instead of .../Default.aspx?pin=somevalue) or specify an empty line (.../Default.aspx?pin=) there is no any call to the stored procedure (I check it with SQL Server Profiler).

Moreover, if I replace the QueryStringParameter with a simple

<asp:Parameter Name="pin" DbType="String" />

and do not point out DefaultValue value, the situation repeats and no calls to the stored procedure are made. What is the reason of such a behaviour?

I'm quite a new to the asp.net and possibly overlook something, but I even tried to do the same in code-behind file programmatically instead of declaratively and the result is the same. The only thing I could find out is that in such case a Selecting event of the SqlDataSource is fired, but the Selected is not. Maybe some kind of an error happens?

Anyway, any kind of help would be greatly appreciated.

Answer 1

The SqlDataSource object has a property called CancelSelectOnNullParameter. Its default value is true, so I think the behavior you're seeing is expected, albeit not obvious. Try setting this property to false.

<asp:SqlDataSource 
  ID="sds2" 
  runat="server" 
  ConnectionString="..."
  SelectCommand="spTest"      
  SelectCommandType="StoredProcedure"
  CancelSelectOnNullParameter="false"
  >

Additionally, you may find the ConvertEmptyStringToNull property of the Parameter class (QueryStringParameter extends this) to be of some use, depending on if/how your stored proc handles null values. Its default value is true as well.