By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,592 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,592 IT Pros & Developers. It's quick & easy.

Passing null values to a SQL Stored Procedure.

P: n/a
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
Nov 18 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.google.c om...
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


Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.