Problems with saving data with single quote ' in the query

I have an asp.net project in which I am getting an error while saving data.I have a text box for school name, most school names get saved but if I have a school name like St.Xavier's, it creates a problem during the execution of the insert query.This happens due to the presence of a single quote in the text.As a result the eventual query that I get is:

Insert Into tblSchool(SchoolName, CreatedBy, CreatedOn) 
values('St.Xavier's',1,'2014-6-13     13:14:16')

How can I save data with single quotes in the text? I am using Microsoft SQL Server 2005.

Jon Skeet
people
quotationmark

You haven't shown your code, but I strongly suspect that you've got code such as:

// Bad code!
String sql = "Insert Into tblSchool(SchoolName, CreatedBy, CreatedOn) "
           + "values('" + name + "'," + creator + ",'" + date +"')";

Don't do that. It's hard to read, error-prone in terms of conversions, and vulnerable to SQL Injection attacks.

Instead, you should use parameterized SQL. Something like:

String sql = "Insert Into tblSchool(SchoolName, CreatedBy, CreatedOn) "
           + "values(@Name, @CreatedBy, @CreatedOn)";
using (var command = new SqlCommand(sql, connection))
{
    command.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;
    command.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = creator;
    command.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value = date;

    int results = command.ExecuteNonQuery();
    ...
}

See the SqlCommand.Parameters documentation for more details.

people

See more on this question at Stackoverflow