Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-21
14
Medium Priority
?
226 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
Comment
Question by:RonNeiger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 7

Expert Comment

by:Navicerts
ID: 12371372
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
ID: 12371414
That is exactly what i am trying to do.
My query is outside of the loop.
0
 

Author Comment

by:RonNeiger
ID: 12371441
I do not know why it only loops though 13 times when there are 128 records in the recordset.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:rlovetx
ID: 12371483
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:
Navicerts earned 1000 total points
ID: 12371486
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
ID: 12371655
rlovetx,

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

Assisted Solution

by:mrGreen
mrGreen earned 1000 total points
ID: 12371938
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
ID: 12372512
mrGreen

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

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

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

-Navicerts
0
 
LVL 9

Expert Comment

by:mrGreen
ID: 12375120
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
ID: 12378570
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

610 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