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