How can I get and use an OUTPUT parameter

Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?

ALTER PROCEDURE [dbo].[NumberCounter]-- Add the parameters for the stored procedure here@.InsertDatedatetimeASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereINSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);SelectIDENT_CURRENT('tblNumberCounter')ENDPublic Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs) Dim ConAs SqlConnection Dim StrInsertAs String Dim cmdInsertAs SqlCommand Dim myNewReceiptNumberAs Integer Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString'Add row to receipt table, then get it for ReceiptNumberText field. cmdInsert = New SqlCommand cmdInsert.CommandText = "NumberCounter" cmdInsert.CommandType = CommandType.StoredProcedure cmdInsert.Connection = New SqlConnection(ConnectStr) cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString()) Try Con.Open() myNewReceiptNumber = cmdInsert.ExecuteScalar() 'Response.Write(myNewReceiptNumber) Catch objExceptionAs SqlException Dim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally Con.Close()End TryEnd Sub

Try this...

ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@.InsertDatedatetime,
@.InsertedInteger int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);Select @.InsertedInteger =IDENT_CURRENT('tblNumberCounter')
END

Public Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs)

Dim ConAs SqlConnection
Dim StrInsertAs String
Dim cmdInsertAs SqlCommand
Dim myNewReceiptNumberAs Integer
Dim ConnectStrAs String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)

cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString())

'Create a new parameter with direction set to output, and add it to the parameters collection
dim InsertedInteger as new SqlParameter("@.InsertedInteger", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
cmdInsert.Parameters.Add(InsertedInteger)
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objExceptionAs SqlException
Dim objErrorAs SqlError
For Each objErrorIn objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try
MessageBox.Show(InsertedInteger.Value)
End Sub

|||Use SCOPE_IDENTITY() to get the ID of the record just inserted. IDENT_CURRENT will give you the ID for any session and any scope. So if the proc gets executed multiple times in parallel your Id's could get mixed up.|||Both of you have help very much.|||Welcome. Now can you mark the post as answered? Doesnt matter whose post you choose (you can choose both too). This helps in a number of ways especially when people search for posts with similar problem like yous.|||

Well, I could use a little more help with this:

I need to use the ID that is output in a query string, however, when I try to use it I get an error 'Object reference not set to an instance of an object'

Anyone see why this might happen??

Try cmdInsert.Connection.Open() InsertedInteger = cmdInsert.ExecuteScalar()Catch objExceptionAs SqlExceptionDim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally cmdInsert.Connection.Close() Response.Redirect("../editnew.aspx?ID=" & InsertedInteger.Value)End Try

|||

You would use ExecuteScalar to capture the output from your SELECT statement and it only captures one value. If you are using output parameters you would use ExecuteNonQuery and create parameters of type OUTPUT and use them to retrieve the values. You can use the sample code below and modify it accordingly:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="usp_testproc"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int))myCommand.Parameters("@.userid").Value = Trim(userid)myCommand.Parameters.Add(New SqlParameter("@.ttype",SqlDbType.VarChar,25))myCommand.Parameters("@.ttype").Value ="charge"'output parametermyParam = mycommand.CreateParameter()myParam.ParameterName ="@.result"myParam.Direction = ParameterDirection.OutputmyParam.SqlDbType = SqlDbType.bigintmycommand.Parameters.Add(myParam)TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Response.Write(convert.todouble(mycommand.Parameters("@.result").Value))Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try