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

In article
<9fcb29531f54af4f130aab7eee04f10b_at_localhost.talkaboutdatabases.com>, mattwoberts_at_yahoo.com says...
> 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?
>
>

Sorry I couldn't dig up any sample code, but I would go at like this (from memory):

cmd = new Command("INSERT INTO test (...) RETURNING id INTO &id, mConn); cmd.Parameters.Add("id", Int32, DirectionOutput) mConn.Open
cmd.Execute
Msgbox cmd.Parameters(0).Value ' this has the new id

(Close everything)

... something like that. Just stick to using Commands, Parameters and straight SQL, and you'll never go back to .AddNew/.Update

• Tom