Problems with OLEDbDataAdapter method Update

[Frank Bacon]

I run this code without error. I create a recordset of the changes, the row
I want to add, but the Update command doesn't add the records to the DB.
The Update Command returns a zero indicating that no records change. I'm
lost does anyone have any ideas?

'Open Appropriate VAR Table

strSql = "Select * FROM [" + CStr(dcSheetName.ColumnName) + "]
ORDER BY [Date];"

dsVarTables = CreateDataSet(strSql, myConnection)

daVarTables = New OleDbDataAdapter()

daVarTables.SelectCommand = New OleDbCommand(strSql,
myConnection)



NewRow = dsVarTables.Tables(dcSheetName.ColumnName).NewRow()


oleCB = New OleDbCommandBuilder(daVarTables)

oleDBC = oleCB.GetInsertCommand

strSql = oleDBC.CommandText "Used this to make sure I had a
valid INSERT statement

The code Fills each Value in new Data Row....
..
..

..

dsVarTables.Tables(dcSheetName.ColumnName).Rows.Add(NewRow)

dsChanges = dsVarTables.GetChanges(DataRowState.Added)

dbgChanges.DataSource = dsChanges.Tables(1)

dbgChanges.Refresh()

If NewRow.HasErrors() = True Then MsgBox("New Row Has Errors")

Try

RecUpdated = daVarTables.Update(dsChanges)

Catch err As Exception

MessageBox.Show(err.Message)

End Try

MsgBox((dcSheetName.ColumnName) + Str(RecUpdated) + " records
Updated", MsgBoxStyle.OKOnly)

 

[William Ryan eMVP]

Have you verfied that the db HasChanges? If you arne't getting an exception
and aren't trapping it but ignoring it, it probably doesn't think there's
anyhting to update. http://www.knowdotnet.com/articles/efficient_pt4.html .
Also, you have a bunch of code here that's sort of confusing, casts to
strings that are already strings and the like. If you would, could you
explain the intent of the logic too. I can see what's happening but it
doesn't appear to make sense and that might also help narrowing down the
problem. Also, what are you calling .Fill on? you'll want to make sure you
are calling update on the same exact object (in this instance a datatable ).
Let me know and we'll get it figured out.

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[Frank Bacon]

Thanks for taking a look at this. As far as I can tell the db does
recognize the changes in that these lines return a valid dataset and I
am able to display it:
dsChanges = dsVarTables.GetChanges(DataRowState.Added)
dbgChanges.DataSource = dsChanges.Tables(1)

Your second point maybe applicable in that I created the original
dataset in a sub and assigned it to dsVarTables. There is a lot of
unnecessary code such as the Cstr cast that I made in attempts to
segregate the problem by eliminating possibilities.

The application takes non-table-formated data from an Excel workbook and
puts it in an Access Db. The workbook contains ten tables, each of
which correspond to a matching Access Table. Each spreadsheet also has
a Map, an access table holding the cell addresses of for a corresponding
access fields. The application opens the db and gets a list the
spreadsheets, held in access fields whose column name contains the names
of the respective Access Table. Stepping through this list, the app
opens each Excel spreadsheet, an Access Table “Map” with the Cell
location and the Access destination table. Receiving a variant value
(of different data types) from the Excel the app converts this data to
the required data types for Access and assigns it to the appropriate
DataColumn in the new data row. Using an OLEDbCommandBuilder Object the
correct SQL statements were generated (I verified this with oleDBC =
oleCB.GetInsertCommand) For verification I displayed changes to the Db
in a DataGrid.

The fill is called as follows:

daVarTables = New OleDbDataAdapter()
daVarTables.SelectCommand = New OleDbCommand(strSql, myConnection)
oleCB = New OleDbCommandBuilder(daVarTables)
daVarTables.Fill(dsVarTables, dcSheetName.ColumnName)


Could my problem be the call to create my original dataset?

Private Function CreateDataSet(ByRef strSql As String, ByRef conDB
As OleDbConnection) As DataSet

'Open files spreadsheet list and Maplist
Dim daDataAdapter As New OleDbDataAdapter()
Dim dsDataSet As New DataSet("dsCreatedDataSet")
Dim cmdOleCommand As New OleDbCommand()

cmdOleCommand.CommandText = strSql
cmdOleCommand.Connection = myConnection
daDataAdapter.SelectCommand = cmdOleCommand

'Open the db and create the dataset
Try
myConnection.Open()
daDataAdapter.Fill(dsDataSet)
Catch err As OleDbException
MsgBox(err.Message)
Finally
myConnection.Close()
End Try

CreateDataSet = dsDataSet
dsDataSet = Nothing
daDataAdapter = Nothing
cmdOleCommand = Nothing

End Function




*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!