List box still loads data I don't want?

General
1 
// Variables defined here...
Var rsData As RowSet = dbJobLogSp.SelectSQL( "SELECT * FROM tblTankLocations WHERE address=? ORDER BY status ASC, manufacture ASC", "18535 5th Ave" )
Var sStatus As String = rsData.Column( "status" ).StringValue

// Where the magic happens... or breaks...
lstBxTanks.RemoveAllRows
Try
  If( rsData <> Nil ) Then // So long as there is a record return it then close the rowset...
    For Each row As DatabaseRow In rsData
      If( sStatus = "Decommisioned" )Then
        // Do not a thing...
      Else
        lstBxTanks.AddRow( rsData.Column( "rNum" ).StringValue )
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 1 ) = rsData.Column( "serNum" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 2 ) = rsData.Column( "manufacture" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 3 ) = rsData.Column( "size" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 4 ) = rsData.Column( "tankDate" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 5 ) = rsData.Column( "status" ).StringValue
      End If
      sStatus = rsData.Column( "status" ).StringValue
    Next
  Else
    MessageBox( "What no records?!" )
  End If
  rsData.Close
Catch error As DatabaseException
  MessageBox( "Error: " + error.Message )
End Try

I don’t know why the record still loads when I explicitly tell it not to. This is driving me bonkers trying to sort this out! Linux Kubuntu 26.04 LTS using Sqlite as the DB.

2

A couple things that it could be…

You’re setting sStatus before the loop, and then again at the end of the loop. So you might be testing the previous record instead of the current one. Best practice is to set sStatus at the top of the for loop (especially since “for each” does not guarantee order).

Also “Decommissioned” may be spelled wrong in your code (it usually has two s’s). Depends how it’s entered in your database, obviously.

3

memory this status direkt after open the recordset make no sense, it is just the status of the first record.

and why not filter the status in the sql statement?

and status <> 'Decommisioned'

4 

I use instead a variable Row_Idx As Integer, then:


lstBxTanks.AddRow( rsData.Column( "rNum" ).StringValue )

Row_Idx = lstBxTanks.LastAddedRowIndex
lstBxTanks.CellTextAt( Row_Idx, 1 ) = rsData.Column( "serNum" ).StringValue

and so on.

“Faster“ to do and easier to read.

5

See how the sStatus has moved inside the loop?

6

Oh, I will look into that in a bit. Right now I am attempting to create a formatted document for reporting purposes… this is a new pain I am now inflicting upon myself.

7 
// Variables defined here...
Var rsData As RowSet = dbJobLogSp.SelectSQL( "SELECT * FROM tblTankLocations WHERE address=? ORDER BY status ASC, manufacture ASC", "18535 5th Ave" )

// Where the magic happens... or breaks...
lstBxTanks.RemoveAllRows
Try
  If( rsData <> Nil ) Then // So long as there is a record return it then close the rowset...
    For Each row As DatabaseRow In rsData
      If( rsData.Column( "status" ).StringValue <> "Decommisioned" )Then
        lstBxTanks.AddRow( rsData.Column( "rNum" ).StringValue )
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 1 ) = rsData.Column( "serNum" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 2 ) = rsData.Column( "manufacture" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 3 ) = rsData.Column( "size" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 4 ) = rsData.Column( "tankDate" ).StringValue
        lstBxTanks.CellTextAt( lstBxTanks.LastAddedRowIndex, 5 ) = rsData.Column( "status" ).StringValue
      Else
        // Skip it..
      End If
    Next
  Else
    MessageBox( "What? No records?!" )
  End If
  rsData.Close
Catch error As DatabaseException
  MessageBox( "Error: " + error.Message )
End Try

Okay so I changed how I look for the condition and I still get unwanted records in the listbox…

Screenshot_20260817_072023
Screenshot_20260817_0720231125×698 111 KB

8

You believe that no row should be added if the status field holds the word ‘Decommisioned’

And if the field actually contains that word, your code would be correct.

So since these rows appear to be added anyway, the obvious reason is that the field does not contain EXACTLY what you are testing for.

And that is because it contains Decomissioned but you are filtering out Decommisioned
one has two S’s and one M, while the other has one S, and 2 M’s

9

Which is why you either use a constant or even better an enumeration for jobs like this.

10

if you interate with for each then use the object row not something else even if it seems to work with rsData cursor position.

if there is no rows for each will do nothing.
empty record set is similar to if rsData.AfterLastRow=true

rsData <> Nil is needless but a try catch for SelectSQL is useful.

i would put the Try few rows above and RemoveAllRows for the list before the query.

btw. it seems like you never use break points and step through in debug view.
or you can also use break command at a condition.

11

Oh… spelling is so important… yeah, I see the issue. There were several that were spelled wrong… derp. Thank you.

12

If( rsData.Column( "status" ).StringValue <> "Decommisioned" )Then

Why even do the filtering in Xojo?

SQL is your friend here… lose the unwanted records before you even do the loop.

Try something like

Var rsData As RowSet = dbJobLogSp.SelectSQL( "SELECT * FROM tblTankLocations WHERE address=? and status NOT LIKE ‘Decom%‘ ORDER BY status ASC, manufacture ASC", "18535 5th Ave" )

13

Because… I didn’t even consider that… hmm, thanks Jeff! I will look at this method and add it to my growing list of how to do things.

14

@John_McDonald
whatever database you are using let it do the heavy work for you.