How to code to transfer information from one table to another (ASP.NET)

Hi,

I'm new to EE as well as asp.net
I'm trying to create an ecommerce website thru the learning process,
so that i can show it as part of my portfolio in later part of my life.
I have a question here...

i have table Cart, Orders, OrderDetails.

under Cart, i have columns as follows... with example entries
CartID, MemberID, ProductID, ProductName, ProductPrice, Quantity
1              77             101            Test1           $20.00           2
2              77             102            Test2           $25.00           3

CartID is the primary key to identiy each row... nothing more than that
1 MemberID can have many CartID

On button click,
how do i transfer all the rows for MemberID 77 from Cart to OrderDetails ?

juz assume that OrderDetails have the following columns:
OrderID, ProductID, ProductName, ProductPrice, Quantity

Forget about OrderID data entries.
OrderID and ProductID are the primary keys.
excuse_pottiAsked:
Who is Participating?
 
beelineukConnect With a Mentor Commented:
The following SQL will insert the Cart information for member 17 into  to the OrderDetails, and erase the data from the Card table...

Begin transaction

insert into OrderDetails (ProductID, ProductName, ProductPrice, Quantity)
(select Cart.ProductID, Cart.ProductName, Cart.ProductPrice, Cart.Quantity
From Cart
Where Cart.MemberID = 17)

Delete Cart
From Cart
Where Cart.MemberID = 17

Commit

...however, you have 2 tables that pretty much contian the same data. Would it not make more sense seperate product information into a products table, and then relate that back to a members tabel with order status column? This way you wouldn't need to move the data, you would just flag the items as either Order or Cart for instance. Off the top of my head something along the lines of...

e.g.
Products Table
--------------
ProductID      ProductName      ProductPrice1              
101            Test1            $20.00
202            Test2            $10.00

Members Table
-------------
MemberID      MemberName ...
17             John
77             Tim

Status Table
------------
Statusid      Description
1      Cart
2      Order Pending
3      Purchased


Purchase Table
--------------      
MemberID       Date            ProductID                     Statusid
17      22/03/2004      101            1
17      22/03/2004      102            1
77            22/03/2004                101            2
0
 
excuse_pottiAuthor Commented:
thx beelineuk.

i see the point in your suggesstion.....
my database design is inefficient.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.