Getting the primary Key out of database besides using MAX()

[offdarock]

I have to write a record to the database the I need a 100% surefire to grab the primary key and continue to my next page. where I will update that database based on my primary key and some extra gathered info.

 

[ecobb]

What database are you using? If it's SQL Server, you can use "Scope_Identity()" to return the key of a record when it's inserted.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[offdarock]

I am using an access database but we will be porting everything to SQL soon (I hope)

I've been searching for info on scope Identity() but coming up with very little....anymore clues on how to use it

 

[offdarock]

this is what I got....

select Scope_Identity() as newRec;
I get this error
Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement

Iv tried different options, no semi colens, adding semi colons avter the values being added etc???

 

[r937]

the only time people ask for a method to retrieve the primary key is when said primary key happens to be a surrogate (such as an autonumber)

what they often overlook is that the table should also have a "real" primary key

that's the terminology that i prefer, although data modelling purists will rightly call it an alternate or candidate key

have you noticed how often you see threads on discussion forums that begin with "oh please help me, i have duplicates in my table, i want to remove all but the latest of the duplicates"

this is a direct result of not declaring a unique constraint to the "real" (or candidate) key

so...

your table should have a "real" key

hint: it will/should have a unique constraint

when you insert a row, you are supplying the values of the real key

so simply query the row back using those same values, and voila, there's your autonumber

rudy

http://r937.com/

SQL Consulting

 

[offdarock]

I realize now it wont work with access so I settled by using <cftransaction> and selext MAX()

 

[r937]

offdarock, if you query back using the values of a unique key, you won't need a transaction block and your application won't have that bottleneck

rudy

http://r937.com/

SQL Consulting