Solved

This loop will not move past the first record in the recordset.

Posted on 2004-10-21
218 Views
Last Modified: 2010-04-06
The code below will loop 13 time but never moves past the first record.



Do While Not rstTicket.EOF
      If NOT rstTicket.BOF Then
      flag = rstTicket("followUpFlag")
      nDate = rstTicket("followUpDate")
      flag = Cint(flag)
            IF flag = 1 AND nDate = DATE Then
            Response.Write("Option 1 Executed<BR>")
                  set Command1 = Server.CreateObject("ADODB.Command")
                  Command1.ActiveConnection = MM_CL2Main_STRING
                  Command1.CommandText = "INSERT INTO dbo.Ticket (ticketID, dateOpened, lastUpdated, createdBy, currentOwner, issue, webFlag, status, category, merchantFK, followUpFlag, followUpDate, priority)  VALUES ( NewID(),'" &nowDate& "','" &nowDate& "','" &created& "',' 2521 ','" &issue& "','" &web& "','" &stat& "','" &category& "','" &merchant& "', 2 ,'" &firstFollowUp& "','" &priority & "')"
                  Command1.CommandType = 1
                  Command1.CommandTimeout = 0
                  Command1.Prepared = true
                  Command1.Execute()


            ELSEIF flag = 2 AND nDate = DATE   THEN
            Response.Write("Option 2 Executed<BR>")
                  set Command1 = Server.CreateObject("ADODB.Command")
                  Command1.ActiveConnection = MM_CL2Main_STRING
                  Command1.CommandText = "INSERT INTO dbo.Ticket (ticketID, dateOpened, lastUpdated, createdBy, currentOwner, issue, webFlag, status, category, merchantFK, followUpFlag, followUpDate, priority)  VALUES ( NewID(),'" &nowDate& "','" &nowDate& "','" &created& "','2522','" &issue& "','" &web& "','" &stat& "','" &category& "','" &merchant& "', 3 ,'" &secFollowUp& "','" &priority & "')"
                  Command1.CommandType = 1
                  Command1.CommandTimeout = 0
                  Command1.Prepared = true
                  Command1.Execute()

            ELSEIF  flag = 3 AND nDate = DATE  THEN
                  Response.Write("Option 3 Executed<BR>")
                  set Command1 = Server.CreateObject("ADODB.Command")
                  Command1.ActiveConnection = MM_CL2Main_STRING
                  Command1.CommandText = "INSERT INTO dbo.Ticket (ticketID, dateOpened, lastUpdated, createdBy, currentOwner, issue, webFlag, status, category, merchantFK, followUpFlag, followUpDate, priority)  VALUES ( NewID(),'" &nowDate& "','" &nowDate& "','" &created& "','2523','" &issue& "','" &web& "','" &stat& "','" &category& "','" &merchant& "', 3,'" &restFollowUp& "','" &priority & "')"
                  Command1.CommandType = 1
                  Command1.CommandTimeout = 0
                  Command1.Prepared = true
                  Command1.Execute()

            END IF

rstTicket.MoveNext

END IF
loop
0
Question by:RonNeiger
    14 Comments
     
    LVL 7

    Expert Comment

    by:Navicerts
    Are you re-running the query every time within the loop?  

    Perhaps what you mean to be doing is running the query and THEN looping through the results?

    -Navicerts
    0
     

    Author Comment

    by:RonNeiger
    That is exactly what i am trying to do.
    My query is outside of the loop.
    0
     

    Author Comment

    by:RonNeiger
    I do not know why it only loops though 13 times when there are 128 records in the recordset.
    0
     
    LVL 2

    Expert Comment

    by:rlovetx
    Try adding:

    rstTicket.MoveFirst

    just above your line:

    Do While Not rstTicket.EOF

    Another option might be to change your:

    Do While Not rstTicket.EOF
    Loop

    To just...

    While Not rstTicket.EOF
    Wend
    0
     
    LVL 7

    Accepted Solution

    by:
    not sure sorry :(

    What i would try to do first is look at the values that i am inserting on each loop through to make sure i am getting unique records each time through.  But i assume you already know what you are trying to input.  Hopefully someone else will be able to help more!

    -Navicerts
    0
     

    Author Comment

    by:RonNeiger
    rlovetx,

    I tried making both changes but it did not help.
    0
     
    LVL 9

    Assisted Solution

    by:mrGreen
    I would strip it down, just make sure your original query returns the ammount of records you expect first:

    make a copy and run this:

    counter = 1

    Do While Not rstTicket.EOF

         If NOT rstTicket.BOF Then
         
       counter = counter + 1
    rstTicket.MoveNext

    END IF
    loop


    response.write counter
    0
     

    Author Comment

    by:RonNeiger
    mrGreen

    I did that and got 130 records it should only be 129
    0
     
    LVL 7

    Expert Comment

    by:Navicerts
    well thats because the counter started at 1 not zero :P
    0
     

    Author Comment

    by:RonNeiger
    Even so the records are there that should be. why will it not loop through all of them?
    0
     

    Author Comment

    by:RonNeiger
    What is the differance between a page and a record in a recordset?
    0
     
    LVL 7

    Expert Comment

    by:Navicerts
    each record is unique as defined by the DB it is being inserted into?

    -Navicerts
    0
     
    LVL 9

    Expert Comment

    by:mrGreen
    That's good 129 or 130 whatever, so that proves the entire recordset is looped.

    now what makes you think it is only going round 13 times?

    I would suggest it may be that the conditions in your IFELSE statement are being met 13 times?




    0
     
    LVL 2

    Expert Comment

    by:rlovetx
    I suspect mrGreen is correct here on all counts.

    Your conditions are quite narrow.  Your flags must be either 1, 2, or 3 AND the dates for those flags must equal today's date.

    If you first get the looping count fixed (per earlier discussion, change counter = 1 to counter = 0) and be happy that you are getting the correct number of results (verify this against your database data).

    I would then print those 129/130 results and--by hand--see how many times your flags and dates would actually meet all of your conditions.  This shouldn't take long using order by Flag, Date in your SQL.

    If you find that only 13 of them are 1 & Date=Today, 2 & Date=Today and 3 & Date=Today, then your code works, as written.

    What you may need to add to your original code is an ELSE line after your last ELSEIF code block to do something with the remaining 117/116 records that are not meeting your conditions.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    bootstrap grid help 9 102
    C# Include Files/Content 7 51
    help with simple xml php 22 91
    Display recordset field HTML encoded 21 35
    There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
    Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now