beginner update excel ?

G

Guest

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)??
 
P

Paul Clement

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

Guest

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???
 
P

Paul Clement

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

Guest

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?
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

import excel sheet into a sql server table 7
Reading .csv files containing the "-" character 8
MsAccess Database Updation using Dataset 16
Newbie Question - Updating OLE Tables 2
Jet error message when trying to create a DS from Excel 1
DataAdapter using stored procedures for Insert, Update and Delete 1
Reading one record from an Access DB 6
Writing to two tables 12

Top