Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    20

    Unanswered: wanted: sql shopping cart resources

    hi! I'm still fairly wet behind the ears when it comes to databases, especially sql server 2000. But I just got a copy from my University and I really want to spend some time messing with it. The first thing I want to try is making a shopping cart that stores the items in the database rather than in the session. I'm using asp.net (VB) and I had previously made a simple shopping cart for a SMALL site that stored all the information in a dataset stored in the session... but I've been told that isn't a good idea and reccomended I store the data in the sql database. so that's what I want to do!

    I'm looking for resources that can help me get this started. These are the first questions I have about this approach, and any advice from you experts would be most appreciated!

    1) do I store the data in a temporary table or do I use the regular table and make temporary rows? if the former how do I transfer the rows from the temp table to the order table? if the latter how do I change it from temporary to peromanent? in either case, how do I eliminate rows if the user abandons their cart?

    2) how do I first initialize an order and keep track of the orderID for each user and ensure that no one is assigned a duplicate orderID?

    those are my biggest confusions about this approach. I've looked for books on this but most are either too much about sql syntax and big-time server management, or they are too general and not specific to sql server. I've tried looking online, but I can't find much information on doing this using asp.net 2.0. thanks again for your help!

    -SelArom

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. use a permanent table, and prune abandoned rows with a (daily) scheduled job

    2. use an IDENTITY column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    20
    Quote Originally Posted by r937
    1. use a permanent table, and prune abandoned rows with a (daily) scheduled job

    2. use an IDENTITY column
    thanks for your reply! I'm glad to learn I don't have to go through the trouble of implementing a separate table... in retrospect the answer seems obvious, but I'm still learning...

    as for the IDENTITY column, how do I get the id from the IDENTITY column when I create a new order? I'm thinking that I could make a stored procedure that does something like

    SELECT orderID from (insert blah blah blah into orders)

    is that more or less how it is done? I'm still learning the ways around sql server 2000 so I don't know all the things that are (and are not!) possible. I remember asking this question before and I was told to use the @@IDENTITY but how exactly do you work that into the syntax? whenever a user first adds an item to the cart a new orderid needs to be generated. I'm also assuming that because of this I'll have to make some kind of login system for the user, is that true?

    ahh there is so much to learn and I don't even know how to begin but thank you for your help!

    -SelArom

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    logging in should not be necessary to start a cart, but some would disagree

    use SCOPE_IDENTITY instead of @@IDENTITY -- see Books OnLine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2005
    Posts
    20
    okay i've been messing with this for a few months trying to get it right, and so far it's going pretty good! I've managed to implement the cart using the database by creating an order whenever someone adds something to their cart and returning the identity of the new order which is stored in the session...

    but i see a problem: what if they don't complete the order? I'll have a bunch of orders that will never be completed... i know that I could devise a way to delete old orders, but the order ID is a generated column, so say (theoretically) i get 100 people browsing... that's 100 order rows generated everyday, and the orderid will grow really huge really fast!

    I know there must be a better way to implement this... but i'm not sure how to proceed... does anyone have any advice? thank you!

    -SelArom

  6. #6
    Join Date
    Jul 2005
    Posts
    20
    something happened when I tried to post my reply and it's not showing up so here it is again... sorry about that!

    okay i've been messing with this for a few months trying to get it right, and so far it's going pretty good! I've managed to implement the cart using the database by creating an order whenever someone adds something to their cart and returning the identity of the new order which is stored in the session...

    but i see a problem: what if they don't complete the order? I'll have a bunch of orders that will never be completed... i know that I could devise a way to delete old orders, but the order ID is a generated column, so say (theoretically) i get 100 people browsing... that's 100 order rows generated everyday, and the orderid will grow really huge really fast!

    I know there must be a better way to implement this... but i'm not sure how to proceed... does anyone have any advice? thank you!

    -SelArom

  7. #7
    Join Date
    Jul 2005
    Posts
    20
    okay I've been thinking about it, and i've decided that it's probably best to store the cart in a table in the session, because the only thing i need to store is the productid, and unless they're ordering like thousands of products, I don't think the overhead would be that much, right?

    is this a bad idea?

    -SelArom

    EDIT: thought of an alternative solution... what if I keep the details in the database in a "Details" table, and link them temporarily to an unique id generated for the user, and when they checkout, insert a record into the "orders" table and assign the rows in the details table to that order? i'm really not sure what the correct approach is...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •