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.CommandText="usp_getLesNumbers";
getLesNumbers.CommandType = CommandType.StoredProcedure;
getLesNumbers.Connection = msoConn;

SqlParameter param;
param = getLesNumbers.Parameters.Add("@order_No",SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = (txtOrder_No.Text =="") ? null : txtOrder_No.Text;

This generates an error when there is nothing in the txtOrder_No box:
Procedure 'usp_GetLesNumbers' 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/d...us/cpref/html/frlrfsystemdbnullclasstopic.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."