trouble with RowSets

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!

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

[quote=490420:@Oliver Koehler]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)[/quote]

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 ? :wink:

Addendum: Please add a .DefineEncoding( Encoding…) after each String/Text you read from a Database. (Unless it’s already UTF8?)
http://documentation.xojo.com/api/text/encoding_text/defineencoding.html

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

  • SQLiteDatabase
  • OracleDatabase
  • ODBCDatabase

http://documentation.xojo.com/api/databases/rowset.html#rowset-movetofirstrow

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:

[code]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[/code]

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

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”).

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

[quote=490433:@Markus Rauch]for each should handle the cursor move itself or its a bug.
its possible that is was forgotten.[/quote]
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.

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

or RowSet is Forward only and at End

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!

i made a feedback case 60527

got a feedback that it was fixed.