Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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
Avatar of Steve Marshall
Steve MarshallFlag of United Kingdom of Great Britain and Northern Ireland imageIT Manager

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

Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answers