问题描述:

I'm currently trying to write into an excel file. I use this piece of code:

 public void UpdateExcelFile(string dir, string sheet, string sql)

{

System.Data.OleDb.OleDbConnection MyConnection;

System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();

MyConnection = new System.Data.OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0;MAXSCANROWS=0\";", dir));

MyConnection.Open();

myCommand.Connection = MyConnection;

myCommand.CommandText = sql;

myCommand.ExecuteNonQuery();

MyConnection.Close();

if (myCommand != null)

{

myCommand.Dispose();

myCommand = null;

}

if (MyConnection != null)

MyConnection.Dispose();

}

I'm getting an "data type mismatch in criteria expression" exception. My statement looks as follows:

string cmdString = String.Format("Insert into [{0}] (Customer, Location, CalendarWeek, from, till, [agreed at], [agreed with], [{1}]) values('{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')"

, sheetName, getPackaging.Text, getCustomer.Text, getLocation.Text, getCalWeek, getFirstDayOfCalWeek,

getLastDayOfCalWeek, getDateOfAgreement.Value.ToString("dd/MM/yyyy"), getNameForPrice.Text, getPrice.Value.ToString());

So everything is converted to string. In my Excel file I have every single column converted to "standard". Why does this happen? I even have the MaxScanRows property set to 0.

EDIT:

Here is the cmdString

Insert into [Sheet1$] (Customer, Location, CalendarWeek, from, till, [agreed at], [agreed with], [10x500]) values('A','City','1','02.01.2016','08.01.2016','05.03.2016','Test','3')

相关阅读:
Top