• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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
0
RonNeiger
Asked:
RonNeiger
  • 6
  • 4
  • 2
  • +1
2 Solutions
 
NavicertsCommented:
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
 
RonNeigerAuthor Commented:
That is exactly what i am trying to do.
My query is outside of the loop.
0
 
RonNeigerAuthor Commented:
I do not know why it only loops though 13 times when there are 128 records in the recordset.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
rlovetxCommented:
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
 
NavicertsCommented:
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
 
RonNeigerAuthor Commented:
rlovetx,

I tried making both changes but it did not help.
0
 
mrGreenCommented:
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
 
RonNeigerAuthor Commented:
mrGreen

I did that and got 130 records it should only be 129
0
 
NavicertsCommented:
well thats because the counter started at 1 not zero :P
0
 
RonNeigerAuthor Commented:
Even so the records are there that should be. why will it not loop through all of them?
0
 
RonNeigerAuthor Commented:
What is the differance between a page and a record in a recordset?
0
 
NavicertsCommented:
each record is unique as defined by the DB it is being inserted into?

-Navicerts
0
 
mrGreenCommented:
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
 
rlovetxCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now