Getting Oledb exception when updating column in excel

Question

I am getting the following error "No value given for one or more required parameters." On the ExceuteNonQuery() line of the below code.

        System.Data.OleDb.OleDbConnection finalConnection;
        System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
        string sql = null;
        finalConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source ='c:\\temp\\test.xlsx'; Extended Properties ='Excel 12.0 Xml;HDR=NO';");
        finalConnection.Open();
        myCommand.Connection = finalConnection;
        foreach (VinObject v in VinList)
            {
            sql = "Update [Sheet1$] set O = ? where S = ?;";
            myCommand.Parameters.Add(new OleDbParameter("@amt", v.CostNewAmt));
            myCommand.Parameters.Add(new OleDbParameter("@vin", v.VIN));
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();
            }
        finalConnection.Close();

I have also tried using a separate command each time, same error.

    foreach (VinObject v in VinList)
            {
            using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source ='c:\\temp\\test.xlsx'; Extended Properties ='Excel 12.0 Xml;HDR=No';"))
                {
                con.Open();
                string query = @"UPDATE [Sheet1$] SET O = ? WHERE S = ?";
                OleDbCommand cmd = new OleDbCommand(query, con);
                cmd.Parameters.AddWithValue("@param1", v.CostNewAmt);
                cmd.Parameters.AddWithValue("@param2", v.VIN);
                cmd.ExecuteNonQuery();
                con.Close();
                }
            }

I am able to modify that into an insert and insert into a new excel spreadsheet, but for the life of me cannot get this update to work. Any idea what I am doing wrong? Thanks for the help.

Wouldn't you need a new command for each individual statement? Or at the least, reset the Parameters collection? — Jim H.
– Jim H., Commented Mar 16, 2012 at 13:56
hint: What is the value of 'sql' inside your foreach? Does this look like a valid query to you? — Val Akkapeddi
– Val Akkapeddi, Commented Mar 16, 2012 at 13:56
@ValAkkapeddi it bombs on the first one, and the query is valid at that point — Isaac Levin
– Isaac Levin, Commented Mar 16, 2012 at 13:59

Joshua Honig · Accepted Answer · 2012-03-16 14:54:36Z

1

You're getting the error because Excel doesn't recognize the column letter aliases "O" and "S". It needs the actual column "name", which is the value of the cell in the first populated row. If there is not a valid value in that cell, or you have specified HDR=NO in your connection string, the columns will be named F1, F2...Fn. If you're not sure what the inferred column names are, examine the names using OleDbConnection.GetSchema(String,String[]) or OleDbDataReader.GetName(Int32).

Since you have specified HDR=NO in your connection string, your correct SQL will likely be

"Update [Sheet1$] set F15 = ? where F19 = ?;"

For future reference, check out:

How to query and display excel data by using ASP.NET, ADO.NET, and Visual C# .NET
How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET. (Still lots of helpful info even if you are using C#)

answered Mar 16, 2012 at 14:54

Joshua Honig

13.3k8 gold badges58 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Isaac Levin Over a year ago

that did not fix my error sadly, I still get the same error when i replace the letters with the "Fn" values. any other advice? thanks

Joshua Honig Over a year ago

@IsaacLevin Did you examine the actual column names using OleDbConnection.GetSchema or OleDbDataReader.GetName? The documentation is linked in my post

arpan shah Over a year ago

What about the date filed. I have date column as [$-1010409] mm/dd/yyyy format and when i tried to put this in where clause like string selectString = "Update [Sheet1$] set SCIN_ATTEND_CODE = 'P' where SCIN_INVOICE_DAYS = 09/09/2014"; its not recognize and didnt update anything. i tried with adding '' around date but it gives datatype mismatch error.. Any idea?

Collectives™ on Stack Overflow

Getting Oledb exception when updating column in excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related