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.

Jeremy Wiggins · Accepted Answer · 2011-12-02 12:43:00Z

10

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.

edited Dec 2, 2011 at 12:43

answered Dec 2, 2011 at 12:33

Jeremy Wiggins

7,3196 gold badges44 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nbulba Over a year ago

Jeremy, your advice to set false for CancelSelectOnNullParameter did the trick! You are right about the attribute! Thanks a lot! Really strange behaviour for the default settings...

Mesh Over a year ago

The really annoying thing is that 'trival' examples in the MS documentation don't seem to mention this either

gbs Over a year ago

I think it was working for me for a long time. It's just when I moved my project to 4.5 it stopped working.

AnandMohanAwasthi · Accepted Answer · 2011-12-02 06:27:20Z

0

Try this out.

Create a method which will return db null if 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 which will called the stored procedure and fill the dataset.

public DataSet GetspTest(string pin) {

    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();

            }

}

Now called this method on page_load event
```
if(!IsPostBack)
{   
GvTest();
}
```

If found it useful,please mark it as your answer else let me know...

answered Dec 2, 2011 at 6:27

AnandMohanAwasthi

8397 silver badges19 bronze badges

3 Comments

nbulba Over a year ago

Sorry AnandMohanAwasthi, but my question is about the reasons of such an odd behaviour rather than about possible workarounds.

AnandMohanAwasthi Over a year ago

May be it's because, sql server does not accept the null values. In order to successfully execute the procedure even if parameter is not passed you need either declare the Pin null in stored procedure or pass db.null.

AnandMohanAwasthi Over a year ago

and I forgot to mention that you need to modify the query too. Use COALESCE function.

Collectives™ on Stack Overflow

SqlDataSource and stored procedure call issue

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related