Re: Beginner has jet update question

mark wrote on Wed, 15 Feb 2006 08:35:41 -0800:

This is a cross post and I appologize.

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 dataset back to Excel. I suspect this is also simple but it
eludes me.

I have:
Private Sub Update(byval connstring as string)
Dim Conn As System.Data.OleDb.OleDbConnection
Conn = New System.Data.OleDb.OleDbConnection(connstring
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 dataset back into the Excel
*** with explicit commands, specifying columns and values and all of
that. But can I simply make the contents of the Excel *** mirror the
changed dataset (in the same simple way I make the dataset mirror the
Excel *** to begin with)??



This group is for ADO, not ADO.NET, so you might want to check the
appropriate dotnet group.

However, I'm guessing that a DataAdapter takes a SQL string and executes it.
If so, then your problem is that you're providing nonsense for SQL.

select * update [Sheet1$]

What is that supposed to do? SELECT is used to retrieve data, eg.

SELECT * FROM [Sheet1$]

which will retrieve all rows from Sheet1

And UPDATE is used to change data, eg.

UPDATE [Sheet1$] SET Col1 = 1

which will update the column named Col1 to the value 1 on all rows.

With both of the above you use WHERE clauses to limit the rows returned
and/or updated.

Use "select *" to pull all the data from Sheet1 into a recordset (or
whatever the DataAdapter does). You will then need to make your changes, and
do an update of some sort. In ADO (not ADO.NET) you would make changes to
the recordset value returned from Command, and then use the Update method on
the recordset to save those changes back to the source.

Dan


.