How to get the ID of a newly added record from ADO

From: mwob <mattwoberts_at_yahoo.com>
Date: Tue, 19 Apr 2005 06:24:45 -0400
Message-ID: <9fcb29531f54af4f130aab7eee04f10b@localhost.talkaboutdatabases.com>

Hi,
I have seen previous posts about this, and various solutions, but none of them are working for me. I am trying to get Oracle to auto-return the ID of the new record I added, via ADO. I know its possiblle, but its not working for me.

I am using the latest Oracle ODBC driver, and Oracle 9iRel2. My code, which I am sure *should* work, is:

mConn.ConnectionString = "Driver={Oracle in OraHome92};uid=Envoy;pwd=significance;dbq=EnvoyOne;"

mConn.CursorLocation = adUseServer
mConn.Open

rs.Open "select * from test where id < -1", mConn, adOpenKeyset, adLockOptimistic
rs.AddNew "name", "newname"
rs.Update

MsgBox rs("id")

The keys here is that the cursor location must be set to server, and the recordset opened "adOpenKeyset". I have a sequence and a trigger on the "test" table so that the ID is auto-generated. But...All I get is a null from the ID column after calling the update method.

Any ideas? Received on Tue Apr 19 2005 - 05:24:45 CDT