updating

[Roy Adams]

Hi everyone
I'm trying to build a shopping cart app using a db the part I'm stuck
on is the fact that, if someone adds a product that they have
previously added to the cart.
I've got it set up to check whether the size and colour fields match
what's in the db, if they do then you've already added this item with
the same colours and size so, only update the quantity field, if they
don't match, then insert a new record because the size and colours are
different so, this is a different item.

It works when for the first item added but not the second e.i.
I could add
Jacket large blue
then
jacket small red

when i go back and add another Jacket large blue it does what it's
supposed to, only updates the quantity, but when i try the other one
it inserts a new record

Here's the code

table = String(Request. Cookies("table" ));

var rsproducts = Server.CreateOb ject("ADODB.Rec ordset");
rsproducts.Acti veConnection = conn_STRING;
rsproducts.Sour ce = "SELECT * FROM " + table ;
rsproducts.Curs orType = 0;
rsproducts.Curs orLocation = 2;
rsproducts.Lock Type = 1;
rsproducts.Open ();
var rsproducts_numR ows = 0;
// check to see is the item is there, if so, check if size and colour
fields match if so, only update the quantity with the value from the
Quantity field from products page

if (!rsproducts.EO F && String(rsproduc ts.Fields.Item( "size").Val ue) ==
size && String(rsproduc ts.Fields.Item( "colour").Value ) == colour &&
String(rsproduc ts.Fields.Item( "ProductName"). Value)){

conn = Server.CreateOb ject('ADODB.Com mand');
conn.ActiveConn ection = conn_STRING;
conn.CommandTex t = ("UPDATE "+ table + " SET Quantity = Quantity +
'"+Quantity+ "' WHERE ProductID='"+Pr oductID+"'AND
ID='"+String(rs products.Fields .Item("ID").Val ue)+"'" );
conn.Execute();
conn.ActiveConn ection.Close();
Response.Redire ct("../index.asp");
}else{// if item does not match with others then insert new record
//create sql insert
var sql = "INSERT INTO "+ table +
"(ProductID,Pro ductName,Price, Quantity,";
sql = sql+ "size,colour,Pr oductCode,NavID ,groupfield)";
sql = sql+" VALUES ('"+ProductID+" ','"+ProductNam e+"','"+Price+" ','"+Quantity+" ','"+size+"'";
sql = sql+ ",'"+colour+"', '"+ProductCode+ "','"+NavID+"', '1')";

conn = Server.CreateOb ject('ADODB.Com mand');
conn.ActiveConn ection = conn_STRING;
conn.CommandTex t = (sql);
conn.Execute();//do the job
conn.ActiveConn ection.Close();
Response.Redire ct("../index.asp");
}
I was thinking maybe a loop of some sort but don't quite know
Any ideas?
Thank's in advance
Roy

[Tarwn]

I'm not entirely sure how it is working the first time around, my guess would
be luck.

My assumption is that this table your passing around is the name of a
temporary table cretd for that one user, otherwise I am really clueless how
it is working the first time.

Yes, you would need some sort of loop, as right now all your checking
against is tat the new item does not match one of the items in your table
(whichever one happens to appear at the top of the recordset).

I can make some sugestions, but they will be rather general without knowing
more info.

One, I don't suggest opening the whole table and looping through it, but if
you are going to do so you will need to add a few things. Before your if
check to match products against the recordset you need to queue the recordset
up to tthe top record, something like:
if(!rsproducts. EOF)
rsProducts.Move First();
The a simple while !EOF loop would allow you to check agaist all the
records. My advice would be to have a boolean variable and do something like
(warning: my javascript is very rusty):
if(!rsproducts. EOF)
rsproducts.Move First();

var found_match = false
while(!rsproduc ts.EOF){
found_match = found_match ||
(String(rsprodu cts.Fields.Item ("size").Val ue) ==
size && String(rsproduc ts.Fields.Item( "colour").Value ) == colour &&
String(rsproduc ts.Fields.Item( "ProductName"). Value))
}

Now you would just do an if check against the found_math boolean, if there
was a match it will be true and you should update, otherwise there wasn't a
match and you should do an insert.
Now the problems I have with the above method are the following:
1) I don't know if you allow multiple items to be submitted at the same
time, but if so then the ineffiency of looping throught the recordset will be
compounded bvy additional looping through multiple products
2) Looping through a recordset in your code is inefficient
My best suggestion would be to use a stored procedure, if you have the
ability. The stored procedure would accept the arguments for the product
(table name, color, id's, etc), select from the table and then either update
if records were returned or insert if they weren't.

My second best suggesiton would be to implement that samemethod in your
code. Instead of pulling back a recordset and looping through it you could
just as easily execute a SELECT statement:
"SELECT ProductID WHERE ProductID = " + ProductID
If the returned recordset is EOF then insert a new record, otherwise do an
UPDATE
If you have the possibility of adding multiple items to the cart, you could
create a comma-delimited list of the ID's, receive back a recordset
containing the ID's that exist in the table already, then loop through the
new items, either inserting or updating them based on whether their ID's aree
in the returned recordset. Even better than checking against the recordset
would be to loop through it once and create a delimited string so you could
then just check the incoming ID's against the string (using InStr - can't
remember the javascript equivalent) and use that check to decide whether to
insert or update.