? instead of accented letter

General
1

Hi!
I’m getting an issue when using an accented letter(à,è,ì,ò,ù)

I’m using this when inserting in the database

TxtDescrizione.Text.defineEncoding(encodings.UTF8).ReplaceAll("'","''")

and in the db (SQL Server) I found everything ok.

immagine
immagine631×202 16.4 KB

But when I put in grid the record,

LstSegnalazioni.AddRow(rs.IdxField(1).StringValue,rs.IdxField(2).StringValue,rs.IdxField(3).StringValue.defineEncoding(encodings.UTF8)

all the accented letters are showed as ?

immagine
immagine1019×278 19 KB

2

AFAIK, you need to Convert the Encoding before you put it into the Database (if it’s not already using the correct encoding).

Maybe the String ios not UTF8 encoded and you “screw” the encoding before you put it into the Database?

1 Like
3

In the db I see the correct string…

immagine
immagine631×202 16.4 KB

this is my sql table opened in management studio

4

Since we don’t know how Management Studio processes the data, I recommend examining the string before and after DefineEncoding in the debugger. You might see something there that will help you track down the error.

5

You shouldn’t need to define the encoding on data obtained from a UI control. It should already be defined as UTF8.

Reading back from the database however, you will likely need to define the encoding as UTF8 when assigning it to the UI control.

   TxtDescrizione.Text = <field retrieval here>.defineEncoding(encodings.UTF8)

If the database is storing fields using UTF8 and you application is then no conversions should be required. Just ensure that strings have an encoding, typically those returned from a database do not.

6

Hi!

I do this when adding record to the weblistbox

immagine

7

Again :slight_smile:
AFAIK, you need to Convert the Encoding before you put it into the Database (if it’s not already using the correct encoding).

8

[code] ```
stringa_sql=“Insert into Segnalazioni values (”
stringa_sql=stringa_sql+“'”+TxtDataSegnalazione.Text.MidB(7,4)+TxtDataSegnalazione.Text.MidB(4,2)+TxtDataSegnalazione.Text.MidB(1,2)+TxtOraSegnalazione.Text.MidB(1,2)+TxtOraSegnalazione.Text.MidB(4,2)+TxtOraSegnalazione.Text.MidB(7,2)+“',”
stringa_sql=stringa_sql+“'”+TxtCodiceDipendente.Text+“',”
stringa_sql=stringa_sql+“'”+TxtNominativo.Text.defineEncoding(encodings.UTF8).ReplaceAll(“'”,“‘’”)+“',”
stringa_sql=stringa_sql+“'”+SpArea.SelectedRowText+“',”
stringa_sql=stringa_sql+“'”+TxtDataSegnalazione.Text+“',”
stringa_sql=stringa_sql+“'”+TxtOraSegnalazione.Text+“',”
stringa_sql=stringa_sql+“'”+TxtLuogo.Text.ReplaceAll(“'”," “)+”‘,"
stringa_sql=stringa_sql+"’“+RdgTipoNc.SelectedItem.Caption+”‘,"
stringa_sql=stringa_sql+"’“+SpClassificazione.SelectedRowText+”‘,"
stringa_sql=stringa_sql+"’“+TxtDescrizione.Text.defineEncoding(encodings.UTF8).ReplaceAll(”‘“,”’‘“)+”’,"
stringa_sql=stringa_sql+“'”+SpPriorita.SelectedRowText+“',”
stringa_sql=stringa_sql+“‘Inviata’,”
stringa_sql=stringa_sql+“‘’,”
stringa_sql=stringa_sql+“‘’)”


this is done before doing the insert in the database
9

I’d like to suggest a more understandable version of your code. This will make it easier to read it in the forum and help you.

// Helper function for masking single quotes in text fields
Function SanitizeSQLText(input As String) As String
  Return input.ReplaceAll("'", "‘’").DefineEncoding(Encodings.UTF8)
End Function

// Helper function for creating a timestamp in the format YYYYMMDDHHMMSS
Function GetTimestamp(dateText As String, timeText As String) As String
  Return dateText.MidB(7,4) + dateText.MidB(4,2) + dateText.MidB(1,2) + _
         timeText.MidB(1,2) + timeText.MidB(4,2) + timeText.MidB(7,2)
End Function

// Start by building the SQL statement
Var timestamp As String = GetTimestamp(TxtDataSegnalazione.Text, TxtOraSegnalazione.Text)

Var values() As String = Array( _
  "'" + timestamp + "'", _
  "'" + TxtCodiceDipendente.Text + "'", _
  "'" + SanitizeSQLText(TxtNominativo.Text) + "'", _
  "'" + SpArea.SelectedRowText + "'", _
  "'" + TxtDataSegnalazione.Text + "'", _
  "'" + TxtOraSegnalazione.Text + "'", _
  "'" + TxtLuogo.Text.ReplaceAll("'", " ") + "'", _
  "'" + RdgTipoNc.SelectedItem.Caption + "'", _
  "'" + SpClassificazione.SelectedRowText + "'", _
  "'" + SanitizeSQLText(TxtDescrizione.Text) + "'", _
  "'" + SpPriorita.SelectedRowText + "'", _
  "'Inviata'", _
  "''", _
  "''" _
)

Var stringa_sql As String
stringa_sql = "INSERT INTO Segnalazioni VALUES (" + Join(values, ", ") + ")"
1 Like
10

Don’t build sql like that. It is very dangerous. Read about sql injection.

The user interface works in UTF8, check that before you do anything else. If it is something else, then a convert will be required.

When opening the database tell it you are using UTF8, so the engine doesn’t convert it to something else. For MySQL use (Set names “UTF8”) for example.

Ensure that your database is built to use UTF8. Check storage types and sorting methods.

When reading data back from the database ensure everything has a defined encoding and it is UTF8. If it is nil define it. If the data is still wrong check the database and driver is working in UTF8.

There should be other threads with solutions in the forum already. This comes up a lot.

4 Likes
11

@Ian_Kennedy ist talking about this.

He correctly recommends something more like this:

db.SQLExecute("INSERT INTO Segnalazioni VALUES (?,?,?,?,?,?,?,?,?,?,?)", _
timestamp, _ 
TxtCodiceDipendente.Text, _
SanitizeSQLText(TxtNominativo.Text), _
SpArea.SelectedRowText, _
TxtDataSegnalazione.Text, _
TxtOraSegnalazione.Text), _
TxtLuogo.Text.ReplaceAll("'", " "), _
RdgTipoNc.SelectedItem.Caption, _
SpClassificazione.SelectedRowText, _
SanitizeSQLText(TxtDescrizione.Text), _
SpPriorita.SelectedRowText, _
"Inviata")
1 Like
12

Thanks Sascha I only have my phone with me. So can’t provide fuller examples.

2 Likes