Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-03-22
2
Medium Priority
?
305 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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
No other job is as rewarding and demanding as building an iPhone app is. It is not really in the hands of the developer for the success of an iPhone app. Many factors operate jointly for every iOS application's success in the market.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project

577 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