Database values.. Please help me!

[angel0910]

Hi everyone,

I need some help..
I use ADODB object connecting to MS Access database..
following codes is to negotiate with database..

if i were to insert values in the table.. how can i return the primary key of the table after inserting?

===========================================================
Set Rs = Nothing
Set Rs = New ADODB.Recordset

If bReadOnly Then
Rs.Open sSQLStatement, Cn, adOpenStatic, adLockReadOnly
Else
Rs.Open sSQLStatement, Cn, adOpenDynamic, adLockOptimistic
End If
===========================================================

rsInsertHeader.AddNew
rsInsertHeader("tableid&quot.Value = nvl(.txtLockerNo.Text, "&quot
rsInsertHeader.Update

===========================================================

 

[vbSun]

How is the primary key? u generate it? like customer id CUST1234A.. then u have it.. if it is an autonumber, get the max() of that field.. wot else can be the possiblity..???

 

[DrJavaJoe]

sSQLStatement = "SELECT @@IDENTITY"

 

[vbSun]

Aint that for SQLServer?

 

[johnwm]

Q. <Aint that for SQLServer?>
A. It's also supported by Jet4

Q. <wot else can be the possiblity..???>
A. Multi-user environment

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

>It's also supported by Jet4

But only in combination with a JET 4 database....

Anyways, when adding records using a recordset and not via an Action query (such as INSERT INTO), when using a Server side cursor, then calling up the Identity additionally is not needed.
ADO will issue retrieve the last Identity number automatically as soon as you call AddNew (if Identity is supported).

So,

.AddNew

lNewID = .Fields(0).Value
.
.
.
.Update

will work under the given condition.
Be aware though, that as soon as you call the AddNew, the Autonumber is immediately reserved and even when cancelling the AddNew action, the number is not used ever again.
If you are not sure whether the AddNew action will be cancelled or not by the user, and you want to prevent &quot;holes&quot; with these numbers, then Action queries (INSERT INTO) are a better choice.

 

[angel0910]

hi,

thanxs vbSun, DrJavaJoe, johnwm and CCLINT

i would like to insert and retrieve the values out at the same time using the same ADO object

is it possible?

 

[CCLINT]

Then try DrJavaJoe's suggestion, or mine.
After the INSERT, the connection object will hold the new ID locally.