single quote in data messing with single quote in SQL 1

[DougP]

I have this as a customer name
Ms. C'Etter Sneed

and this code throws the error:
Syntax error (missing operator) in query expression ''Ms. C'Etter Sneed' as Name ....

SQLCode = SQLCode & Chr(39) & curcust.Name.GetValue & Chr(39) & " AS Name, "

How can I change it to allow a single quote in the data?


TIA

DougP

 

[genomon]

You need to REPLACE() the single quotes in your data with two consecutive single quotes. This an MS convention, and SQL Server will then play nicely with your data.


"Business conventions are important because they demonstrate how many people a company can operate without."

 

[DougP]

Oh Yeah Baby
Shes playing nice now!!
have a Star !!!
heres what I did

         If InStr(1, curcust.Name.GetValue, "'") Then
                                NewName = Replace(curcust.Name.GetValue, "'", "''")
                                SQLCode = SQLCode & Chr(39) & NewName & Chr(39) & " AS Name, "
                            Else
                                SQLCode = SQLCode & Chr(39) & curcust.Name.GetValue & Chr(39) & " AS Name, "
                            End If

DougP

 

[MicroZealous]

This reminds me of a related problem I had once, when importing a text file into a payroll application. The root cause was obvious when I realized the import failed when it reached "John Smith, Jr." !

 

[SgtJarrow]

Just an FYI, the REPLACE function will basically perform the IF for you.... You could change all the above code to just:

SQLCode = SQLCode & Chr(39) & REPLACE(curcust.Name.GetValue, "'", "''") & Chr(39) & " AS Name, "

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer