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)??
--
mark b
