My For - Next Loop isn't working

General
1

This is driving me crazy and I cannot see what the issue is, but basically the ‘c’ loop does not increment and the year always remains at 2017. As far as I recall this code used to work fine.

This is Windows and XOJO 2019r1, I have not tried in a later version of XOJO as yet due to the fact I cannot install a later IDE in the office and have no access to my home systems for a day or two.

I have an array ‘Years(4)’ is a string array with four elements, each element is a year title, i.e. Years(1)=2015 to Years(4)=2018

The listbox row, column 0 contains a two letter identifier for the ‘fldFileType’ part of the sql query.

A fresh pair of eyes would be appreciated as I am sure this is something obvious in my coding rather than a bug as when I watch in the debugger and go around the loop ‘c’ always stays at 1 instead of 1,2,3,4. No errors are thrown and the loop runs four times but c never increases?


//dte is the current day and month, i.e. UK format dd/mm/
//the code basically creates a comparison of the last four years data for each file type

dim c, r as integer


for c=1 to 4 //columns
  for r=0 to lbClaimCount.LastIndex //rows
    sql="SELECT Count(tblClaimRecords.fldFileType) as num_claims, tblClaimRecords.fldFileType FROM tblClaimRecords "
    sql=sql+"WHERE tblClaimRecords.fldDateCreated >='01/01/"+years(c)+"' AND tblClaimRecords.fldDateCreated <='"+dte+years(c)+"' "
    sql=sql+" AND tblClaimRecords.fldFileType='"+lbClaimCount.cell(r,0)+"' GROUP BY tblClaimRecords.fldFileType"
    rs1=app.db.SQLSelect(sql)
    
    If app.db.ErrorCode>0 Then
      MsgBox("DB Error: " + app.DB.ErrorMessage)
    End If
    
    if rs1<>nil then
      lbClaimCount.cell(r,c+1)=rs1.field("num_claims").StringValue
      claims=claims+rs1.field("num_claims").IntegerValue
    else
      lbClaimCount.cell(r,c+1)="0"
    end if
  next r
  lbClaimCount.cell(lbclaimcount.lastindex,c+1)=cstr(claims)
  lbClaimCount.CellBold(lbClaimCount.lastindex,c+1)=true
  claims=0
next c
2

Have you tried to reverse the Loops run ?

for r=0 to lbClaimCount.LastIndex //rows for c=1 to 4 //columns …/… next c next r

IMHO: this can explain why the “internal” loop runs only once (maybe).

3

I can’t see anything obvious.

Are you able replicate this in a new test project with just this code?
Comment lines out until your c loop starts working again?
Try it with this above the next c to see what c is just before its range is checked for the loop and see what you get (don’t trust the ide)
system.debuglog(“c=” + str©)

4

debug log is also my friend if something go wrong you can track what happens :slight_smile:

  • i would put the data collect and add data to listbox in separate methods.
    it make the code more understandable, better for debug and you can reuse this methods.
    for debuging you can see what goes in a method and what goes out so you can verify that this part is ok or not.
5

Getting closer, debuglog shows that below. I get the first column data OK, but the remaining 3 years just copy the first years data so getting closer, thanks for your help and alternative thinking. Just need a nudge in a different direction sometimes.

09:52:07 : SELECT Count(tblClaimRecords.fldFileType) as num_claims, tblClaimRecords.fldFileType FROM tblClaimRecords WHERE tblClaimRecords.fldDateCreated >='01/01/2017' AND tblClaimRecords.fldDateCreated <='31/12/2017' AND tblClaimRecords.fldFileType='AF' GROUP BY tblClaimRecords.fldFileType 09:52:08 : c=1 years=2017 SELECT Count(tblClaimRecords.fldFileType) as num_claims, tblClaimRecords.fldFileType FROM tblClaimRecords WHERE tblClaimRecords.fldDateCreated >='01/01/2018' AND tblClaimRecords.fldDateCreated <='31/12/2018' AND tblClaimRecords.fldFileType='AF' GROUP BY tblClaimRecords.fldFileType c=2 years=2018 SELECT Count(tblClaimRecords.fldFileType) as num_claims, tblClaimRecords.fldFileType FROM tblClaimRecords WHERE tblClaimRecords.fldDateCreated >='01/01/2019' AND tblClaimRecords.fldDateCreated <='31/12/2019' AND tblClaimRecords.fldFileType='AF' GROUP BY tblClaimRecords.fldFileType 09:52:09 : c=3 years=2019 SELECT Count(tblClaimRecords.fldFileType) as num_claims, tblClaimRecords.fldFileType FROM tblClaimRecords WHERE tblClaimRecords.fldDateCreated >='01/01/2020' AND tblClaimRecords.fldDateCreated <='31/12/2020' AND tblClaimRecords.fldFileType='AF' GROUP BY tblClaimRecords.fldFileType c=4 years=2020

6

Just a rough guess: You’re adding rows in a Listbox while still working with old .LastIndex in for loop?
Don’t mix your DB I/O and your output in a Listbox. try to grab your data first and then fill the Listbox.

7

Thanks Tomas, I usually do normally but this is such a small set of data it was easier to fill the box with the titles and loop it four times for each year reading the entries from the first column for each unique row (if that makes sense).

Thank you all for the pointers. The fact is that I clearly don’t use system.debuglog() as often as I should :-).

I have tracked down the issue. The issue was in the format of the dates. The data file was originally in MSACCESS and I transferred the data across to sqlite which is not only much faster, but also more robust IMHO.

What I forgot to do was take account of the date format which changed from MSACCESS. i.e. ‘dd/mm/yyyy’, to sqlite, ‘yyyy-mm-dd’. This was masked by the fact that the numbers and queries appeared to be working - although not correctly as each year was showing the same data - not a FOR/NEXT issue but the same search values being returned irrespective of what the sql date searched was.