execute a stored procedure and return a value

[buddyel]

I am trying to insert a record into a table that has an identity field and have the identity value returned after the insert. I have included a portion of the stored procedure that returns this value but my question is how to i get the value returned to visual basic so i can display it to the user.

The stored procedure is as followed.

@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO ticket_category (category_name) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()

 

[andrea96]

Use

cmdInsert.Parameters("@Identity").Value

after you execute the stored procedure to get the value.

 

[jebenson]

This code should do what you want.


Dim cmd As New SqlCommand("TheStoredProcNameGoesHere", cn)

With cmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15)
.Parameters.Add("retValue", SqlDbType.Int)
.Parameters("retValue").Direction = ParameterDirection.ReturnValue
End With
Then:

If cn.State = ConnectionState.Closed Then
cn.Open()
End If

cmd.Parameters("@CategoryName").Value = "ValueHere"

cmd.ExecuteNonQuery()

returnval = cmd.Parameters("retValue").Value

If you have any questions, please post again and I'll try to answer them.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira