Solved

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

Posted on 2014-03-19
5
794 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 34

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 34

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 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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