view data from MS SQL help

can anyone help with the following:

I have a database in mssql server with a table that contains a field nvarchar(max) and to display the data in a listbox shows me this way …

just shows me that way the data in that field.
and if I show the data in a textarea, shows me the same.

the other data in the other fields show me good.

Thank you for your help.

What do you read in the field if you open the table / record through MS SQL Server Management Studio?

Make sure you DefineEncoding before you put the data in your listbox.

in MS SQL Server Management Studio shows me good field data:

With defineEncoding keeps showing me the raw data understandable

Thank you for your help.

What is the encoding of the string in the database?

is the only field that shows me wrong data.
This field is of data type nvarchar (max) and the database is Modern_Spanish if that’s what you mean by your question

thanks for the help

nvarchar(max) only means that the field is of variable length, up to the maximum size that can be handled by a nvarchar field (8000 characters if I remember well). This is unlikely the cause of your issue.

You may encounter a problem if for example your field holds unicode text, and your string expects ascii text, or vice versa. There is relevant information on this topic in the text encoding topic of the language reference manual.

I would also suggest to check the collation parameter for the field. If is is set to some unusual value, you may end-up with garbled text when you query the table. If not already the case, the collation parameter should be set to database default.

[quote=44745:@Alberto Gomez]is the only field that shows me wrong data.
This field is of data type nvarchar (max) and the database is Modern_Spanish if that’s what you mean by your question

thanks for the help[/quote]
Actually the encoding should be something like UTF-8, UTF-16 etc
Basically it tells the runtime how to interpret a sequence of bytes - some writing systems require more than one byte per character and that can have a HUGE influence on correctly interpreting the bytes received

I have become all fonts handles encodings and does not show me the data well, the only thing it does is change the type of encoding symbols that I put.

Encodings don’t have a lot to do with fonts
It has everything to do with how the BYTE in the data are to be interpreted

Alberto did you get this sorted? I have just come across the same problem. I converted a field from varchar(500) to Text, and now the data is useless in Xojo. Changing the field datatype back to varchar(500) & all works again. Obviously this is fine if the dev has control of the database design, but not good if not.

Wayne, would you post some sample data that works one way and not the other, along with the hex values that you get back from the database? The database shouldn’t be altering the byte values, so there’s something odd going on here.

This is the data returned from the text field E6A194E78DA9E6A4A0E281B3E281A1E6BDAEE695B4E794AEE1A8BEE196BFE98BB100. This is what the sql studio is showing “This is a note.” The detached SQL files are here. The Xojo project is here. Changing the data type for notes to varchar(50) will give you the correct data in the project.

I changed the data type to text and still the problem. in the field I have more than 500 characters.

Wayne, do you get the exact same bytes back from the database, or are they different?

No. Each time I run the program so just reading the database it’s similar, but not the same.

E6A194E78DA9E6A4A0E281B3E281A1E6BDAEE695B4E794AEE1A8BEE196BFE98BB100
E6A194E78DA9E6A4A0E281B3E281A1E6BDAEE695B4E794AEECB099E1839ED0BD00
E6A194E78DA9E6A4A0E281B3E281A1E6BDAEE695B4E794AEE3BB82C7BCE79A9F00
E6A194E78DA9E6A4A0E281B3E281A1E6BDAEE695B4E794AEEC84B3E4B29EE6A79000

And it’s actually getting worse. I have another table in the database that has a column with a data type of varbinary(max). When that column is included in a SQLSelect the debug app just crashes.

Time to find/file a feedback report I think.

<https://xojo.com/issue/30780>