CODEWITHSUNDEEP
c#stringexcelinsertoledb
MHeads
MHeads

Reputation: 387

C# OLEDB protect apostrophie in query

I try to find a way to protect apostrophie in my query string. I have a value in string format that contain apostrophie and it throw me an error when I tried to insert

ex : 

Insert into ["excelApp.worksheetsList.ElementAt(0).Name "$"] " + ([col1], [col2]) 
values values" + " ('" + val1 + "', '" + val2 + "');")

This is an exemple. here val1 contains "hereIsMy'value".

Thanks for helping me

Upvotes: 2

Views: 538

Answers (2)

Steve
Steve

Reputation: 216293

You should use parametrized queries and you don't have to worry about single quotes in query

using(OleDbConnection cn = new OleDbConnection(GetConnectionString()))
{
    cn.Open();
    string cmdText = "Insert into [" + excelApp.worksheetsList.ElementAt(0).Name + "$] " +   
                     "([col1], [col2]) values (?, ?)";
    OleDbCommand cmd = new OleDbCommand(cmdText, cn)
    cmd.Parameters.AddWithValue("@p1", val1);
    cmd.Parameters.AddWithValue("@p2", val2);
    cmd.ExecuteNonQuery();
}

In this example your command text consist of a single string with placeholders for the parameters value. Then a command object is declared to have that string and two parameters are added at its collection of parameters. They are created according to the variable type passed as value. So, if val1 and val2 are strings and a single quote (apostrophe) is present it is automatically formatted for the insert/delete/update or select operation requested

Upvotes: 3

Paul Sasik
Paul Sasik

Reputation: 81479

Use parameterized commands and you won't have to worry about such things. You won't have to worry about apostrophes and a bunch of other problems.

Upvotes: 1

Related Questions