I would suggest not copying all of the product data from the Product table over to the basket table. You woul dbe better off have a unique key (product key maybe?) in the Product table and then copying tatto your basket. This cuts down on redundancy of data in your database, leaving less room for error and saving storage space as well.
Also, rather than using Recordset objects for everything, you should consider starting to learn some basic SQL syntax. Generally just about everything is faster with the ADO objects if you use SQL statements to do your searching, updating, etc. rather than looping through recordset objects. Databases are made to do two things: store data and search it.
So back to your question above. The easiest way to copy the data from one table to another from your code would be to select a single record out of the first table and then create an insert statement to push it into the second table. This can be done like so:
Code:
Dim obj_conn, rs_product, str_sql
Set obj_conn = Server.CreateObject("ADODB.Connection")
obj_conn.Open "your connection string"
'pretend the user's product key was passed in Request.Form("prodKey")
Dim productKey
productKey = Request.Form("prodKey")
'since we're going to use that field in a SQL statement, escape any single quotes by doubling them
productKey = Replace(productKey,"'","''")
'--- Pull back the product for the given key
' obviously you would have to fix the field and table names, i'm just making some up (and assuming product key is a text field)
str_sql = "SELECT productKeyField, productNameField, etc FROM ProductTable WHERE productKeyField = '" & productKey & "'"
Set rs_product = conn_obj.Execute(str_sql)
'--- Check if we actually got any records back
If rs_product.EOF Then
'product not found - show a message and stop processing
Response.Write "Sorry, that product was not found."
Response.End
End If
'--- create an insert statement to insert the basket record
' Inserts looks like: INSERT INTO TableName(fieldName1,fieldName2,etc) VALUES(value1,value2,etc)
str_sql = "INSERT INTO BasketTable(quantity, productKeyField, productNameField) VALUES("
str_sql = str_sql & Request.Form("QuantityField") & "," 'numbers do not get quotes around them
str_sql = str_sql & "'" & rs_product("productKeyField") & "'," 'assuming this field is a text field - tey get single quotes around them
str_sql = str_sql & "'" & rs_product("productNameField") & "')" 'end paranthese for the VALUES() section
'--- Execute the insert statement to insert this new record into the Basket table
obj_conn.Execute str_sql
'--- Cleanup remaining objects
Set rs_product = Nothing
obj_conn.Close
Set obj_conn = Nothing
That would be the basic logic for copying some fields from one table to the other. Again, I would suggest you limit the copying to a single key field. If you do this than you can always retrieve the data back out later by querying for a JOIN of the data in the two recordsets.
Pretend Product Table has the following fields:
productId (autonumber)
productKey (text)
productName (text)
productDesc (text)
Pretend Basket table has the following fields:
basketId (autonumber)
productId (number) - links a record in basket to one record in Product
basketQty (number)
Now, if you wanted to print out a nice display of all the products in the Basket, complete with product name, you would need data from both table. A JOIN between two tables lets you specify how you would like the dastabase to combine data in a meaningful manner from both tables. There are several types, but the one we need in this situation is called an INNER JOIN. The Syntax of a SELECT statement with an INNER JOIN would be something like:
SELECT [table1].[field1],[table1].[field2],[table2].[fieldA],etc
FROM [table1] INNER JOIN [table2] ON [condition]
The [condition] is a rule that you make to define a relationship between the two tables. Something like [table1].[field1] = [table2].[fieldA] so that the database knows how to put the data together.
An INNER JOIN to show products in the basket table (based on my sample tables above) would look something like:
Code:
str_sql = "SELECT productKey, productName, basketQty FROM (Product INNER JOIN Basket ON Product.productId = Basket.productId) ORDER BY Product.productName"
I added an order to make it look pretty and alphabetical when we output it.
In any case, i know I got long winded here, but i hope some of it is of use to you.
-T