locked
Binding excel with OLEDB RRS feed

  • Question

  • Hello. I have a code that reads the excel file perfectly and binds it to the datagridview. However, when I try to update the changes made on the datagridview back by calling dAdapter.Update(dTable); it throws the following errors.

    For insert:

    No value given for one or more required parameters.

    For update:

    Comma related error in query

    I'm not sure what is causing this error. Any guidelines would be greatly appreciated.


    public partial class Form1 : Form
        {
            private OleDbDataAdapter dAdapter;
            private OleDbCommandBuilder cBuilder;
            private DataTable dTable;
            private string strConn;
            public Form1()
            {
                InitializeComponent();
            }

            public void ImportExcelXLS()
            {
                bool hasHeaders = false;
                string FileName = @"c:\asdf.xlsx";
                string HDR = hasHeaders ? "Yes" : "No";
                if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                else
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

                string query = "SELECT * FROM [Sheet1$]";

                dAdapter = new OleDbDataAdapter(query, strConn);


                dTable = new DataTable();

                dAdapter.Fill(dTable);

                dAdapter.InsertCommand = new OleDbCommand("INSERT INTO [Sheet1$] ([col1], [col2]) VALUES (@col1, @col2)");
                dAdapter.UpdateCommand = new OleDbCommand("UPDATE [Sheet1$] set col1 = ?, col2 = ? WHERE col1 = ?, col2 = ?");
            }

            private void load_Click(object sender, EventArgs e)
            {
                ImportExcelXLS();
                dataGridView1.DataSource = dTable;
            }

            private void save_Click(object sender, EventArgs e)
            {
                cBuilder = new OleDbCommandBuilder(dAdapter);
                dAdapter.Update(dTable);
            }
        }

                            
    Friday, August 3, 2012 10:20 AM

All replies

  • This SQL:

    INSERT INTO [Sheet1$] ([col1], [col2]) VALUES (@col1, @col2)

    contains two parameters "@col1", and "@col2". 

    You've been told that no value was given to those parameters.

    This SQL:

    UPDATE [Sheet1$] set col1 = ?, col2 = ? WHERE col1 = ?, col2 = ?

    contains five syntax errors. Question sings "?" are not permitted there. Plus comma should be replaced with "AND". Usually parameters are used at those places:

    UPDATE [Sheet1$] set col1 = @param1, col2 = @param2 

    WHERE col1 = @condition1 AND col2 = @condition2


    Developer


    Thursday, August 9, 2012 12:45 PM