I am putting together a program that allows users to bulk load data from excel spreadsheet (v2007). The user selects the file, hits the process button, but before I risk loading some trash into the DB, I do about 50 checks in a staging table to make sure everything is valid.
If something is not right, I want to use an oleDB Update query to put a message into a "Issues" column on the spreadsheet, that tells them what is wrong with that line of the spread sheet. It all works fine, unless the column contains blank cells. Then I get a "Data type mismatch in criteria expression." I believe the blank cells are interpreted as NULL and so Ole doesn't know what datatype to assign to the column. When I put a chacter or space into the cells for that column it works ok, but i can't count on the users to put something in that column.
Any suggections will be appreciated.
//Create the connection to Excel
System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(ConnectionString );
ExcelCmd.Connection = ExcelConnection;
// First query copies sheet to a SQL staging table
// SQL doeas the numerous checks
// Reader has a record set of the rows that have problems
ExcelCmd.CommandText = "Update [" + txtSheet.Text + "$] set " +
"Issues = '" + Reader["Issues"] + "' " +
"where Projectid = '" + Reader["ProjectID"] + "' and " +
"Taskid = '" + Reader["Taskid"] + "' and " +
"LOBID = '" + Reader["LOBID"] + "' and " +
"Period = '" + Reader["Period"] + "' and " +
"[Value] = " + Reader["Value"];
ExcelCmd.ExecuteNonQuery(); // <--- this raises the data type mismatch