ADO: Fast way to get ID of new record in set?

ADO: Fast way to get ID of new record in set?

Post by Michael Harro » Wed, 20 May 1998 04:00:00



Hi there,

I am still cutting my teeth on ADO, and have a simple data entry form in
VB5 with a recordset sitting behind it.  When the user clicks "New" I do a
recordset.AddNew.  The user types data, then clicks "Save" and I do a
recordset.Update.

What I want to know is the ID of this new record.  We are using SQL Server
as our back end, and the ID is an auto-increment integer.  There are two
solutions I have figured out so far:

1. Take a bookmark just after the .Update, then do a .Refresh of the
recordset, then jump to the bookmark.  This is slow and I am pretty sure
it's only by a fluke that it works.
2. Don't use a recordset, instead, bundle all of the data for the new
record into an ADODB.Command object and call an SQL stored procedure to do
an SQL "INSERT INTO" and return the new ID.  This works, but my table has
32 columns and this is a difficult to maintain solution, but fast!.

What is the general consensus here? I have two situations really, I have
data entry forms with simple insert/edit/removes, and some batch-type
objects that need to do some multi-table updates, so must be fast!

Tips MOST appreciated!  Please email me back at this address:

Thanks!
---

"You fight today, you fight to die..." - This Mortal Coil
---

 
 
 

ADO: Fast way to get ID of new record in set?

Post by Richard Ian Le » Fri, 22 May 1998 04:00:00


Michael,

After fighting with the many problems of bound data such as the one you
describe, I made the decision several years ago to never use bound controls
again except in a few situations.  So now every type of database form that I
build has some combination of three functions:  GetData(), UpdateData(),
DeleteData().  These functions simply send parameters to a stored procedure
which handle all of the database access.  This definitely means more coding,
but the time saved when it comes time to debug, maintain and modify are more
than worth it.

I hope this helps and Id really be interested to know if others have taken
the same route.

Ian Lee
Sr. Project Leader
Four-D Software

 
 
 

1. HELP Getting ID From New Record using ADO????

Hi:

My app adds a new employee. Immediately afterward I need to enter a Branch
and Division for the employee into the correct tables. I use an "ID" field
set up as an Identity.

My question is, what is the best way to retreive the ID immediately after I
enter it:

(using ADO)

rsMyNewEmp.AddNew
 . . . code . . .
rsMyNewEmp.Update

EmpID = rsMyNewEmp("ID") <<-- This works for Access but not for SQL Server.

Can you help??

Thanks
Marl Atkins

2. "Not enough memory to load"

3. Converting from ADO Record Sets to a ADO.NET record set object

4. DROPPING INDEX which has SPECIAL CHARACTER

5. ADO and the new record 's ID - desperated

6. Internet Replication and Encryption

7. Getting small chucks of a Large ADO Record Set

8. Undefined Function 'xyz' in Expression

9. ADO Update Method and getting PK of new record

10. Getting new Unique Identifier of record in recordset with VC - ADO

11. Ado: Howto set values in new record

12. Get record ID of a new record created with INSERT

13. Getting the new autoincrement id value