Accessing data from different tables using recordsets

[michellerobbins56]

Hi

I have a sql server database and some asp pages. I have two tables - one is a "quote basket contents" table and one is a "product" table that holds all my product items. I wish to add some product data to the quote basket contents table based on a certain product code (the product code is entered by the user). However first I need to extract the correct product data from the product table based on the product code entered by the user and then insert that product item into the quote basket contents table. I have experience using recordsets to insert data. However how would I do it when there are two tables involved? Normally when I loop through a recordset inserting or manipulating data it normally involves one table. Is there a way to deal with two tables or more at the same time?

Thank you very much for any help.

 

[michellerobbins56]

Essentially I only have one loop to do the data processing as a user selects check boxes which have a product code assigned to them. The loop loops through and gets all the product id's - this I have test and works. I then need to (in the same loop) select the product data from the product table based on the product code selected for the first check box (and so on for each turn of the loop_, and then (in the same loop) insert the found product data into the quote basket contents table...and so on through each turn of the loop. As you can see it is important all to happen in the same loop. Is there a way to do this? What would the syntax be? Is this possible using more than one loop - or do I need to use some kind of nested loop format using more than one recordset to get the product data from the product table and a second recordset to insert the found product data into the quote basket contents table?

Thank you for any help.

 

[Tarwn]

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:

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:

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