updating

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roy Adams (roy_adams_at_ntlworld.com)
Date: 08/14/04 

Date: 14 Aug 2004 08:48:55 -0700

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.CreateObject("ADODB.Recordset");
rsproducts.ActiveConnection = conn_STRING;
rsproducts.Source = "SELECT * FROM " + table ;
rsproducts.CursorType = 0;
rsproducts.CursorLocation = 2;
rsproducts.LockType = 1;
rsproducts.Open();
var rsproducts_numRows = 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.EOF && String(rsproducts.Fields.Item("size").Value) ==
size && String(rsproducts.Fields.Item("colour").Value) == colour &&
String(rsproducts.Fields.Item("ProductName").Value)){

conn = Server.CreateObject('ADODB.Command');
conn.ActiveConnection = conn_STRING;
conn.CommandText = ("UPDATE "+ table + " SET Quantity = Quantity +
'"+Quantity+"' WHERE ProductID='"+ProductID+"'AND
ID='"+String(rsproducts.Fields.Item("ID").Value)+"'" );
conn.Execute();
conn.ActiveConnection.Close();
Response.Redirect("../index.asp");
}else{// if item does not match with others then insert new record
//create sql insert
var sql = "INSERT INTO "+ table +
"(ProductID,ProductName,Price,Quantity,";
sql = sql+ "size,colour,ProductCode,NavID,groupfield)";
sql = sql+" VALUES ('"+ProductID+"','"+ProductName+"','"+Price+"','"+Quantity+"','"+size+"'";
sql = sql+ ",'"+colour+"','"+ProductCode+"','"+NavID+"','1')";

conn = Server.CreateObject('ADODB.Command');
conn.ActiveConnection = conn_STRING;
conn.CommandText = (sql);
conn.Execute();//do the job
conn.ActiveConnection.Close();
Response.Redirect("../index.asp");
                }
I was thinking maybe a loop of some sort but don't quite know
Any ideas?
Thank's in advance
Roy

Relevant Pages

  • Re: access sql question
    ... > to FIELD b in REC2. ... If fields match I need to combine (sum) numeric ... I am new to SQL and am having difficulty. ...
    (microsoft.public.access.queries)
  • Re: Deleting Duplicate Records in a Query
    ... I need to get rid of these duplicates. ... example of a duplicate record. ... Because both fields match at the same time, ... write out the sql statement for me, ...
    (microsoft.public.access.queries)
  • Re: Append results from ADODataset
    ... Clarification ... I'm looking for a simple way to do this with SQL ... statements and a wildcard. ... All fields match in both tables. ...
    (borland.public.delphi.database.ado)