ADODB.recordset.AddNew & returning ID ?! 
Author Message
 ADODB.recordset.AddNew & returning ID ?!

Anyone an idea?

I open a recordset (table) and add new data.
How can i retrieve the id of the new added record within the SAME recordset
definition???

Thanx.

B.M.



Tue, 10 May 2005 00:34:34 GMT  
 ADODB.recordset.AddNew & returning ID ?!
Hi,

It depends on database type. Check next KBs about possible solutions and
issues

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q221931

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195910

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q233299

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195224

--
Val Mazur
Microsoft MVP


Quote:
> Anyone an idea?

> I open a recordset (table) and add new data.
> How can i retrieve the id of the new added record within the SAME
recordset
> definition???

> Thanx.

> B.M.



Tue, 10 May 2005 00:45:17 GMT  
 ADODB.recordset.AddNew & returning ID ?!
The kb's articles I've seen basically suggest requerying the recordset to
refresh the data and then moving to the last record which is meant to be the
newly added record. That's fine providing the recordset is ordered on the
default ID field. Problem occurs if the recordset is ordered on a different
field, because then the requery will place the new record somewhere in the
middle of the recordset.

The workaround I use is call a sub routine within the save sub. The sub routine
opens a new instance of the recordset without an order by, meaning you can go
to the last record and get the ID value, but make sure you use the same
connection object as the first recordset.
Once you have the ID value place it into a variable and close the second
recordset.
Return to the first recordset, do a requery, followed by a find to go to the
newly added record, no matter where it is located in the recordset.
The process may sound unweidly, but it works in less than a second, and shows
the ID field value every time.
Dave

Quote:

> Anyone an idea?

> I open a recordset (table) and add new data.
> How can i retrieve the id of the new added record within the SAME recordset
> definition???

> Thanx.

> B.M.



Tue, 10 May 2005 15:59:16 GMT  
 ADODB.recordset.AddNew & returning ID ?!

Quote:

> I open a recordset (table) and add new data.
> How can i retrieve the id of the new added record within the SAME recordset
> definition???

Do you mean that you are using rs.AddNew?  If so, the ID is not
available until after you physically update the databse using
rs.UpdateBatch.  I usually save a bookmark to the newly added record,
which then can be used to get the new ID after the update.  If my
memory is correct, rs.Update does not set the ID for some reason, but
I could be wrong.

Steve Guidos



Wed, 11 May 2005 05:40:31 GMT  
 ADODB.recordset.AddNew & returning ID ?!
After updating the record, retrieve the ID through the following query, via
Recordset.

rsID.OPen "Select max(ID) from TableName"

NewID=rsID(0)

Best Regards,

Luqman


Quote:
> Anyone an idea?

> I open a recordset (table) and add new data.
> How can i retrieve the id of the new added record within the SAME
recordset
> definition???

> Thanx.

> B.M.



Thu, 12 May 2005 15:45:31 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. ADODB.connection & ADODB.Recordset

2. ADODB Recordset Addnew method

3. How do I retreive autonumber ID's from an ADO recordset after .addnew

4. Recordset.[id] vs Recordset![id] what is the difference

5. Recordset.[id] vs Recordset![id] what is the difference

6. Recordset.[id] vs Recordset![id] what is the difference

7. Function to return ADODB.recordset: runtime error 13

8. dll returns ADODB.recordset as "nothing"

9. return of ADODB.Recordset.Filter if no record find

10. ADODB Recordsets returning data from a stored proc

11. return a ADODB::RecordSet from an ATL object

12. ADODB Recordset does not return primary key after insert

 

 
Powered by phpBB® Forum Software