• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

another ODBC question...

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
stephenkong
Asked:
stephenkong
1 Solution
 
meetzeCommented:
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
 
msa092298Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now