Solved

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

Posted on 2004-03-22
2
293 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MaxSpan challenge 9 67
Enhanced For loop vs ForEach Loop 4 56
find a node in VST 2 47
C# DataGridView_RowsAdded event not firing 1 39
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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 …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now