VB.NET App with SQL SELECT WHERE and Single Quote in Field

I have the following code in a VB.NET Web Form app that searches for names in a FileTable:
Dim connectionString As String = "Data Source=AV-W12-ROMS-1;Initial Catalog=RESUMES;Integrated Security=True"
Dim sql As String = ""
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand
sql = "SELECT IsNull(COUNT(1),0) as 'count' FROM RESUMES.dbo.ftbl_resume_files WHERE CONTAINS([name],'" + search_name + "')"
connection.Open()
cmd.Connection = connection
cmd.CommandText = sql
name_count = cmd.ExecuteScalar()
connection.Close()

Open in new window

I ran into an error when one of the search_name field values was O'leary because of the single quote.  How can I avoid it but include it in the search?
wchestnutAsked:
Who is Participating?
 
Carl TawnSystems and Integration DeveloperCommented:
If you're using inline SQL then you need to escape the single apostrophe with a double apostrophe:
sql = "SELECT IsNull(COUNT(1),0) as 'count' FROM RESUMES.dbo.ftbl_resume_files WHERE CONTAINS([name],'" + search_name.Replace("'", "''") + "')"

Open in new window

Although a parameterised query would be a neater, and safer, option.
0
 
Dan CraciunIT ConsultantCommented:
How about enclosing it between "" instead of '':
sql = "SELECT IsNull(COUNT(1),0) as 'count' FROM RESUMES.dbo.ftbl_resume_files WHERE CONTAINS([name],""" + search_name + """)"

Open in new window

HTH,
Dan
0
 
wchestnutAuthor Commented:
No, that didn't work... SQL didn't like the double-quotes.
New-SQL-String-Value.jpg
0
 
Dan CraciunIT ConsultantCommented:
Yeah, it's confusing. Try this:

sql = "SELECT IsNull(COUNT(1),0) as 'count' FROM RESUMES.dbo.ftbl_resume_files WHERE CONTAINS([name],""" + search_name + """ + ")"

Open in new window

0
 
wchestnutAuthor Commented:
Thanks, Carl!  That worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.