OleDbDataAdapter and Excel

[2ndmessiah]

I have had this headache for a few days now. Thought I had sorted it but haven't.

I have copied worksheets from an excel workbook into a dataset, with each a datatable for each worksheet.

I then use a DataGridView to Display and edit the datatable.

DataGridview.DataSource = MydataSet.DataTable(0) sort thing.

But for some reason I just cant figure out how to write the datatable back to the excel worksheet.

I have this but Im probably barking up the wrong tree.

 Public Function WriteDataTable(ByVal SheetName As String, ByVal ColumnName As String) As Boolean
        Dim Sql As String = "UPDATE [" & SheetName & "$] SET " & ColumnName & " = ?"
        Debug.WriteLine(Sql)
        Try
           
            Dim MyAdapter As OleDbDataAdapter = New OleDbDataAdapter(Sql, MyConnection)

           
           

            MyConnection.Open()
            MyAdapter.Update(MyDataSet, SheetName)
            MyConnection.Close()
            Return True
        Catch ex As Exception

            MyConnection.Close()
            Debug.WriteLine(ex.ToString)

            Return Nothing

        End Try
    End Function

This headache just wont go away. Even an explaination why this dont work would help.

[2ndmessiah]

OK after much reading and scratching of the head it appears using Excel is not the best way to approach my problem.
I have come up with a work round but on later revisions I think excel will be dropped.

[Kochanski]

Here's a solution I devised working with Excel. You'll need to work it out, but what is happening is I'm storing variables in separate TextBox/s then writing them to the Excel file. Obviously you have to establish the variables with a Dim Surname As String statement before this code. Scroll along the code line to see the exact order. After that, I resave the file. NOTE that the order of values has to be the same as in the line that uses = ?

'Values for variables
        Surname = TextBox1.Text
        ForeName = TextBox2.Text
        Instrument = TextBox4.Text
        ID = TextBox5.Text
        Title = TextBox3.Text
        Member = TextBox15.Text
        Fee = TextBox8.Text
        Paid = TextBox9.Text
        GiftAid = TextBox10.Text
        Address1 = TextBox11.Text
        Address2 = TextBox12.Text
        Address3 = TextBox13.Text
        PostCode = TextBox14.Text
        Telephone = TextBox7.Text
        Email = TextBox6.Text
        NOVEMBER = TextBox16.Text
        DECEMBER = TextBox17.Text
        XMAS = TextBox18.Text
        FEBRUARY = TextBox19.Text
        MARCH = TextBox20.Text
        JUNE = TextBox21.Text
        HalfFULL = TextBox22.Text
        Try
            'UPDATE cells according to an ID number

            Dim myCommand As New System.Data.OleDb.OleDbCommand
            Dim sql As String



            sql = "UPDATE [Sheet1$] SET Surname = ?, Forename = ?, Instrument = ?, Title = ?, Member = ?, Fee = ?, Paid = ?, GiftAid = ?, Address1 = ?, Address2 = ?, Address3 = ?, PostCode = ?, Telephone = ?, Email = ?, NOVEMBER = ?, DECEMBER = ?, XMAS = ?, FEBRUARY = ?, MARCH = ?, JUNE = ?, HalfFULL = ? WHERE ID = ('" & Me.TextBox5.Text & "')"
            ' BAK sql = "UPDATE [Sheet1$] SET Surname = ?, Forename = ?, Instrument = ?, Title = ?, Member = ?, Fee = ?, Paid = ?, GiftAid = ?, Address1 = ?, Address2 = ?, Address3 = ?, PostCode = ?, Telephone = ?, Email = ?, NOVEMBER = ?, DECEMBER = ?, XMAS = ?, FEBRUARY = ?, MARCH = ?, JUNE = ? WHERE ID = ('" & Me.TextBox5.Text & "')"

            With myCommand.Parameters
                'These must be in the same order as the UPDATE code in the line above(note ID is the last one)
                'TIP - make sure that the actual Excel column header is exactly the same as anything you add
                .AddWithValue(Surname, TextBox1.Text)
                .AddWithValue(ForeName, TextBox2.Text)
                .AddWithValue(Instrument, TextBox4.Text)
                .AddWithValue(Title, TextBox3.Text)
                .AddWithValue(Member, TextBox15.Text)
                .AddWithValue(Fee, TextBox8.Text)
                .AddWithValue(Paid, TextBox9.Text)
                .AddWithValue(GiftAid, TextBox10.Text)
                .AddWithValue(Address1, TextBox11.Text)
                .AddWithValue(Address2, TextBox12.Text)
                .AddWithValue(Address3, TextBox13.Text)
                .AddWithValue(PostCode, TextBox14.Text)
                .AddWithValue(Telephone, TextBox7.Text)
                .AddWithValue(Email, TextBox6.Text)
                .AddWithValue(NOVEMBER, TextBox16.Text)
                .AddWithValue(DECEMBER, TextBox17.Text)
                .AddWithValue(XMAS, TextBox18.Text)
                .AddWithValue(FEBRUARY, TextBox19.Text)
                .AddWithValue(MARCH, TextBox20.Text)
                .AddWithValue(JUNE, TextBox21.Text)
                .AddWithValue(HalfFULL, TextBox22.Text)

                'ID is not used here

            End With

            myCommand.Connection = con
            myCommand.CommandText = sql
            con.Open()
            myCommand.ExecuteNonQuery()
            con.Close()

            'SAVE section - saves the Excel properly without displaying alerts
            Dim MyFileName As String = "Orchcurrent.xls"
            Dim XLapp As Object = CreateObject("Excel.Application")
            Dim WBook As Object = XLapp.Workbooks.Open(myFolderName & "\Orchcurrent.xls")
            XLapp.Visible = False
            Dim apSheet As Object = XLapp.Worksheets(1)
            XLapp.DisplayAlerts = False
            WBook.SaveAs(myFolderName & MyFileName)
            XLapp.Displayalerts = True
            WBook.Close()
            XLapp.Quit()
            XLapp = Nothing
            'END of SAVE section
            Me.BackColor = Color.WhiteSmoke
            MsgBox("Updated")
            Me.Label25.Visible = False
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try