retrieve last inserted id

[Bastien]

Hi All,


Using classic ASP applciation with DB2 (just adding it to the apps quiver of DBs). I am trying to do an insert (in a set of n inserts) and then retrieve the auto-increment field's value using the standard ADO call of

    rsForm.Update 
     nFormStructRecId = rsForm("record_id") 
    rsForm.Close

The problem is that the value comes back as 0. How should I fix the problem?


Regards



Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[juliane26]

what version of DB2 do you have, which platform?
what is your insert statement?
do you use sequences or generated identity columns?


Juliane

 

[Bastien]

v,8.1 windows

ado insert
sequences

   sSQL = "select * from form_structure where form_id = '" & nHeaderID & "' and field_name = '" & sFldName & "' "

    'use ADO to get the record id number back

    rsForm.open sSQL, PortalDB, adOpenDynamic, adLockOptimistic
    
    if rsForm.EOF and rsForm.BOF then
      rsForm.AddNew 
'      sThisSortOrder = 999       'move the newly added record to last place
    end if
    
    rsForm("field_name") = lcase(sFldName)
    rsForm("sort_order") = nSortCounter      'current sort order
    rsForm("form_id")    = nHeaderID
    rsForm("data_type")  = lcase(sDefaultDataType)
    
    rsForm.Update
    
    nFormStructRecId = rsForm("record_id")

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[juliane26]

I can't see any insert, just a select ...

you are just trying to fetch some data ??
I dunno ADO, sorry, but your question doesn't make sense with an SELECT statement. Where is the INSERT ??

Juliane

 

[Bastien]

ADO handles the insert

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[MarcLodge]

Bastien,
I, like Julianne, am a little confused. I understand that you are using ADO to perform the db2 access, but I can see no sign of an Insert statement. I can see an Update statement, but no Insert. I'm hoping that you might have accidently coded an Update to change an existing record, rather than an Insert. Is this possibly the case?

Marc

 

[Bastien]

No, its how ado works...i do my select and if no record is found then the AddNew method is called. I then assign values to the record set fields object and call the Update method. Its looks to me like this is a read consistency timing issue. I am looking at doing another select on the table to pull the last record id out

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[MarcLodge]

OK, Bastien, good luck with it. Let us know how you get on.

Marc

 

[juliane26]

you don't assign the record id, so is it a generated column or a sequence value?

a sequence can have a call PREVVAL FOR <sequence-name> to get the previous value.

For a generated column: having an explicit insert you could use the new SELECT OVER INSERT clause: if remember correctly something like:
SELECT id FROM NEW TABLE(INSERT INTO <table> VALUES() )
it will do an insert and return the newly generated id column.

Well, I am not sure how that works with your ADO logic ... but I hope you will find out...


Juliane