CODEWITHSUNDEEP
c#mysql
BioGeek
BioGeek

Reputation: 22837

Inserting into a MySQL database table, using C#, a string that can contain double quotes

I want to insert into a MySQL database table, using C#, a string that can contain double quotes. Here is what I've tried so far (first escaping the double quotes and then enclosing the string between double quotes):

string sqlStr = "INSERT INTO table(ID, Comment) VALUES ";
foreach (KeyValuePair<string, string> kvp in myDict)
{
    string id = kvp.Key;
    string comment = String.Format("\"{0}\"", kvp.Value.Replace(@"""", @"\"""));
    sqlStr +=  String.Format("({0}, {1}), ";
}
// strip last comma
sqlStr = sqlStr.Substring(0, sqlStr.Length - 2);
conn.DoSql(sqlStr);

But that still gives the error that I have an incorrect SQL syntax. What is the correct way of doing this?

Upvotes: 0

Views: 364

Answers (2)

Tony Vitabile
Tony Vitabile

Reputation: 8594

SQL uses single quotes to enclose strings. That means that double quotes in your strings don't need to be escaped, but single quotes do. Assuming you don't have any single quotes or apostrophes in your strings, your code should read:

string sqlStr = "INSERT INTO table(ID, Comment) VALUES ";
foreach (KeyValuePair<string, string> kvp in myDict)
{
    string id = kvp.Key;
    string comment = String.Format("'{0}'", kvp.Value);
    sqlStr +=  String.Format("({0}, {1}), ";
}
// strip last comma
sqlStr = sqlStr.Substring(0, sqlStr.Length - 2);
conn.DoSql(sqlStr);

Upvotes: 1

Zohaib Aslam
Zohaib Aslam

Reputation: 595

try to print sql statement before executing it and check it's contents

// firt do this
Console.WriteLine(sqlStr);

// and then this
conn.DoSql(sqlStr);

post contents of sqlStr if you can :)

Upvotes: 0

Related Questions