looping through records and only update one

[Roy Adams]

Hi posting again because no answer to previous..
tring to loop through a recordset and update a record, thing is it
only updates the first record in the table rather than searching
through the entire table or records returned, and updating a record if
certain criteria is met.
shouldn't the while loop do this?
I know my syntax must be wrong, but difficult to work out how or where

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

var rsproducts = Server.CreateOb ject("ADODB.Rec ordset");
rsproducts.Acti veConnection = conn_STRING;
rsproducts.Sour ce = "SELECT * FROM "+ table +" WHERE
ProductID='"+Pr oductID+"'";
rsproducts.Curs orType = 3;
rsproducts.Curs orLocation = 2;
rsproducts.Lock Type = 1;
rsproducts.Open ();
var rsproducts_numR ows = 0;

//problem here//
while(!rsproduc ts.EOF){
if (String(rsprodu cts.Fields.Item ("size").Val ue) == size &&
String(rsproduc ts.Fields.Item( "colour").Value ) == colour ){//if size
and colour are the same as what's in the results...

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 size='"+size+"' AND
colour='"+colou r+"'" ); ////then only update the quantity//
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');//make the sql connection
object and open it here
conn.ActiveConn ection = conn_STRING;//connect
conn.CommandTex t = (sql);
conn.Execute();//do the job
conn.ActiveConn ection.Close();//close
Response.Redire ct("../index.asp");
}
rsproducts.Move Next();
}

as i said before this works but only for the first record in the table
why isn't it looping through?

[Bob Lehmann]

Because you are redirecting after the first row is evaluated and updated or
inserted.

Bob Lehmann

"Roy Adams" <ro*******@ntlw orld.com> wrote in message
news:13******** *************** ***@posting.goo gle.com...

Hi posting again because no answer to previous..
tring to loop through a recordset and update a record, thing is it
only updates the first record in the table rather than searching
through the entire table or records returned, and updating a record if
certain criteria is met.
shouldn't the while loop do this?
I know my syntax must be wrong, but difficult to work out how or where

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

var rsproducts = Server.CreateOb ject("ADODB.Rec ordset");
rsproducts.Acti veConnection = conn_STRING;
rsproducts.Sour ce = "SELECT * FROM "+ table +" WHERE
ProductID='"+Pr oductID+"'";
rsproducts.Curs orType = 3;
rsproducts.Curs orLocation = 2;
rsproducts.Lock Type = 1;
rsproducts.Open ();
var rsproducts_numR ows = 0;

//problem here//
while(!rsproduc ts.EOF){
if (String(rsprodu cts.Fields.Item ("size").Val ue) == size &&
String(rsproduc ts.Fields.Item( "colour").Value ) == colour ){//if size
and colour are the same as what's in the results...

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 size='"+size+"' AND
colour='"+colou r+"'" ); ////then only update the quantity//
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');//make the sql connection
object and open it here
conn.ActiveConn ection = conn_STRING;//connect
conn.CommandTex t = (sql);
conn.Execute();//do the job
conn.ActiveConn ection.Close();//close
Response.Redire ct("../index.asp");
}
rsproducts.Move Next();
}

as i said before this works but only for the first record in the table
why isn't it looping through?

[Aaron [SQL Server MVP]]

If this is a SQL Server database, you can do this in a stored procedure and
get rid of this messy loop. I'll assume you have a little bit of interest
in using a SINGLE table instead of using a table for each user.

CREATE PROCEDURE dbo.updateCart
@sessionID INT,
@productID VARCHAR(32),
@size VARCHAR(2),
@colour VARCHAR(12),
@quantity INT
AS
BEGIN
SET NOCOUNT ON

UPDATE CartTable
SET Quantity = @quantity
WHERE
SessionID = @sessionID
AND productID = @productID
AND size = @size
AND colour = @colour

IF @@ROWCOUNT = 0
INSERT CartTable -- I'll let you fill in the rest...
END
GO

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Roy Adams" <ro*******@ntlw orld.com> wrote in message
news:13******** *************** ***@posting.goo gle.com...

Hi posting again because no answer to previous..
tring to loop through a recordset and update a record, thing is it
only updates the first record in the table rather than searching
through the entire table or records returned, and updating a record if
certain criteria is met.
shouldn't the while loop do this?
I know my syntax must be wrong, but difficult to work out how or where

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

var rsproducts = Server.CreateOb ject("ADODB.Rec ordset");
rsproducts.Acti veConnection = conn_STRING;
rsproducts.Sour ce = "SELECT * FROM "+ table +" WHERE
ProductID='"+Pr oductID+"'";
rsproducts.Curs orType = 3;
rsproducts.Curs orLocation = 2;
rsproducts.Lock Type = 1;
rsproducts.Open ();
var rsproducts_numR ows = 0;

//problem here//
while(!rsproduc ts.EOF){
if (String(rsprodu cts.Fields.Item ("size").Val ue) == size &&
String(rsproduc ts.Fields.Item( "colour").Value ) == colour ){//if size
and colour are the same as what's in the results...

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 size='"+size+"' AND
colour='"+colou r+"'" ); ////then only update the quantity//
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');//make the sql connection
object and open it here
conn.ActiveConn ection = conn_STRING;//connect
conn.CommandTex t = (sql);
conn.Execute();//do the job
conn.ActiveConn ection.Close();//close
Response.Redire ct("../index.asp");
}
rsproducts.Move Next();
}

as i said before this works but only for the first record in the table
why isn't it looping through?

[Tarwn]

There are some significant diferences between this code and your previous
post. I responded to theother under the assumption that ProductID was
significant enough to find a single item, it wasn't obvious that color and
size needed to accompany it.

While I still believe stored procdure would be best, Aaron covered this in
dpth enough that I don't feel the need to get into it.

The easiest wayto overcome your curent problem with minimal changes would be
to simply add AND size='"+size+"' AND
colour='"+colou r+"'" to your SELECT statement.
Please also double check your field types, because in your previous post
things like PRoductID were numeric and you have now changed them to strings.

-T

[Roy]

Thank's Tarwn
you were right i took out the while loop and changed the select
statement works a treat thank's again

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!