Updating Excel Cell with Non-Numeric Data in C#

Question

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!

Answer 1

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#

Answer 2

It sounds like you are trying to use SQL to INSERT a text value into a column which the DBMS (the Access Database Engine) sees as DOUBLE FLOAT and hence getting a type mismatch error. You may be able to change registry values to convince the engine to consider the column to be text, see:

External Data - Mixed Data Types