Stored procedure parameter output value

Discussion in 'ASP .Net' started by Steven, Nov 5, 2003.

  1. Steven

    Steven Guest

    I'm calling a stored procedure which has an output parameter of type int.
    Once the stored procedure is executed, I want to check the value of the
    parameter in case it is null. However, when the a null value is returned I
    don't seem to be able to detect it.

    Any help would be greatly appreciated.

    C# code is as follows:

    SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter param = cmd.Parameters.Add("@iApplicationID",
    SqlDbType.Int);
    param.Direction = ParameterDirection.Output;

    param = cmd.Parameters.Add("@vcApplicationConstName",
    SqlDbType.VarChar);
    param.Value = sAppConstName;

    if (conn.State == ConnectionState.Closed)
    conn.Open();

    cmd.ExecuteNonQuery();
    conn.Close();

    // check for null here
    if (cmd.Parameters["@iApplicationID"].Value != null){
    iID = (int)cmd.Parameters["@iApplicationID"].Value;
    }
    else{
    throw new ApplicationException("Unable to retrieve Application ID for
    application: " + sAppConstName);
    }

    Stored Procedure (for test purposes):

    CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]

    @vcApplicationConstName varchar(50),
    @iApplicationID int OUTPUT

    AS

    SET @iApplicationID = null

    GO

    Thanks in advance for your help.

    Steven
    Steven, Nov 5, 2003
    #1
    1. Advertising

  2. Hello

    Compare with DBNull.Value instead of null.

    if (cmd.Parameters["@iApplicationID"].Value != DBNull.Value)
    {
    .....
    }


    Best regards,
    Sherif


    "Steven" <> wrote in message
    news:boanet$qp4$1$...
    > I'm calling a stored procedure which has an output parameter of type int.
    > Once the stored procedure is executed, I want to check the value of the
    > parameter in case it is null. However, when the a null value is returned I
    > don't seem to be able to detect it.
    >
    > Any help would be greatly appreciated.
    >
    > C# code is as follows:
    >
    > SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
    > cmd.CommandType = CommandType.StoredProcedure;
    >
    > SqlParameter param = cmd.Parameters.Add("@iApplicationID",
    > SqlDbType.Int);
    > param.Direction = ParameterDirection.Output;
    >
    > param = cmd.Parameters.Add("@vcApplicationConstName",
    > SqlDbType.VarChar);
    > param.Value = sAppConstName;
    >
    > if (conn.State == ConnectionState.Closed)
    > conn.Open();
    >
    > cmd.ExecuteNonQuery();
    > conn.Close();
    >
    > // check for null here
    > if (cmd.Parameters["@iApplicationID"].Value != null){
    > iID = (int)cmd.Parameters["@iApplicationID"].Value;
    > }
    > else{
    > throw new ApplicationException("Unable to retrieve Application ID for
    > application: " + sAppConstName);
    > }
    >
    > Stored Procedure (for test purposes):
    >
    > CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]
    >
    > @vcApplicationConstName varchar(50),
    > @iApplicationID int OUTPUT
    >
    > AS
    >
    > SET @iApplicationID = null
    >
    > GO
    >
    > Thanks in advance for your help.
    >
    > Steven
    >
    >
    Sherif ElMetainy, Nov 5, 2003
    #2
    1. Advertising

  3. Steven wrote:

    > I'm calling a stored procedure which has an output parameter of type int.
    > Once the stored procedure is executed, I want to check the value of the
    > parameter in case it is null. However, when the a null value is returned I
    > don't seem to be able to detect it.
    >
    > Any help would be greatly appreciated.
    >
    > C# code is as follows:
    >
    > SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
    > cmd.CommandType = CommandType.StoredProcedure;
    >
    > SqlParameter param = cmd.Parameters.Add("@iApplicationID",
    > SqlDbType.Int);
    > param.Direction = ParameterDirection.Output;
    >
    > param = cmd.Parameters.Add("@vcApplicationConstName",
    > SqlDbType.VarChar);
    > param.Value = sAppConstName;
    >
    > if (conn.State == ConnectionState.Closed)
    > conn.Open();
    >
    > cmd.ExecuteNonQuery();
    > conn.Close();
    >
    > // check for null here
    > if (cmd.Parameters["@iApplicationID"].Value != null){
    > iID = (int)cmd.Parameters["@iApplicationID"].Value;
    > }
    > else{
    > throw new ApplicationException("Unable to retrieve Application ID for
    > application: " + sAppConstName);
    > }
    >
    > Stored Procedure (for test purposes):
    >
    > CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]
    >
    > @vcApplicationConstName varchar(50),
    > @iApplicationID int OUTPUT
    >
    > AS
    >
    > SET @iApplicationID = null
    >
    > GO
    >
    > Thanks in advance for your help.
    >
    > Steven
    >
    >


    MSDN says:
    When sending a null parameter value to the server, the user must specify
    DBNull, not null. The null value in the system is an empty object that
    has no value. DBNull is used to represent null values

    So try
    cmd.Parameters["@iApplicationID"].Value != DBNull.Value

    Dmitry
    Dmitry Baibakov, Nov 5, 2003
    #3
  4. Steven

    Steven Guest

    Excellent. Thanks.
    Steven, Nov 5, 2003
    #4
  5. Steven

    oj Guest

    "NULLl" is a valid value and it's not the same as "null" - the literal that
    represents a null reference.

    Here is some info on DBNull class.

    http://msdn.microsoft.com/library/d...us/cpref/html/frlrfSystemDBNullClassTopic.asp


    "Wilford Munley" <> wrote in message
    news:...
    > "Sherif ElMetainy" <> wrote in message

    news:...
    > > Hello
    > >
    > > Compare with DBNull.Value instead of null.
    > >
    > > if (cmd.Parameters["@iApplicationID"].Value != DBNull.Value)
    > > {
    > > ....
    > > }
    > >
    > >
    > > Best regards,
    > > Sherif
    > >
    > >
    > > "Steven" <> wrote in message
    > > news:boanet$qp4$1$...
    > > > I'm calling a stored procedure which has an output parameter of type

    int.
    > > > Once the stored procedure is executed, I want to check the value of

    the
    > > > parameter in case it is null. However, when the a null value is

    returned I
    > > > don't seem to be able to detect it.
    > > >
    > > > Any help would be greatly appreciated.
    > > >
    > > > C# code is as follows:
    > > >
    > > > SqlCommand cmd = new SqlCommand("sp_GetApplicationID", conn);
    > > > cmd.CommandType = CommandType.StoredProcedure;
    > > >
    > > > SqlParameter param = cmd.Parameters.Add("@iApplicationID",
    > > > SqlDbType.Int);
    > > > param.Direction = ParameterDirection.Output;
    > > >
    > > > param = cmd.Parameters.Add("@vcApplicationConstName",
    > > > SqlDbType.VarChar);
    > > > param.Value = sAppConstName;
    > > >
    > > > if (conn.State == ConnectionState.Closed)
    > > > conn.Open();
    > > >
    > > > cmd.ExecuteNonQuery();
    > > > conn.Close();
    > > >
    > > > // check for null here
    > > > if (cmd.Parameters["@iApplicationID"].Value != null){
    > > > iID = (int)cmd.Parameters["@iApplicationID"].Value;
    > > > }
    > > > else{
    > > > throw new ApplicationException("Unable to retrieve Application ID

    for
    > > > application: " + sAppConstName);
    > > > }
    > > >
    > > > Stored Procedure (for test purposes):
    > > >
    > > > CREATE PROCEDURE [dbo].[sp_BSQ_GetApplicationID]
    > > >
    > > > @vcApplicationConstName varchar(50),
    > > > @iApplicationID int OUTPUT
    > > >
    > > > AS
    > > >
    > > > SET @iApplicationID = null
    > > >
    > > > GO
    > > >
    > > > Thanks in advance for your help.
    > > >
    > > > Steven
    > > >
    > > >

    > >
    > >

    >
    > Why are DBNull and null different? Aren't they the same thing?
    >
    > Wilford
    >
    oj, May 27, 2004
    #5
  6. Steven

    Jeff Louie Guest

    Me thinks you mispoke. Here is the what is said:

    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).

    In other words:

    NULL is not a value at all. NULL is a placeholder for missing
    information.

    Regards,
    Jeff
    Author. Visual Cafe for Java Explorer, Database Development Edition
    Coriolis
    1998.
    >"NULLl" is a valid value and it's not the same as "null" - the literal

    that
    represents a null reference.<

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Jeff Louie, May 27, 2004
    #6
    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. Craig Buchanan
    Replies:
    1
    Views:
    394
  2. MS
    Replies:
    5
    Views:
    11,311
    Marc Scheuner [MVP ADSI]
    Jan 31, 2005
  3. Mr Not So Know It All
    Replies:
    4
    Views:
    23,321
    Mr Not So Know It All
    Jan 17, 2006
  4. rockdale
    Replies:
    1
    Views:
    10,341
    cyler108
    Jan 15, 2009
  5. Machelle Chandler

    Using query string to pass a value to a stored procedure parameter

    Machelle Chandler, Oct 21, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    156
    Machelle Chandler
    Oct 21, 2003
Loading...

Share This Page