Shopping Cart Datatable

Hi- i'm posting this again because i put it in the wrong forum earlier.

I'm currently making a shopping cart application. I have made a class ShoppingCart, this programatically makes a datatable containing the productid and qty of items added to the basket, an instance of the class is stored in a session variable. I have a page called ViewCart.aspx which i want to show all the items in the cart and work out pricing etc... Question is, how do i match the cart datatable with the main products sql server table so that i can show the full product detail in a grid view and not just the guid of the product & qty.

The join i'm looking for in SQL format is:

SELECT tblProducts.*, CART.* FROM tblProducts RIGHT JOIN CART ON tblProducts.ProductID = CART.ProductID;

Any help much appreciated!

Ben

Hi Ben,

You plan to display all records matching the products selected and stored in current shopping cart. Right? These products are stored in an array or other collections objects. It depends on your approach of storing the shopping cart data.

In your sql statement it will display all data matches and not necessarily only products stored in shopping cart.

If you stored teh products selected by customer in an array, use loop to get specific product data, and stored in a new DataTable object. That is what I would do. Hope this helps.

den2005

Hi den2005, thanks for your help.

My shopping cart data is stored in a data table which is kept in an instance of my class ShoppingCart.vb. The class (with cart datatable) is then stored in a session variable to allow it to be passed to all the pages. Heres an example of the tables i want to join:

CART Table - Programatically made and filled by my class ShoppingCart

ID ProductID QTY

=====================

1 4 1

2 2 1

3 3 5

tblProducts Table - Which is stored on an SQL Server

ProductID Title Price Weight

=====================================

1 Test Item1 2.50 50

2 Test Item2 42.00 50

3 Test Item3 10.50 100

4 Test Item 4 1.50 100

And what i then want is to join the two tables to give me:

ID ProductID Title QTY Price Weight

=============================================

1 4 Test Item 4 1 1.50 100

2 2 Test Item 2 1 42.00 50

3 3 Test Item 3 5 10.50 100

Any help greatly appreciated, i'm really having trouble with this!!

Thanks

Ben

When creating a shopping cart, I'm woundering if the Item the user select is better to be inserted in the database or in a session. When in the session you save a lot of database access. When saved in the database you can keep a history of items the user did not buy and maybe send him an email remembering him and giving a small rebate and when he logs again he still have his history of the last cart. Or will people complain about privacy issue.

For your question, you could get all product ID from the session and then make a seperate query to the database getting all product name, id, price from your product ID list.

Hi ThE_lOtUs,

Thanks for your comment. Yes i wasn't sure where to store the cart initially but for the time being (to keep things simple) i'm just going store in a session. Later, i will change it to a database to allow me to see what items have been put in the cart (for marketing purposes etc.), i doubt that holding cart history is a privacy issue but i'm not sure.

Could you give me a little help on how to query the sql database with the cart product IDs?

Thanks for your help!

Ben

Hi Ben,
Like suggested by ThE_IOtUs, if I understand it he whatsyou to create a new table that stores the customer's selected products(cart) in a database table for later retrieval.

den2005