none
C# OleDbDataReader query RRS feed

  • Question

  • Hi everyone.

    This is like my second day working with C# and I'm trying desperately to decipher the previous programmer's code.
    He seems to be using the OleDbDataReader object in order to read data from an excel file. If column A has values 123, 456 and 789, it reads it fine. If column A has values 123A, B456 and 7C89, then it reads it fine as well. However, if column A contains a combination of these types of values, for example 123, B456 and 789, then the number values are overlooked and the destination table thus stores only B456. I've noticed that if I append the number 123 with a character, then it is interpreted (example 1y23t). I've tried reading up on the OleDbDataReader object but I cannot fathom why this would happen? I would greatly welcome any assistance. Please see below for an extract of the code:

      void uploadProjectedExpiriesData4()
            {
                String ExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Custom Apps\\Load Flat Files\\Data\\Stock Expiry\\2009 06 Expiring Stock - June 09 January 10.xls\";Extended Properties=\"Excel 8.0;HDR=YES;\"";
                OleDbConnection conn = new OleDbConnection(ExcelConn);
                OleDbCommand ocmd;
                OleDbDataReader odr;
                String xsql = "select * from [Projected Expiries$]";

                CacheConnection cc = new CacheConnection("Server = 10.192.100.212; Port = 1972; Namespace = SMSIALCH; Password = SYS; User ID = _SYSTEM;");
                CacheCommand ccom;
                String csql;

                ocmd = new OleDbCommand(xsql, conn);
                conn.Open();
                odr = ocmd.ExecuteReader();

                cc.Open();
                int count = 1;
                string year, month;
                year = "2009";
                month = "10";
                while (odr.Read())
                {
                    if (chk(odr["Material"]).Length > 0)
                    {
                        csql = "insert into dbo.ProjectedExpiries (Year, Month, Material, Description, SLoc, ExpiryDate, Batch, Qty, BUn, UnitPrice,    
    TotalCurrentValue, AvgMonthlyConsumption, DaysToExpiry) " +
                        "VALUES('"+year+"', '"+month+"', '" + chk(odr["Material"]) + "', '" + chk(odr["Material description"]) + "', '" + chk(odr["SLoc"]) + "', '" + chk(odr["Expiry Date"]) + "', '" + chk2(odr["Batch2"]) + "', " + chk2(odr["Qty"]) + ", '" + chk(odr["BUn"]) + "', " + chk2(odr["Unit Price"]) + ", " + chk2(odr["Total Current Value"]) + ", " + chk2(odr["Avg Monthly Consumption"]) + ", " + chk2(odr["Days to Expiry"]) + ")";
                        Debug.WriteLine(count + " | " + csql);
                        count++;

                        ccom = new CacheCommand(csql, cc);
                        ccom.ExecuteScalar();
                    }
                }
            }

    Thanks,
    Ashley.
    Wednesday, August 5, 2009 1:27 PM

Answers

  • I think the problem you are encountering has to do with how the OLEDB provider determines the data type of the column for Excel. What happens is that it makes a guess after scanning the data present in several of the rows (I believe the default is eight rows).

    Try adding the IMEX argument to your connection string to see if that resolves the issue:

    String ExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Custom Apps\\Load Flat Files\\Data\\Stock Expiry\\2009 06 Expiring Stock - June 09 January 10.xls\";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Wednesday, August 12, 2009 1:37 AM
    Wednesday, August 5, 2009 2:02 PM
  • One addition to Paul's post. Setting IMAX=1 in connection string will force provider to treat all the values as strings. In this case you will receive all the values for all the column as strings and if you need values in specific data type, you will need to convert them explicitly in your code.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Wednesday, August 12, 2009 1:37 AM
    Friday, August 7, 2009 10:02 AM
    Moderator

All replies

  • I think the problem you are encountering has to do with how the OLEDB provider determines the data type of the column for Excel. What happens is that it makes a guess after scanning the data present in several of the rows (I believe the default is eight rows).

    Try adding the IMEX argument to your connection string to see if that resolves the issue:

    String ExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Custom Apps\\Load Flat Files\\Data\\Stock Expiry\\2009 06 Expiring Stock - June 09 January 10.xls\";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Yichun_Feng Wednesday, August 12, 2009 1:37 AM
    Wednesday, August 5, 2009 2:02 PM
  • One addition to Paul's post. Setting IMAX=1 in connection string will force provider to treat all the values as strings. In this case you will receive all the values for all the column as strings and if you need values in specific data type, you will need to convert them explicitly in your code.
    Val Mazur (MVP) http://www.xporttools.net
    • Marked as answer by Yichun_Feng Wednesday, August 12, 2009 1:37 AM
    Friday, August 7, 2009 10:02 AM
    Moderator