Passing null values to a SQL Stored Procedure.

Discussion in 'ASP .Net' started by John Hoge, May 12, 2004.

  1. John Hoge

    John Hoge Guest

    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
    John Hoge, May 12, 2004
    #1
    1. Advertising

  2. 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."



    "John Hoge" <> wrote in message
    news:...
    > 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], May 12, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Smith
    Replies:
    1
    Views:
    1,233
    Brock Allen
    Jun 17, 2005
  2. Replies:
    1
    Views:
    324
    Keith Patrick
    Jan 5, 2007
  3. Replies:
    3
    Views:
    4,055
    Dimitri Kurashvili
    Mar 9, 2007
  4. Hermes
    Replies:
    1
    Views:
    169
    Bob Barrows [MVP]
    Sep 12, 2004
  5. David Shorthouse
    Replies:
    1
    Views:
    215
    Bob Barrows [MVP]
    Mar 7, 2006
Loading...

Share This Page