using vba to store special characters in mySQL db

[austin350s10]

I am using a simple SQL UPDATE statement to store userform textbox data into a database. The problem is that when a user types in the character " ' " or " \ " the data is not stored into the database. I don't receive an error it simply does not store the data. I thought it might be an encoding issue but the database column I am attempting to update is encoded 'utf-8'. The only thing I can think of is that " ' " and " \ " are illegal characters and need some sore of special handling in order to be stored properly. I just don't know what type of special handling these characters need or if they can even be stored at all. Has anyone had this problem before and come up with a solution?

 

[eblake]

In your code do a debug.print sqlStatement to see what it looks like. You might have to do a search and replace for the ' character and escape them in the SQL command, replace ' with '' (2 single quotes).

It's a common issue.

If you post the SQL statement I can probably figure out if there might be something else wrong with it.

 

[austin350s10]

That is kind of what I was thinking but i'm not totally sure how to do it.

Here is the snippit of code I am using:

Dim table1 As String, field1 As String, sqlVal As String, sqlVal As String, sqlstr As String

table1 = "testData"
field1 = "idtestData"

' my SQL connector here

sqlVal = txtNotes.Value
    

sqlstr = "UPDATE " & table1 & " SET " & txtNotes.Name & " = '" & sqlVal & "' WHERE " & field1 & " = '2'"

SQL.Execute sqlstr

SQL.close

 

[eblake]

Try this: (in the replace command it replaces the single ' with two ') - The data in the table should only show a single '.

sqlstr = "UPDATE " & table1 & " SET " & txtNotes.Name & " = '" & Replace(sqlVal, "'", "''") & "' WHERE " & field1 & " = '2'"

 

[austin350s10]

That's exactly what I was looking for. Thanks a ton!!!!!