Passing null values to a SQL Stored Procedure.

[John Hoge]

Is it possible to pass a null value to a stored procedure in .net?

I have a search Sproc that can take one of two numbers to search on,
but not both. I use the code below to pass a null value to the sproc
if a TextBox is empty.

SqlCommand getLesNumbers = new SqlCommand();
getLesNumbers.C ommandText="usp _getLesNumbers" ;
getLesNumbers.C ommandType = CommandType.Sto redProcedure;
getLesNumbers.C onnection = msoConn;

SqlParameter param;
param = getLesNumbers.P arameters.Add(" @order_No",SqlD bType.Int);
param.Direction = ParameterDirect ion.Input;
param.Value = (txtOrder_No.Te xt =="") ? null : txtOrder_No.Tex t;

This generates an error when there is nothing in the txtOrder_No box:
Procedure 'usp_GetLesNumb ers' expects parameter '@order_no', which was
not supplied

I would like to supply a null value to indicate that this field has
not been filled out. I could do this with an empty string, but I think
that null is a more explicit way to indicate that a field is not
filled out. The technique works in classic asp, so I assume there
should be a way to do it in .net

I'm using SQL7 with win2k and the latest service packs on both.

Thanks,
John

[Ken Cox [Microsoft MVP]]

I suspect you're looking for DBNull Class in .NET.

http://msdn.microsoft.com/library/de...classtopic.asp

"This class is used to indicate the absence of a known value, typically in a
database application.

In database applications, a null object is a valid value for a field. This
class differentiates between a null value (a null object) and an
uninitialized value (the DBNull.Value instance). For example, a table can
have records with uninitialized fields. By default, these uninitialized
fields have the DBNull value."

"John Hoge" <jh******@yahoo .com> wrote in message
news:d0******** *************** ***@posting.goo gle.com...

Is it possible to pass a null value to a stored procedure in .net?

I have a search Sproc that can take one of two numbers to search on,
but not both. I use the code below to pass a null value to the sproc
if a TextBox is empty.

SqlCommand getLesNumbers = new SqlCommand();
getLesNumbers.C ommandText="usp _getLesNumbers" ;
getLesNumbers.C ommandType = CommandType.Sto redProcedure;
getLesNumbers.C onnection = msoConn;

SqlParameter param;
param = getLesNumbers.P arameters.Add(" @order_No",SqlD bType.Int);
param.Direction = ParameterDirect ion.Input;
param.Value = (txtOrder_No.Te xt =="") ? null : txtOrder_No.Tex t;

This generates an error when there is nothing in the txtOrder_No box:
Procedure 'usp_GetLesNumb ers' expects parameter '@order_no', which was
not supplied

I would like to supply a null value to indicate that this field has
not been filled out. I could do this with an empty string, but I think
that null is a more explicit way to indicate that a field is not
filled out. The technique works in classic asp, so I assume there
should be a way to do it in .net

I'm using SQL7 with win2k and the latest service packs on both.

Thanks,
John