I made up 2 joined SQL database tables, Orders and orderCart to track 

orders and the items in the order respectively.  Orders has "orderID" as a 

unique identifier generated by SQL which is joined to the orderCart table 

to identify which items in there belong to this particular order (i.e., if 

an order has an orderID or 23 in Orders table, in the orderCart there may 

be 5 items, but all of them should list 23 as the OrderID which identifies 

them as all being in the same order).  I want to be able to insert the 

items into the orderCart using a loop to loop through an session array I am 

temporarily using to store items in, but I don't know how to "refer back" 

to the uniquely generated order ID.  I am using code with the following 

general algorithm:



Insert INTO Orders (userID, orderDate, shipDate, shipMethod) Values 

(user_id, GetDate(), "unk", ship_method)

    For i=1 to ubound(shoppingCart, 2)

    Insert INTO OrderCart (orderID, productID, product quantity) Values 

(orderID, product_id, product_quantity)

    Next



In the loop how can I get ASP or SQL to use the order ID that was generated 

by SQL in the first INSERT statement?

I'm a bit of a newbie, but I use the recordset object to add my new record,

then use a --"objRS.movelast"-- command to extract the ID of the most

recently added record. ---ID = objRS("UserID")---. If you use the AddNew

method of the recordset, it will automatically incriment any autonumber key

field, therefore the id is easy to extract.  I'm not sure if this is an

option for you, but I do know it will work.



dc



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dana Coffey

Technologist, Xceed, Inc.

112 Krog St.  Atlanta, GA  30307

tel.  xxx-xxx-xxxx  x 5013 

dcoffey@x...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Everything should be as simple as it is, but not simpler. 

----Albert Einstein 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~









{{-----Original Message-----

{{From: Ben Lew 

{{Sent: Monday, July 03, 2000 4:29 PM

{{To: ASP Databases

{{Subject: [asp_databases] referring back to a uniquely generated ID

{{number

{{

{{

{{I made up 2 joined SQL database tables, Orders and orderCart to track 

{{orders and the items in the order respectively.  Orders has 

{{"orderID" as a 

{{unique identifier generated by SQL which is joined to the orderCart table 

{{to identify which items in there belong to this particular order 

{{(i.e., if 

{{an order has an orderID or 23 in Orders table, in the orderCart there may 

{{be 5 items, but all of them should list 23 as the OrderID which 

{{identifies 

{{them as all being in the same order).  I want to be able to insert the 

{{items into the orderCart using a loop to loop through an session 

{{array I am 

{{temporarily using to store items in, but I don't know how to "refer back" 

{{to the uniquely generated order ID.  I am using code with the following 

{{general algorithm:

{{

{{Insert INTO Orders (userID, orderDate, shipDate, shipMethod) Values 

{{(user_id, GetDate(), "unk", ship_method)

{{    For i=1 to ubound(shoppingCart, 2)

{{    Insert INTO OrderCart (orderID, productID, product quantity) Values 

{{(orderID, product_id, product_quantity)

{{    Next

{{

{{In the loop how can I get ASP or SQL to use the order ID that was 

{{generated 

{{by SQL in the first INSERT statement?

{{

{{

Dana, I got a blank email on this...did you have an idea/solution to my

problem?



Thanks



Ben



----- Original Message -----

From: "Dana Coffey" 

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, July 03, 2000 11:45 AM

Subject: [asp_databases] RE: referring back to a uniquely generated ID

number

Dana,



Don't do it this way...

If you have a cursor that is aware of  changes made to the database by other

users, then your .movelast may move to a record that someone else has added!



If you are using the recordset's .addNew() function, then straight after

calling the .update() method, the cursor will be sitting on the new record,

so there is no need to do .movelast()



objRS.addNew

...

objRS.Update

intID = objRS("IDFieldNameHere")

objRS.close

set objRS = nothing



That being said, avoid adding records to a table this way if you can - it's

generally the slowest of all methods.



Cheers

Ken





----- Original Message -----

From: "Dana Coffey" 

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, July 04, 2000 1:45 AM

Subject: [asp_databases] RE: referring back to a uniquely generated ID

number

Look up @@identity in you SQL Server Books Online



Cheers

Ken



----- Original Message -----

From: "Ben Lew" 

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, July 03, 2000 4:29 PM

Subject: [asp_databases] referring back to a uniquely generated ID number





> I made up 2 joined SQL database tables, Orders and orderCart to track

> orders and the items in the order respectively.  Orders has "orderID" as a

> unique identifier generated by SQL which is joined to the orderCart table

> to identify which items in there belong to this particular order (i.e., if

> an order has an orderID or 23 in Orders table, in the orderCart there may

> be 5 items, but all of them should list 23 as the OrderID which identifies

> them as all being in the same order).  I want to be able to insert the

> items into the orderCart using a loop to loop through an session array I

am

> temporarily using to store items in, but I don't know how to "refer back"

> to the uniquely generated order ID.  I am using code with the following

> general algorithm:

>

> Insert INTO Orders (userID, orderDate, shipDate, shipMethod) Values

> (user_id, GetDate(), "unk", ship_method)

>     For i=1 to ubound(shoppingCart, 2)

>     Insert INTO OrderCart (orderID, productID, product quantity) Values

> (orderID, product_id, product_quantity)

>     Next

>

> In the loop how can I get ASP or SQL to use the order ID that was

generated

> by SQL in the first INSERT statement?

There is a reply, but the formatting/encoding seems to be mangled going

through the listserver. If you look at the actual message, headers and all,

you'll see the reply (eg in OE choose Properties | Details|  Message Source

and you'll see what Dana wrote.



Cheers

Ken





----- Original Message -----

From: "Ben Lew" 

To: "ASP Databases" <asp_databases@p...>

Cc: <dcoffey@x...>

Sent: Tuesday, July 04, 2000 4:07 AM

Subject: [asp_databases] RE: referring back to a uniquely generated ID

number





> Dana, I got a blank email on this...did you have an idea/solution to my

> problem?

>

> Thanks

>

> Ben

>

thanks!

I didn't know.

=o)



-----Original Message-----

From: Ken Schaefer 

Sent: Monday, July 03, 2000 6:36 PM

To: ASP Databases

Subject: [asp_databases] RE: referring back to a uniquely generated ID

number





Dana,



Don't do it this way...

If you have a cursor that is aware of  changes made to the database by other

users, then your .movelast may move to a record that someone else has added!



If you are using the recordset's .addNew() function, then straight after

calling the .update() method, the cursor will be sitting on the new record,

so there is no need to do .movelast()



objRS.addNew

...

objRS.Update

intID = objRS("IDFieldNameHere")

objRS.close

set objRS = nothing



That being said, avoid adding records to a table this way if you can - it's

generally the slowest of all methods.



Cheers

Ken

Ken, that looks to be what I need, however, I can't seem to get it to

work...my asp page generates the following SQL statement:



INSERT INTO orders ( user_id, order_entrydate, order_status, shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 ) SELECT @@IDENTITY as

'orderID', INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (OrderID ,1, 'Abel Super Series 12',

$735.00, 1), INSERT INTO OrderCart (Order_ID, Product_ID, Product_Name,

Product_Price, Product_Quantity) VALUES (OrderID ,9, 'Ande Tournament

Monofilament', $10.49, 1), INSERT INTO OrderCart (Order_ID, Product_ID,

Product_Name, Product_Price, Product_Quantity) VALUES (OrderID ,10, 'Extreme

Fluorcarbon Monofilament', $19.99, 1)



But when I run this in the query analyzer I get the message:



Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'INSERT'.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.

Server: Msg 128, Level 15, State 1, Line 1

The name 'OrderID' is not permitted in this context. Only constants,

expressions, or variables allowed here. Column names are not permitted.



I'm not worried about the incorrect syntax near INSERT...it's the 'OrderID'

not permitted that's causing the problems.  I've tried running the

@@identity in a separate sql statement after I've executed the following:



INSERT INTO orders ( user_id, order_entrydate, order_status, shipping_cost,

total_cost) VALUES (6, GETDATE(), 0,$38.27, 803.75 )



But then all I get are a bunch of null values (I get a number of null values

equal to the number of rows I have in the table.



The statement I'm running is "SELECT @@identity FROM orders"



Can you tell me what I'm doing wrong?



Also, is there a WROX book on SQL that would be equivalent to Beginning ASP

series?



Thanks,



Ben







----- Original Message -----

From: "Ken Schaefer" 

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, July 03, 2000 9:36 PM

Subject: [asp_databases] Re: referring back to a uniquely generated ID

number





> Look up @@identity in you SQL Server Books Online

>

> Cheers

> Ken

>

> ----- Original Message -----

> From: "Ben Lew"

> To: "ASP Databases" <asp_databases@p...>

> Sent: Monday, July 03, 2000 4:29 PM

> Subject: [asp_databases] referring back to a uniquely generated ID number

>

>

> > I made up 2 joined SQL database tables, Orders and orderCart to track

> > orders and the items in the order respectively.  Orders has "orderID" as

a

> > unique identifier generated by SQL which is joined to the orderCart

table

> > to identify which items in there belong to this particular order (i.e.,

if

> > an order has an orderID or 23 in Orders table, in the orderCart there

may

> > be 5 items, but all of them should list 23 as the OrderID which

identifies

> > them as all being in the same order).  I want to be able to insert the

> > items into the orderCart using a loop to loop through an session array I

> am

> > temporarily using to store items in, but I don't know how to "refer

back"

> > to the uniquely generated order ID.  I am using code with the following

> > general algorithm:

> >

> > Insert INTO Orders (userID, orderDate, shipDate, shipMethod) Values

> > (user_id, GetDate(), "unk", ship_method)

> >     For i=1 to ubound(shoppingCart, 2)

> >     Insert INTO OrderCart (orderID, productID, product quantity) Values

> > (orderID, product_id, product_quantity)

> >     Next

> >

> > In the loop how can I get ASP or SQL to use the order ID that was

> generated

> > by SQL in the first INSERT statement?

>

Hello Ken,

Could you expand on your e-mail regarding opening "Blank e-mail" a little 

more.  I did not understand the "in OE choose Properties |....."



Thanks

- Vijay





>From: "Ken Schaefer" 

>Reply-To: "ASP Databases" <asp_databases@p...>

>To: "ASP Databases" <asp_databases@p...>

>Subject: [asp_databases] RE: referring back to a uniquely generated ID 

>number

>Date: Tue, 4 Jul 2000 17:15:59 +1000

>

>There is a reply, but the formatting/encoding seems to be mangled going

>through the listserver. If you look at the actual message, headers and all, 

>you'll see the reply (eg in OE choose Properties | Details|  Message Source 

>and you'll see what Dana wrote.

>Cheers

>Ken