Wrox Programmer Forums
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 2nd, 2006, 10:52 AM
Authorized User
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to retrieve ID field of newly-created record

I'm adding a new user to my database as follows:


strSQL = "SELECT * FROM Users"
Set rsData = GetRecordset(strSQL, adOpenStatic, adLockPessimistic)


rsData("UserLogin") = userLogin
rsData("UserPassword") = userPassword



However, the form which submitted the user data includes data that is to be written to a *second* table for this new user.

My problem: How can I write to this other table since I do not (yet) know the UserID (which is the primary key, and auto-generated by the database) of the newly-created user in the Users table?

I want to do this all in one step (on the same page) but it seems impossible since in order to create the new user, I must update the recordset, but as soon as I do that, I have no way of knowing what UserID the database has assigned to it.

In the past, I have got round this by manually assigning the next highest UserID to the new user (i.e. UserID not auto-generated). But this seems clumsy.

Is there a smarter way?

(Note: I am not using stored procedures at all and do not intend to do so for this project).

Old October 2nd, 2006, 11:00 AM
Registered User
Join Date: Oct 2006
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

Have a look at:


Might be of some use.
Old October 2nd, 2006, 11:22 AM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

A stored procedure would be best in this secnario (as you could handle all data with one call), IMHO, but since you aren't you will have to make 2 calls to the database before you can work with the second table:

//INSERT Statement

Then assign this value to a variable and you can work with the second table.
SELECT max(primary_key) from table1

I agree that this is a bit clumsy but since you don't intend to use SP's I dont really see any alternative.

--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
Old October 2nd, 2006, 11:27 AM
Authorized User
Join Date: Jan 2005
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts

Mattallyc - fantastic. That works. Thank you!

For the record, here is what I added:

Set rsData = dcnDB.Execute(strSQL)
userID = rsData.Fields("NewID").value
Set rsData = nothing

Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Record & retrieve ID gundulf ADO.NET 5 July 12th, 2007 10:03 PM
Retrieve last record savoym SQL Server 2000 4 March 3rd, 2006 12:43 PM
How can i force a field being created in a map jtf Biztalk 0 July 30th, 2005 03:06 AM
importing a newly created Word obj into SQLServer scottcaulk VS.NET 2002/2003 1 June 30th, 2003 04:44 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.