CODEWITHSUNDEEP
c#databaseinsertspecial-characters
lunchbox
lunchbox

Reputation: 435

single quotes escape during string insertion into a database

Insertion fails when "'" is used. example string is: He's is a boy. I've attempted to skip the "'" using an escape symbol , but I believe this is not the right way.

textBox3.Text.Replace("'", " \'");
string sql= "insert into gtable (1text,1memo) values ('"+textBox3.Text+"',null)";
        OleDbCommand cmd = new OleDbCommand(sql, con);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

I did have the option of replacing "'" with "`" but this changes the text in the db as well. I wish to retain "'" as the same , and also insert it into the db.

Upvotes: 32

Views: 95118

Answers (5)

Aki
Aki

Reputation: 179

The best way is:

string Name = Server.HtmlEncode(txtName.Text);

Upvotes: -5

codingbiz
codingbiz

Reputation: 26386

Try this

    string sql= "insert into gtable (1text,1memo) values (@col1,NULL)";
    OleDbCommand cmd = new OleDbCommand(sql, con);
    cmd.Parameters.AddWithValue("@col1",textBox3.Text);
    con.Open();

Upvotes: 58

Joshua Shearer
Joshua Shearer

Reputation: 1120

On the MSDN article for String.Replace it says:

Returns a new string in which all occurrences of a specified Unicode character or String in the current string are replaced with another specified Unicode character or String.

On the very first line you are not assigning the value of textBox3.Text to the result of that method call, meaning that absolutely nothing happens.

Furthermore, to escape a quote in SQL Server, you simply use two single-quotes (Note: NOT the same thing as a double-quote).

This should give you the expected outcome:

textBox3.Text = textBox3.Text.Replace("'", "''");

Additionally, you may wish to look into String.Format for your string concatenation needs.

String escapedInput = textBox3.Text.Replace("'", "''");
String sql = String.Format("insert into gtable (1text,1memo) values ('{0}',null)", escapedInput);

Upvotes: 2

Nikhil Agrawal
Nikhil Agrawal

Reputation: 48568

To insert single quotes in database replace ' with ''. In database only single quote will go.

Use this

string sql= "insert into gtable (1text,1memo) values ('" 
            + textBox3.Text.Replace("'", "''") + "', null)";

Rest code is same.

Upvotes: 9

juergen d
juergen d

Reputation: 204766

try

string sql= "insert into gtable (1text, 1memo) " + 
            "values ('" + textBox3.Text.Replace("'", "''") + "', null)";

Upvotes: 46

Related Questions