Search code examples
c#sqloledboledbconnection

Datatype mismatch in query expression with OleDbCommand

char zoneCH = '-'; 
string insertString = "UPDATE streets set TmpColumn=City+'" + zoneCH + "'+State+'" +zoneCH +"'+Zip WHERE 1"; 
OleDbCommand com1 = new OleDbCommand(insertString, mdbConnection);
com1.ExecuteNonQuery();

This will work if Zip is a string, but if Zip is a Number I get a data type mismatch in query expression. How can I make it work with a Number?

Solution

  • Probably because it's trying to Add instead of concatenate. Different DB's handle this differently. Some use the left side to determine what it should do.

    You should explicitly cast the Zip to a string and then you'll be fine

    However since this is MS Access the best bet is to use the & to concatenate 

    string insertString = "UPDATE streets set TmpColumn=City & '" + zoneCH + "'& State &'" +zoneCH +"'& Zip WHERE 1"