CODEWITHSUNDEEP
c#sqlsql-servercsv
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

How to avoid the single quote within a column value

I have the following code which iterates through a CSV file and writes to the SQL table:

foreach (string line in lines.Skip(1))
{
     var sqlfmt = "INSERT INTO [" + tab + "] values ({0})";
     var insert = string.Format(sqlfmt, line.Replace("\"", "'"));

     MessageBox.Show(insert + "");

     SqlCommand ty = new SqlCommand(insert, myConnection);
     ty.ExecuteNonQuery();
}

The issue I have now is if one of the column has ' in the text my application crashes.

What would be the best way to avoid the issue?

Upvotes: 0

Views: 108

Answers (1)

Ruslan
Ruslan

Reputation: 2799

Just change

var insert = string.Format(sqlfmt, line.Replace("\"", "'"));

To:

var insert = string.Format(sqlfmt, line.Replace("'", "''").Replace("\"", "'"));

The reason for this is that in T-SQL (SQL Server's version of SQL), single-quotes inside a string are escaped by another single quote. For example, if you wanted to properly quote the string Bob's quotes', the properly-escaped SQL string would be 'Bob''s quotes'''.

Upvotes: 1

Related Questions