Add a New Record and Retrieve the RowID Number

[Daryll Shatz]

How do I add a new record to a table and then extract the rowID number that
was auto assigned to the record (ie: do a @@Indentity in code)?

I tried using this code but setting a dataview with a Sort value causes the
wrong record to be retrieved in the last statement. Any suggestions
welcomed.

'Table Layout:
RowID as AutoAssigned Integer
Col1 as anything
Col2 as anything

'Define a dataview with a sort value off the dataset table
Dim dv as New DataView(DataSet11.Table,"","Col2",
DataViewRowState.CurrentRows)
'Code to add a new record to a DataSet Table
Dim dr As DataSet1.TableRow
dr = DataSet11.Table.NewTableRow
'Populate datarow
dr.col1 = value1
dr.col2 = value2
'Write new record
DataSet11.Table.AddTableRow(dr)
'Sync(DataSet And DB)
daTable.Update(DataSet11, "Table")
'Repopulate(DataSet)
DataSet11.Table.Clear()
daTable.Fill(DataSet11, "Table")

RowID = dvTable(dvTable.count - 1)("ID")

 

[CT]

Try this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconretrievingidentityorautonumbervalues.asp

 

[Daryll Shatz]

Thanks, Carsten.

I understand I would use a output parameter, but I don't see how to retrieve
the value.

SqlInsertCommand1.CommandText = "INSERT INTO ....."

SqlInsertCommand1.Connection = DBConn

SqlInsertCommand1.Parameters.Add("@col1", "Col1")

Dim myParam as SqlParam =
da.InsertCommand1.Parameters.Add("@Indentity,"RowID")

myParm.Direction = ParameterDirection.Output

dr.col1 = somevalue

dr.col2 = somevalue

DataSet11.Table.AddTableRow(dr)

daTable.Update(DataSet11.Table)

'How do I retrieve the @Indentity value here?

 

[David Sceppa]

Darryl,

Configure your query to assign the new auto-increment value
to an output parameter. Then, add a parameter to the
InsertCommand’s parameters collection. Bind the parameter to the
correct column in your DataTable through the call to Add
(corresponds to setting the SourceColumn property). Set the
parameter’s direction to Output and make sure that the command is
configured to push values of output parameters back to the
DataRow after update. This is controlled by the Command’s
UpdatedRowSource property, which is set to Both by default. Both
will check for output parameters and for a row returned by the
query. I prefer using output parameters only for performance
reasons.

Your code should look something like this:

With MyDataAdapter.InsertCommand
.CommandText = "INSERT INTO MyTable ...;" & _
"SET @ID = SCOPE_IDENTITY()"
.Parameters.Add(...)
...
.Parameters.Add(...)
p = .Parameters.Add("@ID", SqlDbType.Int, 0, "ID")
p.Direction = ParameterDirection.Output
.UpdatedRowSource = UpdateRowSource.OutputParameters
End With

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Daryll Shatz]

Thanks, David. I understand how to set up the Insert and Parameters. What
I don't understand is how to retrieve the output parameter value in another
subroutine which caused the new record to be added..


Assuming I have setup your code below in SUB A:

Public Sub A(...)
...

With MyDataAdapter.InsertCommand
.CommandText = "INSERT INTO MyTable ...;SET @ID = SCOPE_IDENTITY()"
.Parameters.Add(...)
...
.Parameters.Add(...)
p = .Parameters.Add("@ID", SqlDbType.Int, 0, "ID")
p.Direction = ParameterDirection.Output
.UpdatedRowSource = UpdateRowSource.OutputParameters
End With
End Sub

How do I retrieve the output parameter value in SUB B:

Private Sub B(...)
DataSet11.Table.NewRow(...)
...

DataSet11.Table.AddTableRow(dr)
dr.Col1 = ....
daTable.Update(DataSet11.Table)

msgbox ???What Goes Here to Display the Output Parameter's Value???

...
End Sub