Search code examples
c#sqlexceloledb

Updating Excel Cell with Non-Numeric Data in C#

I have a query that is 

ExcelQuery = "Update [Sheet1$] "
               +"set CITIZEN_ID = #" + value 
               + " where CITIZEN_ID = " + value;

As you can see, I'm essentially just prepending a "#" to the CITIZEN_ID field. value is a int/numeric value. So if I had "256" in the CITIZEN_ID column it would be converted to "#256"

When I execute this I get an OleDbException Syntax error in date in query expression so I surrounded part of the query in single quotes like this, 

ExcelQuery = "Update [Sheet1$] "
               +"set CITIZEN_ID = '#" + value + "' "
               +"where CITIZEN_ID = " + value;

With that I get yet another OleDbException this time with, Data type mismatch in criteria expression.

I'm guessing for some reason the CITIZEN_ID fields don't want to take anything besides a plain number. Is there any way I can remedy this to get that pound symbol in?

Thanks!

Solution

  • Can't you just change the number format so it shows a '#' before each number in the CITIZEN_ID field.

    This doesn't solve your stated problem .. but it avoids it :-)

    Update: This StackOverflow Question ( excel-cell-formatting) talks about cell formatting using C#