Getting the returned value from SQL stored Procedure in VB.net

[Papa_Don]

Group,

I've written a short stored procedure in SQL Server 2008 to create a new Order Number. The code looks like this:

INSERT INTO ORDRNUMBERREC(OrderNo,UserId,CreateDate)
SELECT MAX(OrderNo)+1,'system',GETDATE() FROM ORDRNUMBERREC
WITH (TABLOCKX)

In VB2010, I've written the following code to execute the stored procedure:

            con = New SqlConnection(sConnection)
            cmd = New SqlCommand
            Dim rdr As SqlDataReader
            cmd.CommandText = "Update_OrderNo"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = con

I now want to read this new OrderNo to insert it into a TextBox. This code sits under the above writen code:

            con.Open()
            rdr = cmd.ExecuteReader()
            Do While rdr.Read()
                ordno = rdr(0)
            Loop
            rdr.Close()
            con.Close()

Unfortunately this is returning a value of '0'. I've confirmed by querying the table the procedure is running and a new order number is being assigned. What I can't figure out is why '0' displays in the textbox.

Is it because I'm not querying the table correctly? Ultimately I need to have this new order number returned before the procedure is run by another user (which is why I'm using TABLOCKX). Can someone help me get this correctly?

As always, thanks!

Don

[G_Waddell]

Hi,

You will need to output the new Order Number as an output parameter or return in your Stored Procedure. You should drop the datareader too and use cmd.ExecuteNonQuery as you are not returning a data result set.

Tweek your stored procedure something like as follows:

CREATE PROCEDURE Update_OrderNo @OrdNo BIGINT OUTPUT

INSERT INTO ORDRNUMBERREC(OrderNo,UserId,CreateDate)
SELECT MAX(OrderNo)+1,'system',GETDATE() FROM ORDRNUMBERRECWITH (TABLOCKX)

--CHECK IT HAS INSERTED CORRECTLY
IF @@ERROR =0 
BEGIN
    SELECT @OrdNo = MAX(OrderNo) FROM ORDRNUMBERRECWITH 
END
--IN Case of Failure
IF @@ERROR <> 0 
BEGIN 
    -- Log the error - use a better message than I did :)
    RAISERROR('Awhhh Crap',16,1) WITH LOG   
    SELECT @OrdNo = 0
END

Now for you VB Code:

cmd = New SqlCommand
cmd.CommandText = "Update_OrderNo"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
'Add your parameter
cmd.Parameters.Add("@OrdNo", SqlDBType.BigInt)
'specify it is an output parameter
cmd.Parameters("@OrdNo").Direction = ParameterDirection.Output
'open connection if necessary
If con.State.ToString <> "Open" then
    con.open
end if
'Run procedure
cmd.ExecuteNonQuery
'Get Result
ordrno = cmd.Paramters("@OrdNo").value

Hope this helps

[Papa_Don]

Mr. Waddell,

Thank you for the corrections. This worked absolutely perfect. And I learned some things!

Thanks for your help!!

Don

[G_Waddell]

No worries