Add New Record with Linked IDs

[Esoteric]

Hey anyone know how to do this:

I am adding a record to table CUSTOMERS and then once complete add a record to CUSTPROFILE with the ID linked back to the Customer Record I just Added.

Is there a way to get the ID(key) upon adding a new record to the table?

 

[Miditen]

This might help:

If you want to know the value of an autonumber field for a record you have just inserted, you can use this:

strSQL = "SELECT Max(ID) AS maxnum FROM CUSTOMERS"
Set myRecordset = Conn.Execute(strSQL)
maxnum = myRecordset("maxnum&quot.Value

ID is the name of your autonumber field, CUSTOMERS is your table name, and strSQL is obviously just a variable. You can then pass that number along something like this:

Response.Redirect "step2.asp?myID=" & maxnum

 

[Esoteric]

Will that work well? What if between the update and the new query a record gets added.

Here is what I have done:

rs.AddNew
--------- Add data here
rs.Update
rs.movelast
IDKEY = rs("ID&quot

This works but I have the same possible dilema here what if between the update and the movelast something gets added? Possible issue.

Is there a way to have the Current Open record ID and store that?

 

[ToddWW]

Esoteric,

That's a dilema if I might say so myself. I think there's a way you can lock the table during this process so any new additions will wait in line until it is unlocked. Of course, I don't know how to do this.

What I do in my app is remove the AutoID function of my database and have ASP generate my own AutoID and store it to a variable at the top of the page. Then when I do inserts into multiple tables, I insert the same ID into all tables..

Does that make sense. It's definitely a viable workaround..

If you're willing to do some modifications to your DB and need a method for ASP to generate an AutoID, let me know.

ToddWW

 

[Esoteric]

Well I can do it by simply locking it, and I know how to do that, that is probably the best way.

 

[DennisTheMenace]

The following insert statement will return a recordset with the Identity of the newly created customers id (if indeed your table is set up with idCustomers with the check box identity)

insert into Customers(chrName, chrPhone)
values("Smith,John", "5551212&quot
select idCustomers = @@identity

Then, you would use the id from the recordset to tie them together on your next insert statement

insert into CustProfile(idCustomers, chrHobby)
values(rs("idCustomers&quot, "Underwater Basket Weaving&quot

Hope this helps! ) =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[chiph]

Esoteric -

Do like Dennis says. As long as you keep the ADO connection open and don't make other queries, ADO will keep the Identity field of the last record you inserted for you. All you have to do is SELECT the @@IDENTITY value.

Of course, your database needs to have a column in the table that has the Identity attribute on it, but you probably knew that.

Chip H.