Re: Last inserted id

> -----Original Message-----
> From: Simeo Reig [mailto:simreig(at)terra(dot)es]
> Sent: 10 November 2001 20:56
> To: pgsql-odbc(at)postgresql(dot)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.