looping through records and only update one

Discussion in 'ASP General' started by Roy Adams, Aug 15, 2004.

  1. Roy Adams

    Roy Adams Guest

    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?
     
    Roy Adams, Aug 15, 2004
    #1
    1. Advertising

  2. Roy Adams

    Bob Lehmann Guest

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

    Bob Lehmann

    "Roy Adams" <> wrote in message
    news:...
    > 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 Lehmann, Aug 15, 2004
    #2
    1. Advertising

  3. 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" <> wrote in message
    news:...
    > 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 [SQL Server MVP], Aug 15, 2004
    #3
  4. Roy Adams

    Tarwn Guest

    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
     
    Tarwn, Aug 15, 2004
    #4
  5. Roy Adams

    Roy Guest

    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!
     
    Roy, Aug 15, 2004
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Edward
    Replies:
    1
    Views:
    326
    =?Utf-8?B?UmF2aW5kcmE=?=
    Jun 10, 2004
  2. Alan Silver
    Replies:
    2
    Views:
    11,089
    Alan Silver
    Jul 19, 2005
  3. Emmett Power

    ASP UPDate database looping through form fields

    Emmett Power, May 6, 2004, in forum: ASP General
    Replies:
    4
    Views:
    307
    Emmett Power
    May 7, 2004
  4. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    474
    Ray at
    May 10, 2004
  5. Aaron
    Replies:
    2
    Views:
    542
    dhtml
    Apr 10, 2011
Loading...

Share This Page