0

I have a excel file without headers and I'm inserting there some text to specified cells using excel interop. Everything is fine as long as the string does not contain a dot symbol. Text is added to the cell but the application stops working and does not add any more texts. Below is my code.

string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=No;IMEX=0'", filenamepath);

using (OleDbConnection cn = new OleDbConnection(connectionString)) {
   cn.Open();
     OleDbCommand cmd = new OleDbCommand("INSERT INTO[sheet1$B3:B3] VALUES ('" + "Some string with dot." + "')", cn);
     cmd.ExecuteNonQuery();
   cn.Close();
}
derive
  • 1
  • https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection – mjwills Dec 02 '20 at 23:38
  • `but the application stops working` Can you be more explicit? – mjwills Dec 02 '20 at 23:38
  • It sounds like you might be getting an exception. Please add a try/catch block to your code, and please update your post with the the error text if this is the case. – paulsm4 Dec 02 '20 at 23:40
  • I don't get any exception. For example i want to insert texts: first to B3, second to C8, third to E4. When first text contains a dot symbol the async method stops working and the other two texts are not added. I wrote wrongly that application stop working. – derive Dec 02 '20 at 23:54
  • Please share a [mcve]. – mjwills Dec 02 '20 at 23:58

1 Answers1

0

Try prepending the "string with dot" with a single quote '. This tells Excel to interpret the input as text, which is obviously required in your case. Also, note that a "comma" might give you the same problem, depending on the culture.

E.g.:

"INSERT INTO[sheet1$B3:B3] VALUES ('''" + "Some string with dot." + "')"
//                                  ^^

You can try it out in Excel. Without the ' prefix, Excel will format any number-like text as a number.

l33t
  • 18,692
  • 16
  • 103
  • 180
  • Please encourage the use of parameters rather than encouraging SQL Injection. – mjwills Dec 03 '20 at 00:57
  • You just did :) My intension was mostly to get a quick response whether this solves the issue or not. If it turns out to be correct, we can rewrite it using a parameterized query. Do you have some boilerplate code ready? – l33t Dec 03 '20 at 08:55