By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,364 Members | 1,272 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,364 IT Pros & Developers. It's quick & easy.

retrieve ID of new row using dataset

P: n/a
jay
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!
Nov 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.

"jay" wrote:
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!

Nov 19 '05 #2

P: n/a
jay
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

"jfleeson" wrote:
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.

"jay" wrote:
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!

Nov 19 '05 #3

P: n/a
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.

"jay" wrote:
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

"jfleeson" wrote:
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.

"jay" wrote:
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!

Nov 19 '05 #4

P: n/a
jay
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

"jfleeson" wrote:
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.

"jay" wrote:
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

"jfleeson" wrote:
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.

"jay" wrote:

> 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!

Nov 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.