Please Help

Let me start by saying that this is a custom shopping cart application, built using Visual Web Developer with an SQL 2005 Express database. This is my first real project so most of these things are new to me. For the most part, everything works, you can log in, navigate around and whatever, but the process of adding products to your cart and viewing your cart seems just that bit too hard.

The database has Products, Categories, Manufacturers, Customers and Orders tables (you'll find out the important fields later) and a membership provider has been implemented to store user details in the Customers table, those being Name, Password and E-Mail along with an automatically incremented ID.

An "Administration" section has been set up to allow people to create and modify the products, categories and manufacturers. This generates a menu on the left hand side which has links to "~/Details.aspx?CategoryID={0}" which brings up a page with a GridView control showing information about every item that belongs to the category specified in the QueryString. In this GridView, the important columns are ProductID (which has a Label), Quantity (which has a TextBox) and Add (which has a Button).

In order to add products to a customer's cart, we must do the following, which is where it gets too complicated for me...
- get the user's CustomerName and corresponding CustomerID and enter that in the Orders table
- get the ProductID of the appropriate row (the one where the button was clicked)
- get the value from the Quantity of the appropriate row

For example, finding the CustomerName is easy...

Dim CustomerNameAs String = Context.User.Identity.Name

...but then finding the CustomerID of that user and using it as a parameter in SQL is too hard.

My only idea as far as the ProductID and Quantity goes was to make the "Add" button select the row, then find the information from the selected row.

Answering the above problems would also solve this one, but then when the user goes to view their cart, we have to filter the data to only show the orders with their CustomerID. Again, that means finding the CustomerName, then corresponding CustomerID, then saying "WHERE (Orders.CustomerID = *insert parameter here*).

If someone could help me figure out these issues it would be much appreciated.

Thanks.

Managed to fix the issue regarding the CustomerID and CustomerName, but it's not the nicest of solutions.

Dim UserAs String = Context.User.Identity.NameCustomerName.Text = User

Just takes a hidden TextBox, assigns it the value of the user's name and that's used as a ControlParameter. But, as above, there should be a cleaner way of doing this and besides, it only helps with the "View Cart" page, not adding items to your cart. This is because of the following clause in the SQL...

WHERE (Customers.CustomerName = @.CustomerName AND Orders.CustomerID = Customers.CustomerID)

...but the corresponding CustomerID is still needed for adding products.

Can somebody please help me out here?

It seems a "INSERT INTO...SELECT" statement is required, we're reading up on it at the moment and trying to get it working, but some assistance would still be welcome.

For example, what is the syntax for using "INSERT INTO...SELECT" to get values from another table, while also using parameters from the page in the same statement, something like this?

INSERT INTO Orders (CustomerID, ProductID, Quantity) VALUES ("SELECT CustomerID FROM Customers WHERE CustomerName = @.CustomerName", @.ProductID, @.Quantity)

Obviously, that's the wrong syntax, but it should help to understand what we're trying to do.

Made some progress here, managing to figure out the correct SQL statement (which is below if you're interested).

The problem is, we must use controls from the corresponding row of the GridView, according to which "Add" button was clicked. To do this, we thought it would be easy to just select the row and use GridView.Rows(GridView.SelectedIndex).FindControl and to an extent that works. However, with CommandName="Select" and OnClick="Add", the latter is fired first, which means that the row is not selected.

How can we get the row to be selected before the OnClick event is triggered?

Thanks.

"INSERT INTO [Orders] ([CustomerID], [ProductID], [Quantity]) SELECT [CustomerID], @.ProductID, @.Quantity FROM [Customers] WHERE ([CustomerName] = @.User)"