trouble with RowSets

  1. 6 weeks ago

    Hello everyone,
    I'm relatively new to object-oriented programming and I am having a very nice experience so far. However, I'm having a bit of trouble with RowSets.

    I'm trying to achieve the following:

    • display a RowSet in a Listbox
    • display the same RowSet in a Messagebox

    What is currently happening:

    • When I run my program, the Listbox is populated as expected, but the messagebox displays an empty string.
    • When I put the MessageBox-code before the Listbox-code, the MessageBox displays the expected string, but the Listbox stays empty

    my code:

    //rs is a RowSet resulting from a SQL query
    Var Message As String
    
    
    ListBox.RemoveAllRows
    For Each row As DataBaseRow In rs
      Listbox.AddRow(Row.ColumnAt(0).StringValue)      //Display RowSet in Listbox
    Next
    
    For Each row As DataBaseRow In rs
      Message = Message + (Row.ColumnAt(0).StringValue)     //Display RowSet in Messagebox
    Next
    MessageBox(Message)

    I would expect both to display the RowSet and I have absolutely no idea why this is not the case. I would be very grateful if you could point me in the right direction.

    Have a very nice day and stay safe!

  2. Markus R

    May 28 Testers, Xojo Pro Europe / Germany / Lower Saxon...
    Edited 6 weeks ago

    use a break point and step through.
    you can see all variables in the debug window.

    how many rows have this rs? and what is in column 0?
    some empty rows maybe?
    check record count or try MoveToFirstRow if there is a bug with the new for each.

    try this if you see something

    Message = "Message:"
    For Each row As DataBaseRow In rs
      Message = Message + "AAA" + row.ColumnAt(0).StringValue
    Next
  3. Sascha S

    May 28 Testers, Xojo Pro Germany, Lower Saxony
    Edited 6 weeks ago

    @Oliver K ListBox.RemoveAllRows
    For Each row As DataBaseRow In rs
    Listbox.AddRow(Row.ColumnAt(0).StringValue) //Display RowSet in Listbox
    Message = Message + (Row.ColumnAt(0).StringValue) //Display RowSet in Messagebox
    Next

    MessageBox(Message)

    Because you would otherwise need to "set back" the RowSet after the first For..Next Loop.
    BTW: Why go throught the RowSet in 2 Loops, when you can do it in 1 ? ;)

    Addendum: Please add a .DefineEncoding( Encoding...) after each String/Text you read from a Database. (Unless it's already UTF8?)
    http://docs.xojo.com/DefineEncoding

  4. Sascha S

    May 28 Testers, Xojo Pro Germany, Lower Saxony

    Just to be complete. After the first Loop you could set back the RowSet using MoveToFirstRow, but only with:

    • SQLiteDatabase
    • OracleDatabase
    • ODBCDatabase

    http://docs.xojo.com/RowSet.MoveToFirstRow

  5. Sascha S

    May 28 Testers, Xojo Pro Germany, Lower Saxony
    Edited 6 weeks ago

    I do not really know the cause of your issue, but my assumption is that the "For Each row As DataBaseRow In rs" causes a MoveToNextRow with each loop. Like in:

    If rs = Nil Then Return
    
    ListBox.RemoveAllRows
    
    While Not rs.AfterLastRow
    
      Listbox.AddRow rs.ColumnAt(0).StringValue.DefineEncoding(Encodings.ISOLatin1) //Display RowSet in Listbox
      Message = Message + rs.ColumnAt(0).StringValue.DefineEncoding(Encodings.ISOLatin1) //Display RowSet in Messagebox
    
      rs.MoveToNextRow
    
    Wend
  6. Markus R

    May 28 Testers, Xojo Pro Europe / Germany / Lower Saxon...
    Edited 6 weeks ago

    for each should handle the cursor move itself or its a bug.
    its possible that is was forgotten.

  7. Sascha S

    May 28 Testers, Xojo Pro Germany, Lower Saxony

    And, try to avoid ColumnAt(...) with RowSet's. Because it's too easy to cause issues if you change the Database Querry. Maybe it's safer to use rs.Column("colum_name").

  8. 5 weeks ago

    Thank you very much for your responses! I'll look into it in more detail tomorrow and come back with my findings.

  9. Tim H

    May 29 Portland, OR USA
    Edited 5 weeks ago

    @Markus R for each should handle the cursor move itself or its a bug.
    its possible that is was forgotten.

    Since moving the cursor is not supported on all databases, it would cause inconsistent behavior. This is one reason I dislike For/Each with RowSet. It's misleading.

  10. Markus R

    May 29 Testers, Xojo Pro Europe / Germany / Lower Saxon...

    @Tim H Since moving the cursor is not supported on all databases, it would cause inconsistent behavior. This is one reason I dislike For/Each with RowSet. It's misleading.

    it could raise a "not support for each" exception. i know xojo can do this with other things.

  11. Tim H

    May 30 Portland, OR USA

    @Markus R it could raise a "not support for each" exception

    If you mean on the second attempt to iterate through them, then yes. But the better name would be AlreadyAtEndException.

  12. Markus R

    May 31 Testers, Xojo Pro Europe / Germany / Lower Saxon...

    or RowSet is Forward only and at End

  13. I think you are correct. The underlying issue seems to be that my RowSet apparently does not support moving the cursor. I ended up sidestepping the issue by iterating over the RowSet once and writing it into an array.

    .DefineEncoding() ended up being very useful aswell. I took a closer look at the database and discovered that there was one table that was not UTF8.

    I'm very grateful for your input. Thank you Sascha, Markus and Tim!

  14. Markus R

    May 31 Testers, Xojo Pro Europe / Germany / Lower Saxon...

    i made a feedback case 60527

  15. Markus R

    Jun 1 Testers, Xojo Pro Europe / Germany / Lower Saxon...
    Edited 5 weeks ago

    got a feedback that it was fixed.

or Sign Up to reply!