| Author |
 Topic  |
|
|
Rigger82
Junior Member
USA
311 Posts |
 Posted - 29 August 2001 : 09:02:03
|
Ok, trying to insert records into a couple tables in a relational database (SQL Server 2000)
Abbreviated code.
quote:
Conn... (SQL Server 2000 Connection)
Rs... (recordset to table 1)
Rs.Open ... (table 1)
Rs.Addnew
rs(field) = request.form(field)
Rs.Update
Session(UID) = Rs(UID) (Rs(UID) is a identity field for a user id number)
Rs.Close
Rs.. (recordset to table 2)
Rs.Open ... (table 2)
Rs.AddNew
Rs(UID) = Session(UID) (putting the users new id into the next table)
Rs(field2) = Request.form(field2)
Rs.Update
Im getting some "OLE Multiple step Operation Error" at what seems is the line where I try to pull the incremented (ID number from rs(UID)) id from the newly created table entry...
Please advise.
---------------------
Mark Christianson
Gulf War Veteran (82nd Airborne Division)
Owner/Operator of www.paratrooper.net
Rigger@paratrooper.net |
|
|
Doug G
Support Moderator
USA
6493 Posts |
|
|
Rigger82
Junior Member
USA
311 Posts |
|
|
cevans
Junior Member
Canada
101 Posts |
Posted - 29 August 2001 : 13:15:09
|
Is it feasible to move the inserts into a stored procedure?
In the stored procedure, do the insert into the first table. The identity value that was inserted in the database will be contained in the @@identity variable, so you can just go ahead and populate the second table right away using that variable.
The only drawback would be if the tables have a large number of columns. You'd need to pass in a parameter for each one, so the code would get pretty ugly pretty fast if you have a large number of columns.
Clark |
 |
|
|
Rigger82
Junior Member
USA
311 Posts |
Posted - 29 August 2001 : 13:49:49
|
quote:
Is it feasible to move the inserts into a stored procedure?
In the stored procedure, do the insert into the first table. The identity value that was inserted in the database will be contained in the @@identity variable, so you can just go ahead and populate the second table right away using that variable.
The only drawback would be if the tables have a large number of columns. You'd need to pass in a parameter for each one, so the code would get pretty ugly pretty fast if you have a large number of columns.
Clark
New to SQL Server but this sounds like a plan... thanks, I'll give it a shot.
---------------------
Mark Christianson
Gulf War Veteran (82nd Airborne Division)
Owner/Operator of www.paratrooper.net
Rigger@paratrooper.net |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 30 August 2001 : 00:40:44
|
Use SELECT @@IDENTITY as NewID right after your insert. This will return the last identity value used by your connection. If you have any triggers that do stuff to other tables, you can get unexpected results though.
You can also SELECT MAX(id) FROM table but it's not multi-user safe unless you're in a transaction.
======
Doug G
====== |
|
|
|
Rigger82
Junior Member
USA
311 Posts |
Posted - 31 August 2001 : 08:22:09
|
Just an update: I created a stored proc, learning quite a bit now, it not only does the three table insert with the NewId setup as stated above... It also validates that there is not an existing username in the system... figured out the return values etc... its great and Im learning fast how much you can get done via Stored Procedures.
Thanks for everyones help.
---------------------
Mark Christianson
Gulf War Veteran (82nd Airborne Division)
Owner/Operator of www.paratrooper.net
Rigger@paratrooper.net |
|
|
| |
Topic |
|
Topic Locked
