?
Solved

another ODBC question...

Posted on 1998-10-19
2
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 40 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

770 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