Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-03-19
5
Medium Priority
?
1,087 Views
Last Modified: 2014-03-19
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?
0
Comment
Question by:wchestnut
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39940966
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
 

Author Comment

by:wchestnut
ID: 39940986
No, that didn't work... SQL didn't like the double-quotes.
New-SQL-String-Value.jpg
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39940996
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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 2000 total points
ID: 39941007
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
 

Author Closing Comment

by:wchestnut
ID: 39941012
Thanks, Carl!  That worked perfectly!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question