Thread: Updating spreadseet cells that are blank

  1. #1
    Registered User
    Join Date
    Feb 2010
    Posts
    2

    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.

    ...

    //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

    ...

  2. #2
    eh ya hoser, got a beer? stumon's Avatar
    Join Date
    Feb 2003
    Posts
    323
    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.
    The keyboard is the standard device used to cause computer errors!

  3. #3
    Registered User
    Join Date
    Feb 2010
    Posts
    2

    new problem

    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?

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Problem opening blank files with ifstream.
    By Sclorch in forum C++ Programming
    Replies: 4
    Last Post: 02-07-2009, 11:43 AM
  2. Problem with deleting completely blank lines
    By dnguyen1022 in forum C Programming
    Replies: 3
    Last Post: 12-07-2008, 11:51 AM
  3. Discarding blank lines?
    By Blurr in forum C++ Programming
    Replies: 3
    Last Post: 12-02-2007, 12:30 PM
  4. excel spreadsheets / blank cells
    By xddxogm3 in forum Tech Board
    Replies: 3
    Last Post: 06-14-2005, 12:10 PM
  5. Check for a blank cd-r
    By waldis in forum C++ Programming
    Replies: 3
    Last Post: 02-23-2003, 06:16 PM