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(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button3.Click
Dim Conn As System.Data.Ole Db.OleDbConnect ion
Conn = New System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=" & FileName1 & ";" & _
"Extended Properties=Exce l 8.0;")
Dim da As New System.Data.Ole Db.OleDbDataAda pter("select * update ["
& sheetname1 & "]", Conn)
Try
Dim cmdbldr As New System.Data.Ole Db.OleDbCommand Builder(da)
da.UpdateComman d = cmdbldr.GetUpda teCommand
da.Update(DS)
Conn.Close()
Catch ex As System.Data.Ole Db.OleDbExcepti on
MsgBox(ex.Messa ge)
End Try
End Sub
Where ds is the dataset and sheetname1="she et1$".
This code throws the error: missing operator in querry expression
"update[sheet1$]". The code halts on the da.UpdateComman d line when the try
loop is disabled with an unhandled, unspecified
System.Data.Ole Db.OleDbExcepti on.
I suspect the problem is with the line:
Dim da As New System.Data.Ole Db.OleDbDataAda pter("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
