How to insert a string with ( ' ) in to the sql database?

Question

I have the strings which consists of ( ' ) quote mark like "mother's love" ...

While inserting the data by sql query from c#. It shows error. How can i rectify the problem and insert this kind of data successfully?

string str2 = "Insert into tblDesEmpOthDetails (EmpID, Interviewnotes) values ('" + EmpId + "','" + Interviewnotes + "')";

Interview notes consists the value like "Mother's love" (with single quote). While executing this query it shows error as "Unclosed quotation mark after the character string ')" how can i insert this type of strings?

Answer 1

I'm pretty sure you don't use SQL parameters:

using (SqlCommand myCommand = new SqlCommand(
    "INSERT INTO table (text1, text2) VALUES (@text1, @text2)")) {

    myCommand.Parameters.AddWithValue("@text1", "mother's love");
    myCommand.Parameters.AddWithValue("@text2", "father's love");
    //...

    myConnection.Open();
    myCommand.ExecuteNonQuery();
    //...
}

Answer 2

Use named parameters and the SqlParameter.

From http://www.dotnetperls.com/sqlparameter

class Program
{
    static void Main()
    {
        string dogName = "Fido";  // The name we are trying to match.

        // Use preset string for connection and open it.
        string connectionString = 
            ConsoleApplication1.Properties.Settings.Default.ConnectionString;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Description of SQL command:
            // 1. It selects all cells from rows matching the name.
            // 2. It uses LIKE operator because Name is a Text field.
            // 3. @Name must be added as a new SqlParameter.
            using (SqlCommand command = 
               new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
            {
                // Add new SqlParameter to the command.
                command.Parameters.Add(new SqlParameter("Name", dogName));

                // Read in the SELECT results.
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int weight = reader.GetInt32(0);
                    string name = reader.GetString(1);
                    string breed = reader.GetString(2);
                    Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight, name, breed);
                }
            }
        }
    }
}

Answer 3

Although, you could replace all ' characters in the string with two ' characters (''), it's not a good idea. Because of this issue, and many other reasons (such as avoiding SQL-injection attacks), you definitely should be using named parameters rather than adding the values to your insert statement by concatenating them directly into the string. For instance:

command.CommandText = "Insert into tblDesEmpOthDetails (EmpID, Interviewnotes) values (@EmpId, @Interviewnotes)";
command.Parameters.AddWithValue("EmpId", EmpId);
command.Parameters.AddWithValue("Interviewnotes", Interviewnotes);

Answer 4

Add this line to a string your trying to input

say you string was

string test = "that's not working correctly"
test = replace(Request.QueryString(test), "'", "''")

then test is now

"that''s not working correctly"

which is syntactically correct for SQL

Regards

Answer 5

As a variant on the answers that (very correctly) point you at parameters: if this seems a lot of work, then avoid it with a tool such as dapper:

int empId = 123;
string notes = "abc";
connection.Execute(@"insert into tblDesEmpOthDetails (EmpID, Interviewnotes)
                     values (@empId, @notes)", new {empId, notes});

Dapper will automatically take the empId and notes (from the anonymous object) and add them as named/typed parameters. The similar Query/Query<T> extension-methods also allow for easy and highly-optimised querying directly into an object model.

Answer 6

You need to use double ''

INSERT INTO something (Name) VALUES ('O''something''s')

This will insert O'something's. Another example i read is:

Lets assume we have a string:

SQL = "SELECT * FROM name WHERE LastName='" & LastName & "' "

and if we have lastname something like O'Brian, O'Reily etc. we got string like

SELECT * FROM name WHERE LastName='O'Brien'

the second ' will end the SQL statement. So the simplest solution here is to use double '' then we will have string like this:

SELECT * FROM name WHERE LastName='O''Brien'