looping through records and only update one

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.CreateObject("ADODB.Recordset");
rsproducts.ActiveConnection = conn_STRING;
rsproducts.Source = "SELECT * FROM "+ table +" WHERE
ProductID='"+ProductID+"'";
rsproducts.CursorType = 3;
rsproducts.CursorLocation = 2;
rsproducts.LockType = 1;
rsproducts.Open();
var rsproducts_numRows = 0;

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

conn = Server.CreateObject('ADODB.Command');
conn.ActiveConnection = conn_STRING;
conn.CommandText = ("UPDATE "+ table + " SET Quantity = Quantity
+'"+Quantity+"' WHERE ProductID='"+ProductID+"'AND size='"+size+"' AND
colour='"+colour+"'" ); ////then only update the quantity//
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');//make the sql connection
object and open it here
conn.ActiveConnection = conn_STRING;//connect
conn.CommandText = (sql);
conn.Execute();//do the job
conn.ActiveConnection.Close();//close
Response.Redirect("../index.asp");
}
rsproducts.MoveNext();
}

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

Bob
Sun Aug 15 10:16:48 CDT 2004

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

Bob Lehmann

"Roy Adams" <roy_adams@ntlworld.com> wrote in message
news:131b43be.0408150653.114579ca@posting.google.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.CreateObject("ADODB.Recordset");
> rsproducts.ActiveConnection = conn_STRING;
> rsproducts.Source = "SELECT * FROM "+ table +" WHERE
> ProductID='"+ProductID+"'";
> rsproducts.CursorType = 3;
> rsproducts.CursorLocation = 2;
> rsproducts.LockType = 1;
> rsproducts.Open();
> var rsproducts_numRows = 0;
>
> //problem here//
> while(!rsproducts.EOF){
> if (String(rsproducts.Fields.Item("size").Value) == size &&
> String(rsproducts.Fields.Item("colour").Value) == colour ){//if size
> and colour are the same as what's in the results...
>
> conn = Server.CreateObject('ADODB.Command');
> conn.ActiveConnection = conn_STRING;
> conn.CommandText = ("UPDATE "+ table + " SET Quantity = Quantity
> +'"+Quantity+"' WHERE ProductID='"+ProductID+"'AND size='"+size+"' AND
> colour='"+colour+"'" ); ////then only update the quantity//
> 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');//make the sql connection
> object and open it here
> conn.ActiveConnection = conn_STRING;//connect
> conn.CommandText = (sql);
> conn.Execute();//do the job
> conn.ActiveConnection.Close();//close
> Response.Redirect("../index.asp");
> }
> rsproducts.MoveNext();
> }
>
> as i said before this works but only for the first record in the table
> why isn't it looping through?

Aaron
Sun Aug 15 10:19:04 CDT 2004

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" <roy_adams@ntlworld.com> wrote in message
news:131b43be.0408150653.114579ca@posting.google.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.CreateObject("ADODB.Recordset");
> rsproducts.ActiveConnection = conn_STRING;
> rsproducts.Source = "SELECT * FROM "+ table +" WHERE
> ProductID='"+ProductID+"'";
> rsproducts.CursorType = 3;
> rsproducts.CursorLocation = 2;
> rsproducts.LockType = 1;
> rsproducts.Open();
> var rsproducts_numRows = 0;
>
> //problem here//
> while(!rsproducts.EOF){
> if (String(rsproducts.Fields.Item("size").Value) == size &&
> String(rsproducts.Fields.Item("colour").Value) == colour ){//if size
> and colour are the same as what's in the results...
>
> conn = Server.CreateObject('ADODB.Command');
> conn.ActiveConnection = conn_STRING;
> conn.CommandText = ("UPDATE "+ table + " SET Quantity = Quantity
> +'"+Quantity+"' WHERE ProductID='"+ProductID+"'AND size='"+size+"' AND
> colour='"+colour+"'" ); ////then only update the quantity//
> 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');//make the sql connection
> object and open it here
> conn.ActiveConnection = conn_STRING;//connect
> conn.CommandText = (sql);
> conn.Execute();//do the job
> conn.ActiveConnection.Close();//close
> Response.Redirect("../index.asp");
> }
> rsproducts.MoveNext();
> }
>
> as i said before this works but only for the first record in the table
> why isn't it looping through?

Tarwn
Sun Aug 15 10:49:01 CDT 2004

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='"+colour+"'" 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