Insert Information And To Database And Retrieve The ID Created For That Row Of Data In The Table

Hi,

 I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?

  

CREATE PROCEDURE dbo.InsertProduction

@DATEOUT datetime(8),

@DATEREQUIRED datetime(8),

@PREPAREDBY varchar,

@COMMENTID INteger,

@TOTALQUANTITY INteger,

@VENDORID INteger,

@WPO varchar,

@TCAPONUMBER INteger,

@APPROVEDBY varchar,

@Identity int OUT

 

AS

INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY)

SET @Identity = SCOPE_IDENTITY()

 

 

'collect all the information from the form and then apply all and then update

'Get a reference to the Production table.

Dim dtProduction As DataTable = DS.Tables("Production")

Dim dtLineItem As DataTable = DS.Tables("LineItems")

' Create the SqlCommand to execute the stored procedure.

Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection)

Production.InsertCommand.CommandType = CommandType.StoredProcedure

' Add the parameter for the CategoryName. Specifying the

' ParameterDirection for an input parameter is not required.

'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")

Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName")

Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName")

Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName")

Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName")

Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName")

Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName")

Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName")

Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName")

Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName")

' Add the SqlParameter to retrieve the new identity value.

' Specify the ParameterDirection as Output.

Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID")

parameter.Direction = ParameterDirection.Output

' Create a new row with the same schema.

Dim dr As DataRow = dtProduction.NewRow()

'you need the ID from this to insert into the Production DB

' Set the value of all the columns.

dr("DateOut") = CDate(DateTimePicker1.Text)

dr("DateRequired") = CDate(DateTimePicker2.Text)

dr("VendorID") = CInt(vendorbox.SelectedValue)

dr("HomeAddress") = txtApproved.Text.ToString

dr("ApprovedBy") = txtPrepared.Text.ToString

dr("TCAPO") = CInt(txtTCAPO.Text.Trim)

dr("CommentID") = CommentID

dr("TotalCost") = CDec(txtTotals.Text)

dr("TotalQuantity") = CInt(txtQtyTotal.Text)

' Add to the Rows collection or table .

dtProduction.Rows.Add(dr)

'Update the Production Table and then retrieve the ID created in this case

Production.Update(dtProduction)

 

 

Dollarjunkie