Solved

another ODBC question...

Posted on 1998-10-19
2
158 Views
Last Modified: 2013-11-20
My table has one primary key(Order_ID) with seed value = 1, and auto increment enabled. This mean SQL Server assign a primary key value(Order_ID) for my newly inserted record. Before inserting a new record into the table, is there any way I can find out this new primary key value? I need this value for other operations.
0
Comment
Question by:stephenkong
2 Comments
 

Expert Comment

by:meetze
Comment Utility
there is no real way to find out the next number.  The way I do it is that I create the record then I requery for the id with the same info that I just inserted.  
0
 
LVL 1

Accepted Solution

by:
msa092298 earned 10 total points
Comment Utility
Why do you use auto increment?  I had this problem before and I solved it as following :
Define the id as a primary key (as in your case).
now before inserting a record, do (select Max(mytab.id) from mytab).  Then add one to it and try to insert it, if you get a primary key violation (someone else has just used that ID between the select and insert), then increment and try again until you succeed.  Then you have the primary key and use it wherever you want in the other operations.   You can postpone the update of the rest of the fields till after you do whatever else you want to do.

Another soln, if you can use transactions :
begin transaction
insert the record
select the max(id) from the table
end the transaction

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now