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

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( “‘”, “”” ) + “‘” );