webpopup dynamically populated

Targets Web
1

Good morning all.
I’m new with Xojo web development and trying to understand…
I’ve a problem running me crazy… it’s about populating dynamically a webpopupmenu from a mysql query with different categories.
The problem is not the query, everything is working fine…I get all all data I need…
The issue is only when I try to add rows to popup dynamically, using Me.AddRow(categoria). Everything crash… why??
I tried several different systems, but nothing.
Apparently Me.AddRow() accept only “fixed-string”, typed one by one, not dynamically, but it’s hard with flexible needs.

Here my code in PopupMenu / Shown event.

//find my maxID to see how many items to get in the popup
dim massimo as Integer
//Carico dati Tavolo da server
dim aa as MySQLPreparedStatement
aa = mDB_Net.Prepare(“SELECT max(IDCategoria) FROM CategoriaMenu”)
aa.SQLExecute
Dim data As RecordSet = aa.SQLSelect
If data <> Nil Then
While Not data.EOF
massimo = data.IdxField(1).IntegerValue
data.MoveNext
Wend
data.Close
End If

//update my popup based on query
Var i As Integer
For i = 1 To massimo
//Load data from mysql
dim ab as MySQLPreparedStatement
ab = mDB_Net.Prepare(“SELECT Categoria FROM CategoriaMenu WHERE IDCategoria = ?”)
ab.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_LONG)
ab.Bind(0, i)
ab.SQLExecute
Dim data2 As RecordSet = ab.SQLSelect
If data2 <> Nil Then
While Not data2.EOF
dim categoria as String
categoria = data2.IdxField(1).StringValue
Me.AddRow(categoria)
data2.MoveNext
Wend
data2.Close
End If
Next

Any idea? Any suggestion? I’m blocked here.
Thank you so much
Marco

2

What is the specific error message that you’re receiving?

3

No error… simply it stops and in debug you see “The application has gone off-line. Please try again later.”
If I skip //Me.AddRow(categoria)… no issue (but obviously no data in popup…

4

Are you setting the encoding for the data? Try executing these two statements on the database connection before running your query:

set names utf8 collate utf8_general_ci set character set utf8

5

Yes, everything is fine and standard… it’s UTF-8

*General

Project Name: N/A
Server Type: MySQL
Connection Name: *******
Host Name/IP Address: *****
Port: ****
User Name: ****
Save Password: Yes
Encoding: Unicode (UTF-8)
Enable MySQL Character Set: Yes
Use Compression: No

6

Did you try executing those two commands using SQLExecute on your database object?

7

Yes… just did it right now… I added a statement to run these commands before running the queries…
No changes …

8

Interesting. Without seeing your data, I can’t be of any further help. You should unmark the question as answered so others will be more likely to chime in with things I’m not thinking of.

9

If I set a breakpoint, I see everything running fine (except AddRow)… I also tried to move the AddRows outside the sql loop, taking the data from Mysql into a string array first and after, looping the array to get strings out in Me.AddRow(string)… but no changes…

Anyway, thank you Anthony for your trial and support.

10

Just out of curiosity… why are you calling SQLExecute and then SQLSelect on the very next line?

11

Greg, I hope I understood your question… the first Select+Execute is to define how many elements I have to load into popup, for the ForNext loop. The second Select+Execute is to get the elements from database and to add into popup rows (try to add…)

12

But in your code:

dim ab as MySQLPreparedStatement ab = mDB_Net.Prepare("SELECT Categoria FROM CategoriaMenu WHERE IDCategoria = ?") ab.BindType(0,MySQLPreparedStatement.MYSQL_TYPE_LONG) ab.Bind(0, i) ab.SQLExecute // <===== This Line Dim data2 As RecordSet = ab.SQLSelect
You don’t need that SQLExecute. You’re actually doing the SELECT twice, but on the first one you are ignoring the results.

13

Greg, thank you… you’re right… simply I copied a row and forgot to remove the modification… I was too much concentrated on the issue with AddRow and my eyes simply skipped the double SQLselect…
Thank you for notice