Get the auto-increment-value

H

hugobooze

Active member
Joined
Jul 22, 2003
Messages
25
Location
Sweden
Im creating a new row in my datatable:
DataTable.Rows.Add(rowToAdd)

...and update the database:
SqlDA = New SqlClient.SqlDataAdapter(strSql,connectionString)
sqlCommBuild = New SqlClient.SqlCommandBuilder(SqlDA)
SqlDA.Update(DataTable)

The problem is: i want to know the primary key of the created row, which is an auto-increment number in my sql-server.

I cant update the dataset from the database, because, of course, i dont have the primary key...
 
Ive never used the commandbuilder but you can change the direction of the parameter of your primary key to output and modify the stored proc to send it out.
 
Ive been able to get it with this snippet. Im using a full dataset rather than just a datatable, but I think it should still work. Once you update the database, it populates that column in your row with the key generated from the auto-increment

daOrder = New SqlDataAdapter("SELECT * FROM ORDERINFO", gDBConn)
Dim autoOrder As New SqlCommandBuilder(daOrder)
daOrder.MissingSchemaAction = MissingSchemaAction.AddWithKey
daOrder.Fill(dsNew, "ORDERINFO")

Dim newrow As DataRow = dsNew.Tables("ORDERINFO").NewRow()

newrow("PRODUCT") = cb_Product.SelectedValue
newrow("CUSTOMER") = cb_Customer.SelectedValue
newrow("FACILITY") = CInt(cb_Facility.SelectedValue)
newrow("ORDERACTIVE") = True

dsNew.Tables("ORDERINFO").Rows.Add(newrow)
daOrder.Update(dsNew, "ORDERINFO")
dsNew.AcceptChanges()

gCurrOrd = newrow("ORDERID")
 
Back
Top