Problem with SqlDataSource call and stored procedure

Question

I stumbled upon a problem and cannot figure it out myself. Hope someone can help me resolve this.

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 asp.net page with SqlDataSource and GridView controls in the 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 simple. However, if I don’t specify the PIN on the URL ( .../Default.aspx instead of .../Default.aspx?pin=somevalue ) or specify an empty string ( .../Default.aspx?pin= ), there is no call to the stored procedure (I check it with SQL Server Profiler).

Moreover, if I replaced QueryStringParameter with a simple

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

and do not specify the value of DefaultValue, the situation repeats, and calls to the stored procedure are not executed. What is the reason for this behavior?

I am new to asp.net and may be missing something, but I even tried to do the same thing in the program code programmatically and not declaratively, and the result is the same. The only thing I could find out was that in this case the Selecting SqlDataSource event is Selecting , but Selected is not. Maybe some kind of error occurred?

In any case, any help would be greatly appreciated.

Answer 1

The SqlDataSource object has the CancelSelectOnNullParameter property. Its default value is true , so I think the behavior you see is expected, although not obvious. Try setting this property to false .

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

Alternatively, you can find the ConvertEmptyStringToNull property of the Parameter class ( QueryStringParameter extends this) to be useful depending on / if your stored proc processes null values. Its default value is true .

Answer 2

Try it.

• Create a method that returns db null if the parameter is not passed in the stored procedure

   public static object GetDataValue(object o) { if (o == null || String.Empty.Equals(o)) return DBNull.Value; else return o; } 

• Create a method that is called by the stored procedure and populates the data set.

  public DataSet GetspTest (string output) {

    try { DataSet oDS = new DataSet(); SqlParameter[] oParam = new SqlParameter[1]; oParam[0] = new SqlParameter("@Pin", GetDataValue(pin)); oDS = SqlHelper.ExecuteDataset(DataConnectionString, CommandType.StoredProcedure, "spTest", oParam); return oDS; } catch (Exception e) { ErrorMessage = e.Message; return null; } } 

• Now bind the dataset to gridview

   private void GvTest() { DataSet oDsGvspTest = new DataSet(); string pin = Request.QueryString["Pin"]; oDsGvspTest = GetspTest(pin); if (oDsGvspTest.Tables[0].Rows.Count > 0) { Gv.DataSource = oDsGvspTest; Gv.DataBind(); } } 

• This method is now called in the page_load event

   if(!IsPostBack) { GvTest(); } 

If you find this helpful, mark it as your answer, let me know ...