SQL Data Source - store procedure and return parameter

Question

I have a store procedure in SQL Server which returns a value:

CREATE PROCEDURE [dbo].[insertProc]
    @value1 INT,
    @value2 INT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO table1(value1,value2) VALUES (@value1,@value2)

    RETURN SCOPE_IDENTITY();
END

I connect to the DB from ASP.NET using SQL Data Source, which is configured like this:

InsertCommand="@insertedId = insertProc"
InsertCommandType="StoredProcedure"

oninserting="sqlDS_Inserting" 
oninserted="sqlDS_Inserted"

<InsertParameters>
    <asp:Parameter Name="value1" />
    <asp:Parameter Name="value2" />
    <asp:Parameter Name="insertedId" DbType="Int32" Direction="ReturnValue" />
</InsertParameters>

What I want to do it to get the returned value. In the body of sqlDS_Inserted procedure I do like this:

this.insertedId = Convert.ToInt32(e.Command.Parameters["insertedId"].Value);

but I get error:

Object cannot be cast from DBNull to other types.

However, when I look at SQL Server Profiler and run the command (adding declaration of @insertedId variable) it works good. What is the problem and how can I get the returned value of stored procedure from ASP.NET?

Phaedrus · Accepted Answer · 2009-11-05 02:51:54Z

2

I think this statement is your problem.

InsertCommand="@insertedId = insertProc"

You don't need to explicitly assign the return value of the stored procedure to the return value parameter. Just specify the name of the stored procedure.

 InsertCommand = "InsertProc"

Another thing is that you will have to precede the parameter name in your OnInserted event handler with '@'.

this.insertedId = Convert.ToInt32(e.Command.Parameters["@insertedId"].Value);

answered Nov 5, 2009 at 2:51

Phaedrus

8,43128 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukasz Lysik Over a year ago

Unfortunately you are right! I was trying with and without @insertedId on the beginning (and many other options), with or without @ in OnInserted, but I think I never tried in this configuration. Anyway, thanks. It works now.

JonH · Accepted Answer · 2009-11-04 18:25:38Z

0

Try this:

{ 
    SqlConnection conMyData = default(SqlConnection); 
    SqlCommand cmdInsert = default(SqlCommand); 
    SqlParameter parmReturnValue = default(SqlParameter); 
    long l = 0; 

    conMyData = new SqlConnection(_SQLDBConnString); 
    cmdInsert = new SqlCommand("insEmployee", conMyData); 

    { 
        cmdInsert.CommandType = CommandType.StoredProcedure; 
        parmReturnValue = cmdInsert.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt); 
        parmReturnValue.Direction = ParameterDirection.ReturnValue; 
        cmdInsert.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = strFirstName; 
        cmdInsert.Parameters.Add("@MiddleInitial", SqlDbType.VarChar).Value = strMI; 
        conMyData.Open(); 
        cmdInsert.ExecuteNonQuery(); 
        l = (long)cmdInsert.Parameters("RETURN_VALUE").Value; 
    } 

    return l; 
}

answered Nov 4, 2009 at 18:25

JonH

33.2k13 gold badges93 silver badges149 bronze badges

1 Comment

JonH Over a year ago

This way you also do not have to send out an output parameter.

Collectives™ on Stack Overflow

SQL Data Source - store procedure and return parameter

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related