By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,512 Members | 3,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,512 IT Pros & Developers. It's quick & easy.

beginner update excel ?

P: n/a
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
Feb 14 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Tue, 14 Feb 2006 13:24:26 -0800, "mark" <ma**@discussions.microsoft.com> wrote:

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...b;EN-US;316934
Paul
~~~~
Microsoft MVP (Visual Basic)
Feb 15 '06 #2

P: n/a
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???

Feb 16 '06 #3

P: n/a
On Thu, 16 Feb 2006 11:07:32 -0800, "mark" <ma**@discussions.microsoft.com> wrote:

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)
Feb 16 '06 #4

P: n/a

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?
--
mark b
Feb 17 '06 #5

P: n/a
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.


--
mark b
"Paul Clement" wrote:
On Thu, 16 Feb 2006 11:07:32 -0800, "mark" <ma**@discussions.microsoft.com> wrote:

¤ 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)

Feb 17 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.