Getting the value of an autoincrement or identity field during an sql insert

Getting the value of an autoincrement or identity field during an sql insert

Post by Chris Rile » Sat, 30 Jan 1999 04:00:00



Please forgive me if I have asked a common question, but Dejanews was no
help (yes I looked :> )

If I am using an SQL insert inside of VB with dao for instance connected via
ODBC to sqlserver7 database, how do I get the value of the identity or
autoincrement field as the record is added.  I realize that using the
.addnew method on the recordset will make the value of the identity field
immediately available.  However I want to use the .execute method with the
database i.e db.execute("INSERT " & yada yada yada)  Now, that I have just
executed the insert statement, how can I get the value of the AutoNumber
field that is a part of that record.  I can't just look at the last record
as I have 25 concurrent users all adding records and by the time I re-query,
I may have an additional 25 records on top of the one just added in this
example.

Please help me.
Chris


remove the .no.Spam to make it work

 
 
 

Getting the value of an autoincrement or identity field during an sql insert

Post by Richard Weerts/TrainTalk.co » Sat, 30 Jan 1999 04:00:00


Chris -

Well, the way I see it you have two choices -

1) requery the DB with the user's name or other identifier

2) use the rs.addnew method.  

What's wrong with the .addnew method?  The cursor required may be slow,
but certainly not as slow as going back to the DB on some text search.

R


> Please forgive me if I have asked a common question, but Dejanews was no
> help (yes I looked :> )

> If I am using an SQL insert inside of VB with dao for instance connected via
> ODBC to sqlserver7 database, how do I get the value of the identity or
> autoincrement field as the record is added.  I realize that using the
> .addnew method on the recordset will make the value of the identity field
> immediately available.  However I want to use the .execute method with the
> database i.e db.execute("INSERT " & yada yada yada)  Now, that I have just
> executed the insert statement, how can I get the value of the AutoNumber
> field that is a part of that record.  I can't just look at the last record
> as I have 25 concurrent users all adding records and by the time I re-query,
> I may have an additional 25 records on top of the one just added in this
> example.

> Please help me.
> Chris


> remove the .no.Spam to make it work


 
 
 

Getting the value of an autoincrement or identity field during an sql insert

Post by Simon Jone » Sun, 31 Jan 1999 04:00:00



To actually perform this from VB you would open a recordset for the stored procedure. The recordset will contain one row with one column that will be the Identity value just inserted. The transaction makes sure you get the right value no matter what other users are doing at the time.

Regards,
Simon Jones
MillStream Design
Independent IT Consultants


>If I am using an SQL insert inside of VB with dao for instance connected via
>ODBC to sqlserver7 database, how do I get the value of the identity or
>autoincrement field as the record is added.  I realize that using the
>.addnew method on the recordset will make the value of the identity field
>immediately available.  However I want to use the .execute method with the
>database i.e db.execute("INSERT " & yada yada yada)  Now, that I have just
>executed the insert statement, how can I get the value of the AutoNumber
>field that is a part of that record.  I can't just look at the last record
>as I have 25 concurrent users all adding records and by the time I re-query,
>I may have an additional 25 records on top of the one just added in this
>example.

 
 
 

Getting the value of an autoincrement or identity field during an sql insert

Post by Jeffrey Mulle » Mon, 01 Feb 1999 04:00:00


Chris -



app.  Then again, I could be way off on this as I don't use ADO.

-jeff

 
 
 

Getting the value of an autoincrement or identity field during an sql insert

Post by Russ McClellan » Tue, 02 Feb 1999 04:00:00


I use the same thing from ADO...
 
 
 

1. Getting the value of an autoincrement or identity field during an sql insert

Please forgive me if I have asked a common question, but Dejanews was no
help (yes I looked :> )

If I am using an SQL insert inside of VB with dao for instance connected via
ODBC to sqlserver7 database, how do I get the value of the identity or
autoincrement field as the record is added.  I realize that using the
.addnew method on the recordset will make the value of the identity field
immediately available.  However I want to use the .execute method with the
database i.e db.execute("INSERT " & yada yada yada)  Now, that I have just
executed the insert statement, how can I get the value of the AutoNumber
field that is a part of that record.  I can't just look at the last record
as I have 25 concurrent users all adding records and by the time I re-query,
I may have an additional 25 records on top of the one just added in this
example.

Please help me.
Chris


remove the .no.Spam to make it work

2. Portable PC

3. Getting back an identity field value after an insert SQL statement

4. linked SQL Table

5. Recordset-Getting the ID of a row inserted when the ID is IDENTITY/AUTOINCREMENT

6. Designer 2000

7. getting autoincrement value after INSERT

8. Unable to get list of data minng algorithms

9. Attempt to insert duplicate identity values in table (has identity and key fields)

10. Knowing @@IDENTITY value during INSERT???

11. Determine IDENTITY value during multiple INSERTS

12. Learning value of identity field on SQL Server 7 after ADO-based insert

13. Retrieve value of autoincrement field with inserted record