?
Solved

Unable to escape the apostrophe in command string

Posted on 2011-05-04
11
Medium Priority
?
359 Views
Last Modified: 2012-06-27
Hello.

I have a simplet command string in which the query is pulling data from a database that I cannot update.  

I get the following error message:

Incorrect syntax near 'S'. Unclosed quotation mark after the character string ''.  

The query is:

 cmd.CommandText = "select distinct" + "'" + "foam" + "''" + "from tbl_colorFoam WHERE foam <> '' order by foam asc";      

Open in new window



What can I do to avoid this error?  This is controlled by a drop-down that is populated with items that have apostrphes.

I also tried using regex,

DropDownList selectProgram = (DropDownList)FindControl("drpquickProgram");
        Regex.Replace(selectProgram.SelectedValue, " ' ", "''"); 

Open in new window


but nothing seems to work.

 
0
Comment
Question by:ayoZen
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 11

Accepted Solution

by:
brutaldev earned 2000 total points
ID: 35693939
You could just use a standard string replace to double up the apostrophes:
 
selectProgram.SelectedValue.ToString().Replace("'", "''");

Open in new window

or just strip them out:
 
selectProgram.SelectedValue.ToString().Replace("'", string.Empty);

Open in new window

0
 
LVL 5

Expert Comment

by:morgulo
ID: 35694186
I think, You can simply use:
cmd.CommandText = "select distinct foam from tbl_colorFoam WHERE foam is not null and foam <> '' order by foam asc";

If you need use "foam" from the select statement in another query, you should pass it as parameter (cmd.Paramaters).
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35694691
100% percent, if you can change that CommandText then you can replace the dynamic SQL with parameters instead and then you won't care about apostrophes. I read the statement though that this cannot be updated but now that I read it again you may be speaking of the database structure.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:edemcs
ID: 35695406
Use the @ symbol to not have to use escape characters:


cmd.CommandText = @"select distinct" + "'" + "foam" + "''" + "from tbl_colorFoam WHERE foam <> '' order by foam asc";      
0
 

Author Comment

by:ayoZen
ID: 35699458
foam is the column in the database that is throwing the error.   the output of [foam] sometimes has apostrophes, while some records do not.  It's the records that have apostrophes that throw the error.

later on in my code, I created a class that returns foam as one of the values:

private void fillshwAllByDateQry(string stgrdcaption, string sttableType, DateTime firstDate, DateTime lastDate, string foamTitle)
    { 
     lblShwAll.Text = "";
     try
     {               
         string cm = ConfigurationManager.AppSettings["SiteDB"].ToString();
        
         using (SqlConnection conn = new SqlConnection(cm))
         {
             SqlCommand mycomm = new SqlCommand();
             mycomm.Connection = conn;
             mycomm.CommandType = CommandType.StoredProcedure;
             mycomm.CommandText = "stp_DelinquentByDate";
             mycomm.Parameters.AddWithValue("@startDate", firstDate);
             mycomm.Parameters.AddWithValue("@endDate",lastDate);
             mycomm.Parameters.AddWithValue("@foam",foamTitle);

             conn.Open();

Open in new window




0
 

Author Comment

by:ayoZen
ID: 35699750
Sorry, after looking at my code, I wrote out the query string, and this is the correct string:

string rapidCuePrgquery = "select * from vw_shwAllRapidCue WHERE Production LIKE '%" + strYourProgram + "%'";

Open in new window


So, if I am selecting a show, I get the error message  and the actual query,
select * from vw_shwAllRapidCue WHERE Production LIKE '%RUN'S HOUSE %' 

Open in new window

 
that show has an apostrophe.
0
 

Author Comment

by:ayoZen
ID: 35699779
Thank you all so much.  After reading your suggestions, I was able to get my small problem fixed:

Thank you so much!!!!


string noApostrophes = strYourProgram.Replace("'", "''");

string rapidCuePrgquery = "select * from vw_shwAllRapidCue WHERE Production LIKE '%" + noApostrophes + "%'";

Open in new window

0
 
LVL 8

Expert Comment

by:edemcs
ID: 35700052
If you are closing this question, you should be doing so by appointing the correct answer with points to the expert you used.
0
 

Author Closing Comment

by:ayoZen
ID: 35701031
I was able to pinpoint and resolve my error due to the solutions offered by the experts.  I was forced to really examine the problem.  

Thank you.
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35701067
Hi ayoZen, please assign a grading and points to the expert(s) that assisted you in solving your problem instead of just closing the question.

More info on closing questions in the correct way can be found here: http://www.experts-exchange.com/help.jsp#hs=29&hi=407

More info on grading answers can be found here: http://www.experts-exchange.com/help.jsp#hs=29&hi=403
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 35701070
Thank you :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

862 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