Solved

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

Posted on 2004-03-22
2
297 Views
Last Modified: 2010-04-17
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.
0
Comment
Question by:excuse_potti
2 Comments
 
LVL 2

Accepted Solution

by:
beelineuk earned 125 total points
ID: 10652269
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
 

Author Comment

by:excuse_potti
ID: 10655210
thx beelineuk.

i see the point in your suggesstion.....
my database design is inefficient.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Rubik's Cube Code for Effective Presentation 3 74
ejb example issues 3 47
jboss 7.1 start up error 1 58
backup program with robocopy 6 69
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question