Update Excel through Datagrid

[jared.geli]

Hi guys. I need help in updating Excel files through Datagrid. I can update it through a fixed update sql statement(Update table_name Set field1=value1, field2=value2 Where ID=unique_no) but I encountered a situation where a spreadsheet can have 3 columns, sometimes 5 and so on. My point is can I update the excel file without the restriction of columns based on Set. I know Set is inseperable in the Update Command so I tried the Interop way but my application stopped working, it doesn't show any error. Here's what I have so far:

xlWorkBook = xlApp.Workbooks.Open(path)

            xlWorkSheet = xlWorkBook.Worksheets(3)
            Dim w = 0
            Dim r = w + 3
            Dim x = DataGridView1.RowCount - 1
            Do

                Dim y = 1
                Dim c = y + 1
                Dim z = DataGridView1.ColumnCount
                Do

                    xlWorkSheet.Cells(r, c) = DataGridView1.Rows(w).Cells(y).Value

                Loop While (y < z)
                w = w + 1
            Loop While (w < x)

xlWorkBook.Save()
xlWorkBook.Close()

[jared.geli]

Update: I tried using Update command again but this time it's going to update every cell per row using loop and it says cannot update expression; field not updateable

MyConnection.Open()
            Dim w = 0
            Dim r = w + 3
            Dim x = DataGridView1.RowCount - 1
            Do

                Dim y = 1
                Dim c = y + 1
                Dim z = DataGridView1.ColumnCount
                Do

                    Dim updt As OleDbCommand = New OleDbCommand("Update [" & ComboBox1.Text & "$] Set [" & DataGridView1.Columns(y).Name.ToString & "] = '" & DataGridView1.Rows(w).Cells(y).Value & "' Where F1 = '" & DataGridView1.Rows(w).Cells(0).Value & "'", MyConnection)
                    updt.ExecuteNonQuery()
                    y = y + 1

                Loop While (y < z)
                w = w + 1
            Loop While (w < x)

            MyConnection.Close()

            xlWorkBook = xlApp.Workbooks.Open(path)

            xlWorkBook.Save()

            xlWorkBook.Close()

[jared.geli]

Update:

For Each rw As DataGridViewRow In DataGridView1.Rows
                For i As Integer = 1 To rw.Cells.Count
                    Dim updt As OleDbCommand = New OleDbCommand("Update [" & ComboBox1.Text & "$] Set [" & DataGridView1.Columns(i).Name.ToString & "] = '" & rw.Cells(i).Value & "' Where F1 = '" & rw.Cells(0).Value & "'", MyConnection)
                    updt.ExecuteNonQuery()
                Next
            Next

still having the same error

[jared.geli]

Nevermind problem solved. It seems it can't update read only cells on datagrid

 For Each rw As DataGridViewRow In DataGridView1.Rows
                For i As Integer = 1 To rw.Cells.Count - 1
                    If rw.Cells(i).ReadOnly = False Then

                        Dim updt As OleDbCommand = New OleDbCommand("Update [" & ComboBox1.Text & "$] Set [" & DataGridView1.Columns(i).Name.ToString & "] = '" & rw.Cells(i).Value & "' Where F1 = '" & rw.Cells(0).Value & "'", MyConnection)
                        updt.ExecuteNonQuery()

                    End If
                Next
            Next