Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old October 2nd, 2006, 10:52 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
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.AddNew

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

rsData.Update

--------------------------------

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).

  #2 (permalink)  
Old October 2nd, 2006, 11:00 AM
Registered User
 
Join Date: Oct 2006
Location: , , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have a look at:

http://www.kamath.com/tutorials/tut007_identity.asp

Might be of some use.
Cheers,
Matt.
  #3 (permalink)  
Old October 2nd, 2006, 11:22 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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.
  #4 (permalink)  
Old October 2nd, 2006, 11:27 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mattallyc - fantastic. That works. Thank you!

For the record, here is what I added:

strSQL = "SELECT @@IDENTITY AS NewID"
Set rsData = dcnDB.Execute(strSQL)
userID = rsData.Fields("NewID").value
rsData.Close
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.