OleDbDataReader - No value given for one or more required parameters

Question

I'm trying to add data from an Excel spreadsheet to a table in SQL Server using BulkInsert. The online help has been pretty good, but I'm getting an error I don't understand.

Here is the code I'm using to get and save the target Excel file (using a FileUpload control):

protected void btnGetFile_click(object sender, EventArgs e)
    {
        if (this.FileUpload1.HasFile)
        {
            this.FileUpload1.SaveAs(Server.MapPath("~/temp/") + this.FileUpload1.FileName);     
            ImportFromExcel(Server.MapPath("~/temp/") + this.FileUpload1.FileName);
        }
    }

And this is the code I'm using to read the file and perform a bulk insert:

    public void ImportFromExcel(string excelFilePath)
    {
        //  Get connection string to database from web.config
        string strConnDB = ConfigurationManager.ConnectionStrings["strConnectToDB"].ConnectionString;
        //  Connection string for selected Excel file
        string strConnExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

        string strExcelBatchQuery = "select LabBatch from [LabBatch$]";

        OleDbConnection connExcel = new OleDbConnection(strConnExcel);
        connExcel.Open();

        OleDbCommand cmdExcel = new OleDbCommand(strExcelBatchQuery, connExcel);

        OleDbDataReader rdrExcel = cmdExcel.ExecuteReader();

        SqlBulkCopy bulkCopy = new SqlBulkCopy(strConnDB);
        bulkCopy.DestinationTableName = "_trasher_excel_batch";

        while (rdrExcel.Read())
        {
            bulkCopy.WriteToServer(rdrExcel);
        }
        connExcel.Close();
    }

But when I try this code I get the error, "System.Data.OleDb.OleDbException: No value given for one or more required parameters." on this line:

OleDbDataReader rdrExcel = cmdExcel.ExecuteReader();

I've looked all over, but this seems to be the correct syntax. Even the MSDN entry says this is correct.

The table exists in my database, and I've double-checked the table, worksheet, and field names. There are no nulls in the spreadsheet (a couple of replies to similar posts on Stackoverflow said that might be the problem).

Any help? Thanks.

Answer 1

For anyone who comes after:

The SQL syntax I posted seems to work with SELECT *, but not with SELECT [some fields]. I have no idea why, but if you're having the same problem, you can import all the fields in your Excel file and then write your code to use only the data you actually need.

I'm sure there's a better answer, this workaround got my boss off my back.

Answer 2

You are specifying this "[LabBatch$]" parameter in your SQL, but your are not supplying a value for it.

EDIT

private static void ShowExcelWorksheetNames(OleDbConnection m_connexcel, string Filepath)
{
    try {
        DataTable ExcelSheets = m_connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {
            null,
            null,
            null,
            "TABLE"
        });
        foreach (DataRow row in ExcelSheets.Rows) {
            MessageBox.Show(row.Item("TABLE_NAME"));
        }
    } catch (Exception ex) {
        throw new Exception(ex.Message);
    }
}

Answer 3

In your connection string you are setting the "first row is column name" (HDR) to "NO" so the select can only work with "*".

Try:

string strConnExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";