Hi all, I’m new to Xojo. I’ve populated a Weblistbox with data from a connected SQL Server database.
Some columns are giving me trouble, as the rows won’t be displayed.
If I replace those columns with dummy data, the rows are displayed properly.
The name column is giving me headaches.
What could be the cause? Your help is much appriciated.
Here’s my code:
UsersList.RemoveAllRows
if Session.VajraDb <> Nil then
Var rowsFound As RowSet
Try
rowsFound = Session.VajraDb.SelectSQL("SELECT Gender, Name, City FROM RelationView;")
var values(2) as String
For Each row As DatabaseRow In rowsFound
values(0) = row.Column("Gender").StringValue
values(1) = row.Column("Name").StringValue
values(2) = row.Column("City").StringValue
UsersList.AddRow (values)
Next
messagebox UsersList.RowCount.ToString
rowsFound.Close
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
end if
If you highlight your code section and press the </> button the code will be formatted nicely, making it easier to read. That said I can’t see any problems with the code.
What does the problem look like? Can you provide a screenshot of the problem?
I would add a UsersList.RemoveAllRows above your loop, to ensure the list is empty before you start adding rows.
If I use this code, I get an empty listbox.
If a replace
values(1) = row.Column("Name").StringValue
by
values(1) = "Test"
it works all fine (except for having “Test” in every row instead of the name, ofcourse)
Btw:
UsersList.RemoveAllRows
is the first line of code.
Are there any special characters in the Name field? Are any of them null? It should easily be able to cope with international text, so long as your database is returning it correctly.
If you put a breakpoint on the AddRow line what is in your array?
Didn’t spot the removeAllRows, my mistake.
The source table has 1000+ rows. Some names do have special characters like ë and ü.
And some rows have NULL values in name.
do you set the WebListBox number of colums to 3 ?
What do you get in your array when Name is null, is it blank or something odd. You may have to check for the null and adjust the array accordingly.
Yep, column count set to 3.
Avoiding NULL values didn’t solve the problem.
rowsFound = Session.VajraDb.SelectSQL("SELECT Gender, isnull(Name,'<empty>') as Name, City FROM RelationView;")
gave the same unwanted result.
Again, can you show a screenshot of a few rows with the problem?
What is the encoding on the array, prior to you adding the row. It could be the database is not returning a string with an encoding. In which case the browsers would not know how to display it correctly.
Place a breakpoint on the UsersList.AddRow (values) line and then look in the Variables panel. Find values and click it. Click on the magnifying glass a the end of the row and see what the Encoding is.
If that is the problem you will need to either fix the database code to return the correct encoding or do:
values(0) = values(0).DefineEncoding( Encodings.UTF8 )
values(1) = values(1).DefineEncoding( Encodings.UTF8 )
values(2) = values(2).DefineEncoding( Encodings.UTF8 )
or what ever encoding your database is using.
This is the result if I select only the id’s < 69.
A soon as I select more id’s, the listbox is completely empty.
The record where id=69 has this name: Flinkenflögel, G.
So, indeed, the special character will be the reason. How can I avoid this?
I take it that nobody has a < or > in their name? That would cause havoc on the web. If they do you need to do:
value(0).replaceall( "<", "<" )
value(0).replaceall( ">", ">" )
so that the HTML parser doesn’t take offence.
Look at the encoding described above. If your encoding in the database is not UTF8 it may be better to do:
values(0) = values(0).DefineEncoding( Encodings.myencoding ).ConvertEncoding( Encodings.UTF8 )
I suppose you have access to the debugger in WEB.
Add a breakpoint (to stop at ID = 69) and look at the values() array contents in the debugger…
Click to display the values as Hex and look there if you have values between 0 thru 1F…
If you find nothing above, start to gather the data from the database at id 69 (and following)…
Your WebListBox have a vertical ScrollBar…
Who build and fill the data base file you gather data from ?
You or someone else ?
Alternatively you can try:
values(0) = row.Column("Gender").StringValue.DefineEncoding( Encodings.UTF8 )
values(1) = row.Column("Name").StringValue.DefineEncoding( Encodings.UTF8 )
values(2) = row.Column("City").StringValue.DefineEncoding( Encodings.UTF8 )
But the Name column is actually ‘Naam’ and the others are also in Dutch
Try
rowsFound = Session.VajraDb.SelectSQL
("SELECT Geslacht as Gender, isnull(Naam,'<empty>') as Name,
Plaats as City FROM RelationView;")
or
rowsFound = Session.VajraDb.SelectSQL
("SELECT Geslacht , isnull(Naam,'<empty>') as Naam,
Plaats FROM RelationView;")
//followed by
values(1) = row.Column("Naam").StringValue
In SQL Server the columns are definined as nvarchar. The database collation is Latin1_General_CI_AS
The best results so far:
For Each row As DatabaseRow In rowsFound
values(0) = row.Column("Gender").StringValue
values(1) = row.Column("Name").StringValue
values(2) = row.Column("City").StringValue
values(0) = values(0).DefineEncoding( Encodings.ASCII ).ConvertEncoding( Encodings.UTF16 )
values(1) = values(1).DefineEncoding( Encodings.ASCII ).ConvertEncoding( Encodings.UTF16 )
values(2) = values(2).DefineEncoding( Encodings.ASCII ).ConvertEncoding( Encodings.UTF16 )
UsersList.AddRow (values)
Next
But now the special characters like ö are treated wrongly.
Flinkenflögel, G. is shown as
Almost there. Where’s the missing part?
So it is not the database collation that matters (well, it does but only for the database sort order). What matters is the Encoding that the column is defined as and the data is stored as.
Issue 1 : The database may be configured to store data in a given encoding, but that generally doesn’t stop users putting data in an inappropriate encoding.
Issue 2 : Retrieving the data may cause a loss of encoding that needs to be put back. Which is what we are doing with the DefineEncoding and ConvertEncoding.
Put a breakpoint on the “values(2) = row.Column(“City”).StringValue” line. You can do that by clicking on the - at the start of the line. It should change to a red tag.
Now run you program. When it gets to that line it should stop and show the debugger. Down at the bottom right is a Variables panel.
values() should be in that list
Click the String(2) part and it will open up like this:
Click the magnifying glass at the end of the name column.
Here you will see your string, what encoding it is in and have the ability to View as different encodings. This will help you work out what encoding the string is in currently. Choose ones to see which displays correctly.
Once you have worked that out, you can use it in the DefineEncoding. I would stick to UTF8 for display purposes.
Try CP850 or windows-1252.
If your column is defined as VarChar then the encoding will be WindowsANSI and you can use the method shared at https://forum.xojo.com/t/mssqlstringvalue-extension-method/68251.
If the column is defined as nVarChar then the encoding could be anything but most likely UTF8 - the n is for national.
This is what I needed! Thank you all!
This is what eventually worked:
values(1) = values(1).DefineEncoding( Encodings.WindowsANSI ).ConvertEncoding( Encodings.UTF8 )
And thanks for the suggestion to wrap it in a function.
