If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Problem with DB update, then retrieving an ID

I want to be able to insert a new record into the database, then retrieve the ID of that record, which is an identity field. My problem is that when I attempt to retrieve the ID, nothing comes back.
Here is a sample of my code:

DBConnect 1,"*","tblDocuments","DocumentID=0",1
oRS(1).AddNew
oRS(1)("DocumentName")=ContractName
oRS(1)("DocumentDate")=ContractDate
oRS(1)("ProjectID")=ProjectID
oRS(1)("DocumentTypeID")=2
oRS(1).Update
ContractID=oRS(1)("DocumentID")
ContractFileName="ContractID"&ContractID&"."&FileE xtension
Hence, the ContractFileName comes back as ContractID.doc...or whatever the extension is. The ID is not returned for some reason.

This is, unfortunately, definitely something that will be "DB-specific." Some engines force you to requery; some provide a way to retrieve the ID-number that was assigned.

It is for this very reason that many applications use logic that allows the client to obtain an ID-number from a pool or generator, so that server-assigned numbers are never used. It's always been a "crufty" part of application design: one in which all of the so-called solutions taste bad.