Solved

another ODBC question...

Posted on 1998-10-19
2
162 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
ID: 1323539
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
ID: 1323540
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Show shut-down message as Windows 8 shuts down. 9 91
move a line in eclipse 3 103
either24  challenge 19 97
mapAB Challlenge 35 145
Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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