Solved

Escaping Single Quote in C# for the string inserted into SQL Server Database

Posted on 2010-09-23
7
1,081 Views
Last Modified: 2012-06-21
I am trying to use the Replace method in the C# code behind to escape single quotes (change it to double quotes). However, when the stored proc SaveMktMessForGivenProductType is run, the data is incorrectly inserted with double quotes rather than a single quote. I replaced the single quote to double quote in order to escape it and avoid erroring in SQL script, not for it to be inserted with the replaced extra quote within the database. Please see the code section and see if you can find a reason why the data is inserted with double quotes. Please help.
dsMktMessage = rdg.GetMktMessForGivenProductType("GetMktMessForGivenProductType", _ProductTypeID, "ProductTypeID", _HandheldIDMktMess, "HandheldID");

                        

                        dsMktMessage.Tables[0].TableName = MKTMESSAGING;



                        if (dsMktMessage.Tables[MKTMESSAGING].Rows.Count > 0)

                        {

                            _MktMessageOrigText = dsMktMessage.Tables[MKTMESSAGING].Rows[0]["MktMessage"].ToString();

                            _KeySellPtOrigText = dsMktMessage.Tables[MKTMESSAGING].Rows[0]["KeySellPt"].ToString();

                        }



                        rdg.SaveMktMessForGivenProductType("SaveMktMessForGivenProductType", _ProductTypeID, _HandheldIDMktMess, _MktMessageText.Replace("'", "''"), _KeySellingPointsText.Replace("'", "''"), _MktMessageOrigText.Replace("'", "''"), _KeySellPtOrigText.Replace("'", "''"));

Open in new window

0
Comment
Question by:skaleem1
7 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33748072
How are you writing to the database, are you parameterised queries? If you are then you don't need to escape the apostrophe, it will be done automatically.
0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 33748137
You should use parameters instead of trying to escape the quotes... rdg.SaveMktMessForGivenProductType() is your method, and in it using a SqlConnection/SqlCommand?  If so, then you could use a method like below and pass in strings with single quotes without needing to escape them.

static void SaveRecordToDbTest(string field1, string field2)

{

	SqlConnection connection = new SqlConnection("Connection String");

	SqlCommand command = connection.CreateCommand();



	command.CommandText = "INSERT INTO TableName (FieldNameOne,FieldNameTwo) VALUES(@FieldOneValue, @FieldTwoValue)";

	command.Parameters.AddWithValue("@FieldOneValue", field1).DbType = DbType.String;

	command.Parameters.AddWithValue("@FieldTwoValue", field2).DbType = DbType.String;

	command.ExecuteNonQuery();



	command.Dispose();

	connection.Close();

	connection.Dispose();

}

Open in new window

0
 
LVL 1

Author Comment

by:skaleem1
ID: 33748144
This is the method I am calling:

public void SaveMktMessForGivenProductTypeAndHandheld(string StoredProcName, string ProductTypeID, string HandheldIDMktMess, string MktMessageText, string KeySellingPointsText, string MktMessageOrigText, string KeySellPtOrigText)
        {
            Database theDB = DatabaseFactory.CreateDatabase("AccessoriesConnectionString");
            theDB.ExecuteNonQuery(StoredProcName, new object[] { ProductTypeID, HandheldIDMktMess, MktMessageText, KeySellingPointsText, MktMessageOrigText, KeySellPtOrigText });
        }
0
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.

 
LVL 1

Author Comment

by:skaleem1
ID: 33748160
Using Enterprise Library 4.1
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33748188
The only things I can think of

... if you mean double-quotes as in the "double quote", then it is contained in the data itself.

... if you mean double-quotes as in TWO x single-quotes, then SaveMktMessForGivenProductType may ALSO contain the replace-protect single-quotes. In which case you are doing it twice.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 33748190
Well from your definition you appear to be using a stored procedure, so escaping the apostrophes shouldn't be necessary.
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 33748246
Thanks carl_tawn. I removed the replace method to escape single quote and the data is inserted as expected.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in page 3 46
Why is some text in blue in Visual Studio? 6 24
Connection String 16 43
Page navigation in windows phone 8.1 application not working. 5 19
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now