You need hard work to get Lucky!

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

Archive for June, 2010

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

Posted in Asp.net | Tagged: , , , | 2 Comments »