No value given for one or more required parameters. UPDATE Excel file

Question

Hi, I am trying to update data in a row/column in a excel file, but i keep getting this error: 

No value given for one or more required parameters.

protected void Button2_Click(object sender, EventArgs e)
        {
            using (OleDbConnection conn = new OleDbConnection())
            {
                conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\AIRSIGNDMC\wwwroot\Contract.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=NO""");
                string sql = string.Format("UPDATE [Sheet1$B5:B5] SET F5='TestValue1'");
                String TestValue1 = TextBox1.Text;

                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {

                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    
                    finally
                    {
                        conn.Close();
                    }
                    conn.Close();
                }
            } 
        }
    }
}

Answer 1

Bump, any body know how to handle this, or do I need to post in a different forum?

Answer 2

I do not know much about update excel files, but dont you forgot to add the where clause?
UPDATE bla set n = 'a' where id = 1

string sql = string.Format("UPDATE [Sheet1$B5:B5] SET F5='{0}' where bla ='{1}'","TestValue1","TestValue2");

---

Web Developer

Answer 3

Thanks Norkk, that throws this error:

No value given for one or more required parameters.

Answer 4

Bump, anybody know how to deal with this?

Answer 5

Hi I3elivE,

1. TestValue1 is neither declared nor initialized. Try this:

String TestValue1 = TextBox1.Text;                
string sql = String.Format("UPDATE [Sheet1$B5:B5] SET F1={0}",TestValue1);

2. Ive got no expierence in Updating ExcelSheets via .NET, but your SQL Syntax seems strange to me.

• "UPDATE [Sheet1$B5:B5] ...", its meaning is UPDATE B5, isnt it?
• "... SET F5='TestValue1'", its meaning is "SET VALUE in the 5th unnamed column" to "TestValue1", isnt it? But B5 means second column, fifth row, doesnt it? So there is now unnamed fifth column, so it should be "... SET F1='TestValue1'"
• at least: datatype in B5 has to match datatype of Testvalue1, right?

3. Isnt there an OleDbCommandBuilder class? 

Have a nice day,

Tobias Ehling