WebListBox adding rows issues

Gilles_Plante · July 14, 2019, 9:45pm

Hi all,

I run Xojo 2019 r1.1 on Windows 10 1903 to create a small test web app and run into an issue. The WebListBox height is 6 rows, and data comes from a MySQL database.

I have a method that loads the WebListBox (data is retrieved in another method and the RecordSet is passed as parameter the the Fill method. Here is the code:

[code]Me.CustomersList.DeleteAllRows

MsgBox("FillCustomersList dit: nb enr = " + rs.RecordCount.ToText)

While Not rs.EOF

Me.CustomersList.AddRow(rs.IdxField(2).StringValue, rs.IdxField(3).StringValue, _
rs.IdxField(4).StringValue, rs.IdxField(5).StringValue, rs.IdxField(6).StringValue)

rs.MoveNext

Wend

Me.Message.Text = “While handling finished !” //’ This line is not executed

rs.Close

Return True[/code]

The Message text label is not changed, and looking at the debugging window EOF never gets True. Since the MsgBox just before the While loop is not displayed, my guess is that the method never ends.

If I instead of using the While loop I used that code:

[code]rs.MoveFirst

Me.CustomersList.AddRow(rs.IdxField(2).StringValue, rs.IdxField(3).StringValue, _
rs.IdxField(4).StringValue, rs.IdxField(5).StringValue, rs.IdxField(6).StringValue)

rs.MoveNext

Me.CustomersList.AddRow(rs.IdxField(2).StringValue, rs.IdxField(3).StringValue, _
rs.IdxField(4).StringValue, rs.IdxField(5).StringValue, rs.IdxField(6).StringValue)

Me.Message.Text = “While handling finished !”[/code]
The first two rows are displayed and the Message label text is changed.

As another test, I add LIMIT 6 at the end of the query and add rows to the List BOX with this code:

[code]For i = 1 To rs.RecordCount

Me.CustomersList.AddRow(rs.IdxField(2).StringValue, rs.IdxField(3).StringValue, _
rs.IdxField(4).StringValue, rs.IdxField(5).StringValue, rs.IdxField(6).StringValue)

rs.MoveNext

i = i + 1

Again the label text is changed, but I only get 3 rows displayed instead of 6 - the query returns 20 rows.

Just what is going wrong with the WebListBox ?

Thanks

Gilles Plante

AlbertoD · July 14, 2019, 11:50pm

For this code:

For i = 1 To rs.RecordCount Me.CustomersList.AddRow(rs.IdxField(2).StringValue, rs.IdxField(3).StringValue, _ rs.IdxField(4).StringValue, rs.IdxField(5).StringValue, rs.IdxField(6).StringValue) rs.MoveNext i = i + 1 Next
you need to remove

i = i + 1

because i will only run for 1, 3 and 5.

Gilles_Plante · July 15, 2019, 2:03pm

Hi Alberto,

good catch !

When I remove the line, it doesn’t work (same thing the app seems lost somewhere)

If on top of that I change to For = 1 to 4 I get the first 4 records. And If I change 4 to 5 or 6, the issue is back ?!?

I thought that the WebListBox could be buggy. So I added a WebArea and displayed the record contents in there, records separated by EndOfLine. The issue remains, I can display the first 4 records, if I try 5 records then it doesn’t work.

I have no idea what goes wrong.

Thanks

Louis_D · July 15, 2019, 2:33pm

I would code it this way (it will work regardless of the number of records in the recordset) This is the way you had it originally. I prefer to avoid message boxes as debugging tools. Instead, you could also place system.debuglog str(RS.Recordcount) before your loop, to validate that the number of records corresponds to the number of rows in the listbox :


system.debuglog str(RS.Recordcount)

RS.Movefirst  ' not normally required, but I like to be double sure

While not RS.EOF  'EOF is the marker for the end of the recordset
  
  Me.CustomersList.AddRow(rs.IdxField(2).StringValue, rs.IdxField(3).StringValue, _
  rs.IdxField(4).StringValue, rs.IdxField(5).StringValue, rs.IdxField(6).StringValue)
  
  rs.MoveNext

Wend

Place a breakpoint anywhere in the While loop and examine every element of RS. You may find that you don’t have the recordset that you expected, and therefore the issue is elsewhere. (select is wrong, there is nil data somewhere, etc.)

Is some field nil? if so, then a try-catch structure may be in order, where you test each value first, replace invalid data with an empty string or other suitable string, and then you proceed with adding the row with the validated data. In some cases where I don’t control the data, I test every single column in the record before committing it to the UI. Nil or otherwise invalid data is not rare.

Gilles_Plante · July 15, 2019, 4:13pm

Bonjour Louis,

there are some fields which are Null. Here is the new code:

[code]MsgBox("FillCustomersList dit: nb enr = " + rs.RecordCount.ToText)

While Not rs.EOF

Dim FieldVal(5) As String

For i As Integer = 2 To 7

If rs.IdxField(i).Value Is Nil Then
  
  FieldVal(i - 2) = ""
  
Else
  
  FieldVal(i - 2) = rs.IdxField(i).StringValue
  
End If

rs.MoveNext

Wend

Me.Message.Text = “While handling finished !”[/code]
I still use LIMIT 6 for the query. and again that doesn’t work. The maximum I can go is LIMIT 5.

Looking at the Variables window, this is what I get for RuntimeException:

Error Number 0 Message Encountered invalid character Reason Encountered invalid characterShouldn’t Error 0 mean no error ?

I don’t trap exceptions, so if one is thrown, the app should stop but it doesn’t,

I am lost.

Thanks

Peter_Greulich · July 15, 2019, 5:07pm

Maybe your issue is similar to the snag I hit and resolved thanks to all the help. In particular, see Norman’s post at the end of this:

https://forum.xojo.com/54615-code-error-there-is-more-than-one-item-with-this-name

Louis_D · July 15, 2019, 5:21pm

It is probably a mySQL error that is passed to Xojo.

If you look at the recordset in debug, you will probably find that you have data for the first 5 records, but no sixth record. The error stops the process on mySQL.

Gilles_Plante · July 15, 2019, 11:52pm

Since the application quits without explanations - I consider that a bug - I added a UnhandledException handler to App object that displays a MsgBox with the exception and stack and did a step by step execution. Now I know:

The exception type is Xojo,Data.GenerateJSON.
Now that I added that handle the Xojo Unhandle Exception is displayed - ??? - and state that Encountered invalid character.

Googling this I found out .

So looks like that could be a encoding problem.

The database I am using is the northwind that is an Access database converted to MySQL.

Tried it on a Mac, same thing - Ok with LIMIT 4 - but I couldn’t get my Unhandled exception displayed.

Peter_Greulich · July 16, 2019, 12:04am

[quote]and state that Encountered invalid character.
…
So looks like that could be a encoding problem.[/quote]

Right, so it sounds like the same problem I had. Did you look at the link I suggested?

AlbertoD · July 16, 2019, 12:47am

[quote=445350:@Peter Greulich]Maybe your issue is similar to the snag I hit and resolved thanks to all the help. In particular, see Norman’s post at the end of this:

https://forum.xojo.com/54615-code-error-there-is-more-than-one-item-with-this-name[/quote]
Fixed your link.

If you paste the URL the forum will try to auto-parse the link, if you first click the link button to create a link, you must change the http://example.com URL that the forum put by default and not just fill the URL between the new code.

If you click the link icon, you get:

[url=http://example.com]link text[/url]

you have to change both, the example.com and the link text.

I think Peter is talking about this particular post from Norman.

Peter_Greulich · July 16, 2019, 2:14am

Thank you Alberto. Yes that is the correct link.

Here

Greg_O_Lone · July 16, 2019, 12:36pm

App.UnhandledException doesnt support MsgBox because execution stops and no data can be sent to the browser.

JSON requires the encoding be UTF-8 and when getting data from a database, you are required to set the encoding.

Gilles_Plante · July 17, 2019, 3:13am

@Peter Greulich

Yes I had a look at the link.

@Greg O’Lone

The code was changed to this:

[code]Dim FieldVal(5) As String

While Not rs.EOF

System.DebugLog "EOF = " + CStr(rs.EOF)
System.Debuglog "Nb champs = " + rs.FieldCount.ToText

For i As Integer = 2 To 7

If rs.IdxField(i).Value Is Nil Then
  
  FieldVal(i - 2) = ""
  
Else
  
  FieldVal(i - 2) = rs.IdxField(i).StringValue
  
End If

FieldVal(i - 2) = DefineEncoding(FieldVal(i - 2), Encodings.UTF8)

rs.MoveNext

Wend[/code]

I use breakpoints and step by step to execute:

the 6 records are processed
EOF gets true
and when the while is finished the application seems to be frozen, nothing is displayed.

This time I don’t get any message and again Exception is set to 0.

There is no way I can learn which line of code goes bananas.

Peter_Greulich · July 17, 2019, 2:13pm

I would try changing:

FieldVal(i - 2) = DefineEncoding(FieldVal(i - 2), Encodings.UTF8)

to:

FieldVal(i - 2) = DefineEncoding(FieldVal(i - 2), Encodings.WindowsLatin1)

I had tried UTF8 on mine and it wouldn’t work either. It seems like the list expects WindowsLatin1.

Gilles_Plante · July 17, 2019, 3:54pm

@Peter Greulich

You’re right, that works with WindowsLatin1 !!!

The database collation is latin_swedish_ci, I guess the guy who transposed from Access to MySQL was Swedish.

Thanks a lot