Link to home
Create AccountLog in
Avatar of Steve Marshall
Steve MarshallFlag for United Kingdom of Great Britain and Northern Ireland

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:

CrewId        IDENTITY
CrewTime        DateTime
CrewMember1        Integer
CrewMember2        Integer
CrewMember3        Integer

Open in new window


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
ASKER CERTIFIED SOLUTION
Avatar of Steve Marshall
Steve Marshall
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Steve Marshall

ASKER

Question closed as a self-solve. Walking away and having a think generally helps!