Updating an Excel file through ADO.Net

[M Schnell]

All,

I'm trying to take an excel file and insert data from a database into specific cells and then send the file to the user. I've been able to open the excel file and can even read data from specific cells. However, when I try to insert data into the sheet and then save it back, the data gets lost. Here is the applicable part of my code.

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString = sConnectionString + " Data Source=" +sEmailPath & "\energy.xls" + ";"
sConnectionString = sConnectionString + "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
objConn = New OleDbConnection(sConnectionString)

Try
objConn.Open()
Catch ex As Exception
'Error handling code
End Try

'Create new OleDbCommand to return data from worksheet.

objCmdSelect = New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)

'Create new OleDbDataAdapter that is used to build a DataSet
'based on the preceding SQL SELECT statement.

objDA = New OleDbDataAdapter

'Pass the Select command to the adapter.

objDA.SelectCommand = objCmdSelect

'Create new DataSet to hold information from the worksheet.

objDS = New DataSet

'Fill the DataSet with the information from the worksheet.

objDA.Fill(objDS, "Energy")

With objDS.Tables(0)
.Rows(1).Item(1) = ""
.Rows(1).Item(2) = 0
.Rows(1).Item(3) = "CL"
.Rows(1).Item(4) = SeriesFlowFactorLabel.Text
.Rows(1).Item(5) = (CType(hpPerFanMotorLabel.Text, Int32) * CType(numberOfFanMotorsLabel.Text, Integer)).ToString
.Rows(1).Item(6) = (CType(pumpMotorCountLabel.Text, Int32) * CType(pumpMotorCountLabel.Text, Integer)).ToString
.Rows(1).Item(6) = quantityLabel.Text
End With

objDS.AcceptChanges()

objDA.Update(objDS, "Energy")

objConn.Close()

objDS.Dispose()
Any help would be appreciated.

Michael Schnell

 

[Guest]

Try a "Named Range" for your data table rather than just the worksheet...

 

[Guest]

And another thing:

I don't think you've specified the data adapter UPDATE command...

HTH

 

[M Schnell]

Per your first news message, I did go to a named range and it's still not
working. Sometimes the data gets lost, and it isn't saved back and
sometimes I get an error: ""Update unable to find TableMapping".

Also, I have put in an update command, which I got from an msdn article:
"UPDATE [workingArea] SET F2 = ? WHERE F1 = ?"

If this isn't right, can someone please point me in the right direction.

Thanks,

Michael Schnell

And another thing:

I don't think you've specified the data adapter UPDATE command...

HTH

into specific cells and then send the file to the user. I've been able to
open the excel file and can even read data from specific cells. However,
when I try to insert data into the sheet and then save it back, the data
gets lost. Here is the applicable part of my code.

sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnectionString = sConnectionString + " Data Source=" +sEmailPath & "\energy.xls" + ";"
sConnectionString = sConnectionString + "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""
objConn = New OleDbConnection(sConnectionString)

Try
objConn.Open()
Catch ex As Exception
'Error handling code
End Try

'Create new OleDbCommand to return data from worksheet.

objCmdSelect = New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)

'Create new OleDbDataAdapter that is used to build a DataSet
'based on the preceding SQL SELECT statement.

objDA = New OleDbDataAdapter

'Pass the Select command to the adapter.

objDA.SelectCommand = objCmdSelect

'Create new DataSet to hold information from the worksheet.

objDS = New DataSet

'Fill the DataSet with the information from the worksheet.

objDA.Fill(objDS, "Energy")

With objDS.Tables(0)
.Rows(1).Item(1) = ""
.Rows(1).Item(2) = 0
.Rows(1).Item(3) = "CL"
.Rows(1).Item(4) = SeriesFlowFactorLabel.Text
.Rows(1).Item(5) = (CType(hpPerFanMotorLabel.Text, Int32) * CType(numberOfFanMotorsLabel.Text, Integer)).ToString
.Rows(1).Item(6) = (CType(pumpMotorCountLabel.Text, Int32) * CType(pumpMotorCountLabel.Text, Integer)).ToString
.Rows(1).Item(6) = quantityLabel.Text
End With

objDS.AcceptChanges()

objDA.Update(objDS, "Energy")

objConn.Close()

objDS.Dispose()
Any help would be appreciated.

Michael Schnell

 

[Kevin Yu [MSFT]]

Hi Michael,

In Excel, we can define a name for the area you are working on. Using
[Sheet1$] for the whole sheet is also fine. In the update command, we can
also use the named range or the sheet name like in the select statement.
For example:

UPDATE [workingArea] SET F2 = ? WHERE F1 = ?

Make sure that the column name is correct.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[M Schnell]

I had named the area I'm working on. I also have the same code you listed below for my update field. The column names aren't being used because I'm setting individual cells to values:

With objDS.Tables(0)
.Rows(0).Item(0) = ""
.Rows(0).Item(1) = 0
.Rows(0).Item(2) = "CL"
.Rows(0).Item(3) = SeriesFlowFactorLabel.Text
.Rows(0).Item(4) = (CType(hpPerFanMotorLabel.Text, Int32) * CType(numberOfFanMotorsLabel.Text, Integer)).ToString
.Rows(0).Item(5) = (CType(pumpMotorCountLabel.Text, Int32) * CType(pumpMotorCountLabel.Text, Integer)).ToString
.Rows(0).Item(6) = quantityLabel.Text
end with
I'm then trying to call:

objDS.AcceptChanges()
objDA.Update(objDS, "workingArea")
But I come up with the error "Update unable to find TableMapping" . Can anyone please help?

 

[Kevin Yu [MSFT]]

Hi M Schenell,

I meant that we have to check for the column name in the update statement
to see if F1 and F2 matches the column name in the DataTable.

UPDATE [workingArea] SET F2 = ? WHERE F1 = ?

You can try to check with objDS.Tables(0).Columns.ColumnName for each
column name. Or just put objDS.Tables(0)into a DataGrid, all the column
names will be displayed on the header row.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."