asked on

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

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

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

RonNeiger

ASKER

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

RonNeiger

ASKER

I do not know why it only loops though 13 times when there are 128 records in the recordset.

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

ASKER CERTIFIED SOLUTION

Navicerts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RonNeiger

ASKER

rlovetx,

I tried making both changes but it did not help.

SOLUTION

mrGreen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RonNeiger

ASKER

mrGreen

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

Navicerts

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

RonNeiger

ASKER

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

RonNeiger

ASKER

What is the differance between a page and a record in a recordset?

Navicerts

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

-Navicerts

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?

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.