You need hard work to get Lucky!

They are ill discoverers that think there is no land, when they can see nothing but sea.

How to handle single quote in SQL Query or DataSet.Select ?

Posted by zeemalik on June 30, 2010

Problem : When we try to select/filter records based on a column value which contains a single quote then the SQL server will give syntax error because there is a single quote in the start and at the end of SQL query normaly.

Solution 1:  Parameterized Query

We can use parameterized query to avoid the single quote issue like this:

string VendorName = “John O’Corner”;

command.CommandText = “SELECT * FROM Products WHERE VendorName= @VendorName”;

command.Parameters.Add(
       new SqlParameter(“@VendorName”, SqlDbType.VarChar, 50)).Value = VendorName;

Solution 2:  Replace Single Quote

Another option is that you can replace single quote with two single quotes. This will give an effect of a string end then string start in SQL Query. This method is especially suitable while we are selecting records in a dataset.

SystemDataSet.ProductRow[] productRows =  dataSet.Products.Select( ” VendorName = ‘” + VendorName.Replace( “‘”, “”” ) + “‘” );

Advertisements

2 Responses to “How to handle single quote in SQL Query or DataSet.Select ?”

  1. Hi to all, how is all, I think every one is getting
    more from this web page, and your views are good designed
    for new viewers.

  2. What i don’t understood is actually how you are no longer really
    much more smartly-appreciated than you might be now.
    You’re so intelligent. You recognize therefore significantly in
    the case of this matter, made me individually consider it from so
    many varied angles. Its like women and men don’t seem to
    be fascinated until it is one thing to do with Woman gaga!

    Your personal stuffs outstanding. At all times care for
    it up!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: