troubleshooting Question

VB.Net, SQL Server Data Table, and IDENTITY Column

Avatar of Steve Marshall
Steve MarshallFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerVisual Basic.NETSQL
2 Comments1 Solution366 ViewsLast Modified:
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

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
Steve Marshall
IT Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros