How to Insert the proper DataType into Excel 97-2003 format

Question

I'm unable to find the correct DataType for inserting into EXCEL 97-2003 format. I'm able to insert but when veiwing the Excel 97-2003 document every column is coming up with Number saved as text. Even when inserting Double from C# Excel still recognizing it as numbers saved as text and asks the viewer to convert to number. Any help would be greatly appreciated.

foreach (DataRow drow in DataTable DT) 
{ 
 cmd.CommandText = "INSERT INTO [LHOME$]([Date], [Owner], [MAKE], [BUY],  
[OVERAGE], [SUM]) " + "VALUES(" + "'" + drr.ItemArray[0].ToString() + "'" + ","            + "'"     + drr.ItemArray[1].ToString() + "'" + "," + "'" + drr.ItemArray    [2].ToString() +

Is it possible just to disable the Background error checking in Excel 2007?

Answer 1

Try specifying the data types via command parameters:

command = new OleDbCommand(
    "INSERT INTO [LHOME$]([Date], [Owner], [MAKE], [BUY], [OVERAGE], [SUM]) " +
    "VALUES (?, ?, ?, ?, ?, ?)", connection);

command.Parameters.Add(
    "Owner", OleDbType.Varchar, drr.ItemArray[0], "Owner");
...
command.Parameters.Add(
    "Sum", OleDbType.Double, drr.ItemArray[5], "Sum");

adapter.InsertCommand = command;

For a list of available Data Types see this: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx