looping through records and only update one

R

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.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?
 
B

Bob Lehmann

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

Bob Lehmann
 
A

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
 
T

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

Roy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top