retrieve ID of new row using dataset

[Guest]

I am using the dataset object to add a row to a sql server database in vb.net
code, as follows:

dim drow as DataRow
dim cmdBld as new SqlCommandBuilder(mySqlDataAdapter)
ds.tables(0).NewRow()
drow("field1") = data for field 1
and so forth
ds.tables(0).rows.add(drow)
cmdBld = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.Update(ds)

It works great, but how do I retrieve the autoincrement ID field of the row
just added???

Please help!

 

[Guest]

Jay,

How did you create your sql command? is it a stored proc.?

When you call mySqlDataAdapter.Update it should write all the changed /
added reocrds in the data set to the database and update the dataset with the
identities. So all you need to do the get the id from the dataset row. i.e.
drow("id") if "id" is the name of the identity column.

if this doesn't work let me know.

 

[Guest]

Thanks for your help. It pointed me in the right direction.
First, the sql SELECT command was similar to: "SELECT * FROM myTable WHERE
ID = -2;" to give me an empty dataset (to lowering network traffic, increase
performance, etc). When I add the row and update the dataset, the ID column
value is returned as System.DBNull. I tried the same routine but changed the
SELECT command to "SELECT * FROM myTable;" and I got the same results, i.e.,
when I printed drow("ID") in the immediate window after the dataset update,
it still gave me a DBNull value.
The problem seems to be that the autoIncrement ID isn't assigned until the
update but the change is reflected, at this stage, only the back-end database
and NOT in the dataset. To update the dataset with the new ID, you'd have to
do a sqlDataAdapter.Fill(dataset), but how would you know what SELECT
statement to use? If you just ask for the record with the MAX ID, you might
be getting the ID of a record entered by another user right after yours!
The solution I used was to do another query in the table, after the update,
looking for different fields that, in combination, are unique to the record.
Problem is, I might not be able to do that the next time. What am I missing?
Jay

 

[Guest]

Jay,

Here is an example of an insert command that ID is is an identity

this.sqlInsertCommand1.CommandText = "INSERT INTO Codeword(Codeword,
DateActivated, DateDeactivated, Status) VALUES " +
(@Codeword, @DateActivated, @DateDeactivated, @Status); SELECT CodewordID,
Codeword, " +
"DateActivated, DateDeactivated, Status FROM Codeword WHERE (CodewordID =
@@IDENTITY)";

note the second part of the statement. This should be executed as one
command.

Hope this helps.

 

[Guest]

Thanks! It worked like a champ. I used CommandSql.ExecuteScalar since all I
wanted back was the ID. I didn't know that you could stack up SQL statements
like that.
Jay