using two record sets simultaneously

[slickwillyslim]

does anyone know if its possible to create two record sets that use one connection, so that i can pull data from one table and loop the data into another table? i'm trying to avoid the use of arrays. any example of this somewhere? i'm trying to pull everything from a cart table and write to an order table but i have one field that must be created during this data transfer, which is orderNum. this is to assure that the orderNum(order number) is compared to the last record in the order table instead of the cart -(which is temporary order data for each session). i hope this doesn't cause a problem when transferring the data. anyway, any suggestion would be helpful. also wanna say that this is a great forum and you tech guys are full of great information!!! you've made a few logic glaciers melt already, thanks guys.

 

[Tarwn]

There shouldn't be any problem with doing this at all, though not using arrays and using two open recordsets may be less than efficient:

'I'm assuming there is a customer id in a session variable, should be easy to modify it to use whatever you are keeping to key to the cart

Dim rs_cart, rs_orders, str_conn
Dim sql_cart, sql_orders

sql_cart = "SELECT [i]fieldname1[/i],[i]fieldname2[/i],etc FROM ShoppingCart WHERE customer_id = " & Session("customer_id")

Set rs_cart = Server.CreateObject("ADODB.RecordSet")
'open the cart recordset, 0-forwardOnly, 1-ReadOnly, 1-CmdText
rs_cart.Open sql_cart, str_conn, 0, 1, 1

'if there aren't any records in the cart then display an empty cart" error message
If rs_cart.EOF Then
   Response.Write "Nothing in your cart"

   'close the recordset and stop execution
   rs_cart.Close() 
   Set rs_cart = Nothing
   Response.End
End If

'create the orders recordset
sql_orders = "SELECT [i]fieldname1[/i],[i]fieldname2[/i],etc FROM OrdersTable WHERE orderNum = Max(OrderNum)"
Set rs_orders = Server.CreateObject("ADODB.RecordSet")
'Open records with greatest order number - should keep communications time to a minimum and also give you access to the largest current ordernum - 3 static,3 optimistic, 1 cmdText
rs_orders.Open sql_orders,str_conn,3,3,1

'the records should all have the largest recordnumber so we will take it if there are records, if there aren't we can assume this is the first ever order and give it any number we want
Dim order_num
If Not rs_orders.EOF Then
   order_num = rs_orders("orderNum") + 1
Else
   order_num = 1
End If

'transfer records from cart to orders
Do Until rs_cart.EOF
   rs_orders.Addnew

   rs_orders("[i]fieldname1[/i]") = rs_cart("[i]fieldname1[/i]")
   rs_orders("[i]fieldname2[/i]") = rs_cart("[i]fieldname2[/i]")
   'etc
   rs_orders("orderNum") = order_num

Loop

'send the new records back
rs_orders.Update

'remove the cart items
rs_cart.Close
rs_cart.Open "DELETE * FROM cart WHERE customer_id = " & Session("customer_id"),str_conn

'clean up the objects
rs_orders.Close
rs_cart.Close
Set rs_orders = Nothing
Set rs_cart = Nothing

You may need to double check on whether it would actually lt me do an AddNew with that sql statement and a static recordset. I was trying to minimize overhead and may have gone beyond what it will allow.


In terms of eficiency this is not going to be very pretty. I don't like dealing with recordsets when there are other alternatives, as recordset usually provide the least effient methods for doing things. You would be better of using a stored procedure to do all of the work for you. That would minimize the possibility of two orders being created with the same orderNum as well as increase the efficiency by quite a bit, since the data wouldn't have to be transferred back to let ASP manipulate, then transfered again from the ASP to the db.

Another way to make this more efficient would be to dropte data into an array before pushing it back to the orders table. You would still be in danger of duplication but it would be a little faster.

I also think a small design change of your database could yield you a lot more efficincy. First I would create two tables: Order and OrderEntry. Order would get all of the fields that are for the order as a whole, such as customer id, order date, order number, etc. It would also get a new field to flag it as either a cart or an official order (boolean would be easiest). The OrderEntry table would hold data for each item in an order. ItemId, Qty, etc. It would also hold an OrderId field to link it to the Order table. In the Order table this would be the unique key and would be an autonumber or auto-incrementing number.
The advantages to these changes is that:
a) You don't have duplicate entries for all of the general order information, saving you on storage space and communications time between web site and database
b) Switching a cart top an order could be accomplished by a single SQL update call to change the flag from cart to order
c) if you use the numeric OrderId as part of the order number you display to the end user, you don't ave to worry abpout genrating it on your own or accidentally giving two orders the same number

Anyways, justa few thoughts,

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[onpnt]

THis sounds like a database design flaw.

Is there no way you can use the relation aspects of the database to comply with what it is you need in order to make the interface more efficient?



___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064