Syntax error in UPDATE statement.

Question

I am trying to update information in specific excel rows/colums, and I keep getting 

Syntax error in UPDATE statement.

here is my code:

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 sheet.Cells[6, B] = VALUES ('{0}')", TextBox1.Text);

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

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

Thank you!

Answer 1

You are mixing VBA and SQL grammar. Make up your mind. Either use VBA or use SQL, not both.

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP

Answer 2

Please review syntax for an SQL Update statement here http://www.w3schools.com/sql/sql_update.asp then look at your update statement which indicates a syntax error because a) you can not have sheet.Chell[6,B] = VALUES {'{0}') but instead need to have the update statement conform as per the link above and note the comment about not having a Where clause in the SQL update statement you will update all rows. Also check out the following Code Project article on OleDb and Excel http://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled#dml in regards to updating data.

---

KSG

Answer 3

I would like to use sql, where is my VBA grammar?  I don't really know what you are talking about...

Answer 4

UPDATE [w1] SET secondintcol = 3 where firsttextcol = 'firsttextcol';

I guess is has to do something with that?  would it look like this?

UPDATE [MySheetName] SET secondintcol = ColumNumber where firsttextcol = 'RowName';

Answer 5

The table name ends with $. See How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET

If you want to use the objects documented in VBA reference such as sheet or cells, you can find plenty of example codeat http://social.msdn.microsoft.com/Forums/en/exceldev/threads

---

---

The following is signature, not part of post
Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
Visual C++ MVP