asked on VB.Net, SQL Server Data Table, and IDENTITY Column
Hi - hope you can help - losing the will to live here!!
Quite a simple one, but nothing seems to be working the way is is documented to work!
I have a database with a simple SQL table with 5 fields:
Now, I have VB.Net code that writes records into this table, but I need to get the IDENTITY column (CrewId) back after the insert. A stored Procedure has been declared as not an option, therefore I have to write and read data with SQL Queries. No problem ... or so I thought.
I have followed all of the examples in setting up my dataset, generating Insert, Update, and Delete statements, and setting the "Refresh Data Table" option, which allegedly is supposed to add a line to the INSERT command to retrieve the newly created record using SCOPE_IDENTITY() to allow the CrewId to be extracted.
Sounds fine .... but ....
The process goes through successfully and claims to generate the Select, Insert, Update, and Delete statements. It lies! It generates a select statement fine, it generates an insert statement, but DOES NOT add the extra statement to return the row matching SCOPE_IDENTITY(), it DOES NOT generate an update statement, and it DOES NOT generate a delete statement.
When I go back and check the configuration for the TableAdapter, the "Refresh Data Table" option has been turned off again (it was DEFINITELY selected) ....
Now, I could skirt round the problem by just selecting the MAX Id from the table after the insert, because the process inserting the record is supposedly the only process writing into the table ... but it is that "supposedly" that makes me nervous. I would rather use the SCOPE_IDENTITY() to get the Id back so that I am sure I get my Id back and not one that "might" have been created another way. I also do not want to use the @@IDENTITY because I do not want Id's from triggers to come back.
Not sure what I am doing wrong here, but I am obviously missing something ...
Steve
Quite a simple one, but nothing seems to be working the way is is documented to work!
I have a database with a simple SQL table with 5 fields:
CrewId IDENTITY
CrewTime DateTime
CrewMember1 Integer
CrewMember2 Integer
CrewMember3 Integer
Now, I have VB.Net code that writes records into this table, but I need to get the IDENTITY column (CrewId) back after the insert. A stored Procedure has been declared as not an option, therefore I have to write and read data with SQL Queries. No problem ... or so I thought.
I have followed all of the examples in setting up my dataset, generating Insert, Update, and Delete statements, and setting the "Refresh Data Table" option, which allegedly is supposed to add a line to the INSERT command to retrieve the newly created record using SCOPE_IDENTITY() to allow the CrewId to be extracted.
Sounds fine .... but ....
The process goes through successfully and claims to generate the Select, Insert, Update, and Delete statements. It lies! It generates a select statement fine, it generates an insert statement, but DOES NOT add the extra statement to return the row matching SCOPE_IDENTITY(), it DOES NOT generate an update statement, and it DOES NOT generate a delete statement.
When I go back and check the configuration for the TableAdapter, the "Refresh Data Table" option has been turned off again (it was DEFINITELY selected) ....
Now, I could skirt round the problem by just selecting the MAX Id from the table after the insert, because the process inserting the record is supposedly the only process writing into the table ... but it is that "supposedly" that makes me nervous. I would rather use the SCOPE_IDENTITY() to get the Id back so that I am sure I get my Id back and not one that "might" have been created another way. I also do not want to use the @@IDENTITY because I do not want Id's from triggers to come back.
Not sure what I am doing wrong here, but I am obviously missing something ...
Steve
Microsoft SQL ServerVisual Basic.NETSQL
Last Comment
Steve Marshall
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Question closed as a self-solve. Walking away and having a think generally helps!