Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > looping through records and only update one

Reply
Thread Tools

looping through records and only update one

 
 
Roy Adams
Guest
Posts: n/a
 
      08-15-2004
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?
 
Reply With Quote
 
 
 
 
Bob Lehmann
Guest
Posts: n/a
 
      08-15-2004
Because you are redirecting after the first row is evaluated and updated or
inserted.

Bob Lehmann

"Roy Adams" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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?



 
Reply With Quote
 
 
 
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-15-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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
> 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?



 
Reply With Quote
 
Tarwn
Guest
Posts: n/a
 
      08-15-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
 
Reply With Quote
 
Roy
Guest
Posts: n/a
 
      08-15-2004
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!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
looping in array vs looping in a dic giuseppe.amatulli@gmail.com Python 5 09-20-2012 11:58 PM
looping through json array loops through the characters instead ofthe values Aaron Javascript 2 04-10-2011 05:58 PM
Looping through repeater only finds first checked checkbox, not the rest Alan Silver ASP .Net 2 07-19-2005 06:43 PM
any good idea about Update many records in one page, one batch Edward ASP .Net 1 06-10-2004 09:30 AM
Delete records or update records Dan ASP General 1 05-10-2004 01:25 PM



Advertisments