Get the auto-increment-value

[hugobooze]

I'm 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 can't update the dataset from the database, because, of course, i don't have the primary key...

[quwiltw]

I've 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.

[hugobooze]

How do i do that?

[jweissberg808]

I've been able to get it with this snippet. I'm 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")

[hugobooze]

MissingSchemaAction.AddWithKey

 

That's it!

 

Thank you!