Parameter with apostrophe

[jo15765]

I have a listbox in Excel that is passed as a parameter to a SQL Server stored procedure. The issue I am running into is that some of the values in Excel listbox have an apostrophe, which throws a SQL Server error of
'Incorrect Syntax near ' (the apostrophe)

How can I check the value being passed before it leaves Excel to determine if I should double single quote the value from the listbox?

VBA Question

 

[Norie]

You could check using InStr to check for single quotes or you could just use Replace to double up any single quotes.

 

[jo15765]

You could check using InStr to check for single quotes or you could just use Replace to double up any single quotes.

Should I add a check if the string has a single apostrophe in it, or should I just run the replace function regardless?

Function ReplaceTest()
Dim teststring As String
teststring = "Michael's"
teststring = Replace(teststring, "'", "''")
Debug.Print teststring

teststring = "Michael"
teststring = Replace(teststring,"'","''")
Debug.Print teststring
End Function

The 1st example prints Michael''s and the second prints Michael so no error is thrown if I use the Replace function and the string has no apostrophe in it, just was not sure syntactically what is better. To code the if..else or to just have the replace and if it's needed it's done, if it is not, well it's in play as a catch all.