Solved

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

Posted on 2004-03-22
2
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: 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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A short article about problems I had with the new location API and permissions in Marshmallow
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 fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

623 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