How do I pass a null value to a SqlDataSource that is using a stored procedure?

Question

Using .NET 4 and C#. All of these throw an error:

sdsTypeinfo.SelectParameters.Add("@TypeCode", DbType.Boolean, DBNull.Value);

sdsTypeinfo.SelectParameters.Add("@TypeCode", DBNull.Value);

sdsTypeinfo.SelectParameters.Add("@TypeCode", null);

sdsTypeinfo.SelectParameters.Add("@TypeCode", "");

The error indicates that the procedure is not getting any value at all.

Procedure or function 'Typeinfo' expects parameter '@TypeCode', which was not supplied.

Answer

You don't need the @ in the name of the parameter. This is different than adding parameters when calling the stored procedure using a SqlCommand, in which case you want something like this:

cmd.Parameters.AddWithValue("@TypeCode", DBNull.Value);

Also, DBNull.value is an acceptable parameter in the above, but the accepted NULL value when adding SelectParameters to an SqlDataSource is simply "null".

What kind of error does it throw? On the Sql server side or client side? What's exactly the error message? — Icarus
– Icarus, Commented Nov 4, 2011 at 14:27
Is it a typo? The error expects the parameter to be called @pTypeCode and you are passing in @TypeCode... — Bob Vale
– Bob Vale, Commented Nov 4, 2011 at 14:41

James Johnson · Accepted Answer · 2011-11-04 15:10:34Z

1

There should be an option when adding a parameter to convert empty string to null.

<SelectParameters>
    <asp:ControlParameter ControlID="lstCategories" Name="ProductSubcategoryID" ConvertEmptyStringToNull="true" PropertyName="SelectedValue" />
</SelectParameters>

Since you're using a stored procedure, you'll want to make the parameter optional:

@SomeParm INT = NULL

answered Nov 4, 2011 at 15:10

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

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

Comments

rockyashkumar · Accepted Answer · 2011-11-04 14:27:54Z

0

you can try like this...

    string x = null;
command.Parameters.AddParameter(
     new SqlParameter("@column", (object)x ?? DBNull.Value);

would give you a parm with a value of DBNull.Value, but

string x = "A String";
command.Parameters.AddParameter(
     new SqlParameter("@column", (object)x ?? DBNull.Value);

answered Nov 4, 2011 at 14:27

rockyashkumar

1,3524 gold badges14 silver badges24 bronze badges

Comments

marc_s · Accepted Answer · 2011-11-04 14:41:33Z

0

In your stored procedure set the parameter to have a value eg

@TypeCode = ''

or if its an int

@typecode = 1

etc you get the idea.

edited Nov 4, 2011 at 14:41

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 4, 2011 at 14:35

user1028089

1 Comment

mmcglynn Over a year ago

I have no control over the stored procedure.

Collectives™ on Stack Overflow

How do I pass a null value to a SqlDataSource that is using a stored procedure?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related