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!