using vba to store special characters in mySQL db

austin350s10

Active Member
Joined
Jul 30, 2010
Messages
321
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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:
Code:
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
 
Last edited:
Upvote 0
Try this: (in the replace command it replaces the single ' with two ') - The data in the table should only show a single '.

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

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top