Updating spreadseet cells that are blank

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.

Quote:

---

...

//Create the connection to Excel
System.Data.OleDb.OleDbConnection ExcelConnection = new System.Data.OleDb.OleDbConnection(ConnectionString );
ExcelConnection.Open();

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

...

---

I have yet to use ADO.NET to access an excel file but I did a little reading to your problem. You are on the right track with ADO assuming a NULL value. Most everything I've read talks about reading the excel file, not updating it, but this is worth a shot. Check here: Tips for reading Excel spreadsheets using ADO.NET » Lab49 Blog and try to use the IMEX=1 setting in your connection string. It would look something like this

Extended Properties=""Excel8.0;HDR=NO;IMEX=1""

Google it and check out more responses on how this has helped other people.

I tried your suggestion and now I get a new message, "Operation Must Use an Updateable Query".

I should have included the connection string in the original post. this is what I started with:

Code:

---

string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + openFileDialog1.FileName + "; Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

---

I added the Imex option, and got the updatable query message so I added tried adding the Readonly option also. This also cause the "Operation Must Use an Updateable Query" error. This is what the connection string looks like now.

Code:

---

[string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + openFileDialog1.FileName + "; Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;ReadOnly=0\"";

---

Any other suggestions?