Re: Last inserted id

> -----Original Message-----
> From: Simeo Reig [mailto:simreig@terra.es]
> Sent: 10 November 2001 20:56
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Last inserted id
>
>
>  Hi, I have this simple code that uses ADO to insert a row in
> a test table  with a serial id and a varchar,  after insert I
> can obtain varchar's value but I *can't* obtain id's value of
>  this record. What I'm doing wrong ??  Thanks!
>
>
>  *Table definition:
>
>      mydatabase-# \d societats;
>           idsocietat    | integer               | not null default
>  nextval('"societats_idsocietat_seq"'::text)
>           nomsocietat   | character varying(50) |
>           capitalsocial | double precision      | default 0
>
>  *Postgres 7.1.3 under FreeBSD 4.4
>
>
>  *VB code
>
>  Private Sub Command3_Click()
>
>    ' Dims omited
>     Set Cnxn = New ADODB.Connection
>     strCnxn = "Provider=MSDASQL.1;Persist Security
> Info=False;Data  Source=mysource"
>     Cnxn.Open strCnxn
>     Set rstEmployees = New ADODB.Recordset
>     strSQL = "societats"
>
>     rstEmployees.Open strSQL, strCnxn, adOpenKeyset,
> adLockOptimistic,  adCmdTable
>
>     strFirstName = Trim(InputBox("Put name:"))
>     rstEmployees.AddNew
>          rstEmployees!nomsocietat = strFirstName
>     rstEmployees.Update
>
>     ' Show the newly added data
>      MsgBox "New record: " & rstEmployees!idsocietat & " " &
> rstEmployees!nomsocietat
>
>      '''''rstEmployees!idsocietat returns nothing !!!!!!!!!!!!!!
>
>        rstEmployees.Close
>     Cnxn.Close
>     Set rstEmployees = Nothing
>     Set Cnxn = Nothing
>
>  End Sub
>

ADO doesn't re-query the database to get the true representation of the new
row. The common way around this problem is to manually get the new ID first
in a seperate query, and then insert that value as well. The other
alternative is to refresh the recordset after the insert, however this often
isn't desirable.

Regards, Dave.