I've been looking at working with Excel data.

I understand the process of getting the data into a dataset and modifying
it. It's one of simple beauty that is well documented. Now, I want to send
the updated data set back. I suspect this is also simple but it eludes me.

I have:
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
        Dim Conn As System.Data.OleDb.OleDbConnection
        Conn = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=" & FileName1 & ";" & _
              "Extended Properties=Excel 8.0;")
        Dim da As New System.Data.OleDb.OleDbDataAdapter("select * update ["
& sheetname1 & "]", Conn)
        Try
            Dim cmdbldr As New System.Data.OleDb.OleDbCommandBuilder(da)
            da.UpdateCommand = cmdbldr.GetUpdateCommand
            da.Update(DS)
        Conn.Close()
        Catch ex As System.Data.OleDb.OleDbException
            MsgBox(ex.Message)
        End Try
    End Sub

Where ds is the dataset and sheetname1="sheet1$".

This code throws the error: missing operator in querry expression
"update[sheet1$]". The code halts on the da.UpdateCommand line when the try
loop is disabled with an unhandled, unspecified
System.Data.OleDb.OleDbException.

I suspect the problem is with the line:

Dim da As New System.Data.OleDb.OleDbDataAdapter("select * update [" &
sheetname1 & "]", Conn)

Perhaps it is incomplete in some way.

I am sure I can loop all of the stuff in the data set back into the excel
sheet with explicit commands, specifying columns and values and all of that.
But how can I simply make the contents of the excel sheet mirror the changed
dataset (in the same simple way I make the dataset mirror the excel sheet to
begin with)??

¤ I've been looking at working with Excel data.
¤
¤ I understand the process of getting the data into a dataset and modifying
¤ it. It's one of simple beauty that is well documented. Now, I want to send
¤ the updated data set back. I suspect this is also simple but it eludes me.
¤
¤ I have:
¤     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles Button3.Click
¤         Dim Conn As System.Data.OleDb.OleDbConnection
¤         Conn = New System.Data.OleDb.OleDbConnection( _
¤               "provider=Microsoft.Jet.OLEDB.4.0; " & _
¤               "data source=" & FileName1 & ";" & _
¤               "Extended Properties=Excel 8.0;")
¤         Dim da As New System.Data.OleDb.OleDbDataAdapter("select * update ["
¤ & sheetname1 & "]", Conn)
¤         Try
¤             Dim cmdbldr As New System.Data.OleDb.OleDbCommandBuilder(da)
¤             da.UpdateCommand = cmdbldr.GetUpdateCommand
¤             da.Update(DS)
¤         Conn.Close()
¤         Catch ex As System.Data.OleDb.OleDbException
¤             MsgBox(ex.Message)
¤         End Try
¤     End Sub
¤
¤ Where ds is the dataset and sheetname1="sheet1$".
¤
¤ This code throws the error: missing operator in querry expression
¤ "update[sheet1$]". The code halts on the da.UpdateCommand line when the try
¤ loop is disabled with an unhandled, unspecified
¤ System.Data.OleDb.OleDbException.
¤
¤ I suspect the problem is with the line:
¤
¤ Dim da As New System.Data.OleDb.OleDbDataAdapter("select * update [" &
¤ sheetname1 & "]", Conn)
¤
¤ Perhaps it is incomplete in some way.
¤
¤ I am sure I can loop all of the stuff in the data set back into the excel
¤ sheet with explicit commands, specifying columns and values and all of that.
¤ But how can I simply make the contents of the excel sheet mirror the changed
¤ dataset (in the same simple way I make the dataset mirror the excel sheet to
¤ begin with)??

See if the following helps:

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934


Paul
~~~~
Microsoft MVP (Visual Basic)

Yes, every thing seems to point to this type of explicit updating where
fields and records must be specified.

However, in Programming VB.NET by Balena (MS Press) in the Chapter 21 "ADO
in Disconnected Mode" pp 1097 et al, an update of the form
da.update(ds,"tableName") appears. Yet, I cannot seem to make that work.

For example, I have code which reads an excel table into a a dataset(ds). A
grid is used to make modifications. I have verified that those modifications
have indeed occured in the dataset. but when I run the following:

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
        Dim Conn As System.Data.OleDb.OleDbConnection
        Conn = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=" & FileName1 & ";" & _
              "Extended Properties=Excel 8.0;")
        Conn.Open()

        da = New System.Data.OleDb.OleDbDataAdapter( _
                      "select * from [" & sheetname1 & "]",
OleDbConnection1(CurrentIndex))
        Dim cmdbldr As New OleDbCommandBuilder(da)
        da.InsertCommand = cmdbldr.GetInsertCommand
        da.DeleteCommand = cmdbldr.GetDeleteCommand
        da.UpdateCommand = cmdbldr.GetUpdateCommand
        da.Update(DS, sheetname1)
        Conn.Close()
    End Sub

I get the error: The DataAdapter.SelectCommand.Connection property needs to
be initialized???

¤ Yes, every thing seems to point to this type of explicit updating where
¤ fields and records must be specified.
¤
¤ However, in Programming VB.NET by Balena (MS Press) in the Chapter 21 "ADO
¤ in Disconnected Mode" pp 1097 et al, an update of the form
¤ da.update(ds,"tableName") appears. Yet, I cannot seem to make that work.
¤
¤ For example, I have code which reads an excel table into a a dataset(ds). A
¤ grid is used to make modifications. I have verified that those modifications
¤ have indeed occured in the dataset. but when I run the following:
¤
¤     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
¤ System.EventArgs) Handles Button3.Click
¤         Dim Conn As System.Data.OleDb.OleDbConnection
¤         Conn = New System.Data.OleDb.OleDbConnection( _
¤               "provider=Microsoft.Jet.OLEDB.4.0; " & _
¤               "data source=" & FileName1 & ";" & _
¤               "Extended Properties=Excel 8.0;")
¤         Conn.Open()
¤
¤         da = New System.Data.OleDb.OleDbDataAdapter( _
¤                       "select * from [" & sheetname1 & "]",
¤ OleDbConnection1(CurrentIndex))
¤         Dim cmdbldr As New OleDbCommandBuilder(da)
¤         da.InsertCommand = cmdbldr.GetInsertCommand
¤         da.DeleteCommand = cmdbldr.GetDeleteCommand
¤         da.UpdateCommand = cmdbldr.GetUpdateCommand
¤         da.Update(DS, sheetname1)
¤         Conn.Close()
¤     End Sub
¤
¤ I get the error: The DataAdapter.SelectCommand.Connection property needs to
¤ be initialized???

I don't see code for the DataAdapter's Fill method (on DS). Has it been omitted?


Paul
~~~~
Microsoft MVP (Visual Basic)

I created an original dataadaptor to fill the dataset in a previous sub.

Does my dataadaptor for the update need to be the same dataadaptor I used
for the original fill?

I changed my approach to using a single dataadaptor (da).

My update sub is:
    Private Sub Update_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btn_Update.Click
        DBCON.Open()
        Dim cmdbldr As New OleDbCommandBuilder(da)
        da.UpdateCommand = cmdbldr.GetUpdateCommand
        da.Update(DS, sheetname1)
        DBCON.Close()
    End Sub

I now get the error:

Dynamic SQL generation for the DeleteCommand is not supported against a
SelectCommand that does not return any key column information. on the line:

        da.UpdateCommand = cmdbldr.GetUpdateCommand

I suspect the problem is that Excel does not provide a primary key.






Show quoteHide quote

Counting threads
HttpWebRequest
Using Rich Text Box - make a line bold
testing for exceptions
inheritence
GetMac address
Using Word for RTF files creation to use with RichTextBox
Defaullt Printer....
Problem Downloading PDF with HTTPWebRequesst
Releasing resources...