OleDbDataAdapter.Update returns rows, but no rows added to Excel spreadsheet

Question

I can add rows to my Excel spreadsheet one row at a time, but it is incredibly slow (1 minute for 400 records, even using Prepare). So, I know the Sql is valid and the DataTable is good.

The code that works:

    public void InsertFromDataTable(string strSql, DataTable dtTable, string strTableName)
    {
        if (m_oleDbHandler == null)
        {
            m_oleDbHandler = new OleDbHandler(m_strConnection);
        }

        //Do one row at a time since the DataAdapter did not work
        foreach (DataRow drRow in dtTable.Rows)
        {
            OleDbParmCollection cololedbParameters = new OleDbParmCollection();
            foreach (DataColumn dcColumn in dtTable.Columns)
            {
                OleDbParameter odpParameter = new OleDbParameter("@" + dcColumn.ColumnName, drRow[dcColumn.ColumnName]);
                odpParameter.ParameterName = "@" + dcColumn.ColumnName;
                odpParameter.DbType = OleDbHandler.GetDbType(dcColumn.GetType());
                odpParameter.Size = dcColumn.MaxLength;
                odpParameter.SourceColumn = dcColumn.ColumnName;

                cololedbParameters.Add(odpParameter);
            }

            m_oleDbHandler.ExecuteCommand(strSql, cololedbParameters, true);
        }
    }
}

When I try to do the same thing using a DataAdapter, it says it returns 458 rows, but there are no new rows in the spreadsheet. The code that fails:

        //DataAdapter version
        OleDbParmCollection cololedbParameters = new OleDbParmCollection();
        foreach (DataColumn dcColumn in dtTable.Columns)
        {
            OleDbParameter odpParameter = new OleDbParameter();
            odpParameter.ParameterName = "@" + dcColumn.ColumnName;
            odpParameter.OleDbType = OleDbHandler.GetOleDbType(dcColumn.GetType());
            odpParameter.DbType = OleDbHandler.GetDbType(dcColumn.GetType());
            odpParameter.Size = dcColumn.MaxLength;
            odpParameter.SourceColumn = dcColumn.ColumnName;

            cololedbParameters.Add(odpParameter);
        }

        m_oleDbHandler.InsertFromDataTable(strSql, dtTable, cololedbParameters, strTableName);

and then:

    public int InsertFromDataTable(string strSql, DataTable dtTable, OleDbParmCollection cololeDbParameters, string strTableName)
    {
        //Set every row as added so that they will be inserted
        foreach (DataRow drRow in dtTable.Rows)
        {
            drRow.SetAdded();
        }

        //Update the output table
        int intRows = -1;
        try
        {
            OleDbCommand oleDbCommand = new OleDbCommand(strSql, OpenConnection());

            foreach (OleDbParameter oleDbParameter in cololeDbParameters)
            {
                if (oleDbParameter.Value == null)
                {
                    oleDbCommand.Parameters.Add(oleDbParameter.ParameterName, OleDbType.VarChar).Value = DBNull.Value;
                }
                else if (string.IsNullOrEmpty(oleDbParameter.Value.ToString()))
                {
                    oleDbCommand.Parameters.Add(oleDbParameter.ParameterName, OleDbType.VarChar).Value = DBNull.Value;
                }
                else
                {
                    oleDbCommand.Parameters.Add(oleDbParameter);
                }
            }

            OleDbDataAdapter odaAdapter = new OleDbDataAdapter(new OleDbCommand("SELECT * FROM " + strTableName, OpenConnection()));
            odaAdapter.InsertCommand = oleDbCommand;
            odaAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
            odaAdapter.MissingSchemaAction = MissingSchemaAction.Error;
            odaAdapter.TableMappings.Add(strTableName, dtTable.TableName);

            foreach (DataColumn dcColumn in dtTable.Columns)
            {
                odaAdapter.TableMappings[0].ColumnMappings.Add(dcColumn.ColumnName, dcColumn.ColumnName);
            }

            intRows = odaAdapter.Update(dtTable);
        }
        catch (OleDbException ex)
        {
            LogStackTrace();
            LogToDb.LogException(ex, LogToDb.c_strAppError);
            LogToDb.LogMessage("OleDb error", "OleDbHandler.InsertFromDataTable error", strSql, LogToDb.c_intErrorLevelOleDb);
            CancelTransactionAndClose();
            throw;
        }
        finally
        {
            CloseConnection();
        }

        return (intRows);
    }

Why would I get intRows = 458, but there are no new rows in the Excel file?

EDIT: I just did a test to see what happens if I export to a Microsoft Access .mdb (instead of Excel), and the results tell me something. I get 458 blank rows. so, I suspect I am getting 458 blank rows in Excel. So, now the question is why the rows are all blank.

MicrosoftAccessPros.com MicrosoftAccessPros.com · Accepted Answer · 2012-10-29T20:36:10

Got it -- the error was in the section below. This works well for an ExecuteNonQuery, but lousy when there is no data.

        foreach (OleDbParameter oleDbParameter in cololeDbParameters)
        {
            if (oleDbParameter.Value == null)
            {
                oleDbCommand.Parameters.Add(oleDbParameter.ParameterName, OleDbType.VarChar).Value = DBNull.Value;
            }
            else if (string.IsNullOrEmpty(oleDbParameter.Value.ToString()))
            {
                oleDbCommand.Parameters.Add(oleDbParameter.ParameterName, OleDbType.VarChar).Value = DBNull.Value;
            }
            else
            {
                oleDbCommand.Parameters.Add(oleDbParameter);
            }
        }

The corrected code, which works well with both Access and Excel is:

    foreach (OleDbParameter oleDbParameter in cololeDbParameters)
    {
            oleDbCommand.Parameters.Add(oleDbParameter);
    }

There was a second, less serious error. I used

OleDbHandler.GetOleDbType(dcColumn.GetType());

which should have been

OleDbHandler.GetOleDbType(dcColumn.DataType);

OleDbDataAdapter.Update returns rows, but no rows added to Excel spreadsheet

1 Answers