Unable to escape the apostrophe in command string

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.

 
ayoZenAsked:
Who is Participating?
 
brutaldevCommented:
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
 
morguloCommented:
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
 
brutaldevCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
edemcsCommented:
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
 
ayoZenAuthor Commented:
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
 
ayoZenAuthor Commented:
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
 
ayoZenAuthor Commented:
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
 
edemcsCommented:
If you are closing this question, you should be doing so by appointing the correct answer with points to the expert you used.
0
 
ayoZenAuthor Commented:
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
 
brutaldevCommented:
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
 
brutaldevCommented:
Thank you :)
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.