Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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