Changes to DataSet not sticking

[Paul Engel]

I am currently making the move from ADO in VB6 to ADO.Net. It seems very
straightforward, but I have a serious, nagging problem w/ some code I've
written.

Here's the pseudo-code
Instantiate an object of Class WDABase (a class that is used in one of my
tutorials to encapsulate some basic DB functions)
Fill a DataSet from table
Set a DataView with which I will be doing my searches and updates
Do the last two items again for a different table
Loop a delimited text file
Pass an array of values to a procedure to either add a record or update
existing records
If no rows are found, add a new row
Else, update any rows that were found
Accept all changes using the DataSet's AcceptChanges method
Set my views to nothing
Close my database connection

I've pasted the code below. I'm stumped! Can one of you ADO.Net jocks easily
spot what I am doing wrong?
-----------------------------------------------------------------
'Global Declarations in VB Code Module
Dim objAccessDB As New WDABase
Dim objDBView1 As New DataView
Dim objDBView2 As New DataView
Dim objDataSet As New DataSet

-----------------------------------------------------------------

'Instantiate the Access DB object and fill DataSet (imbedded in Select Case
logic
'in a procedure)

Using objAccessDB
'Set DataView
SetABCDataViews() 'Call to SetABCDataViews procedure, which works
well
'Revise label
MainForm.StatusLabel.Text = "Data Loaded...Processing Data"
MainForm.StatusLabel.Refresh()

'******* AT THIS POINT...I call a proce that opens a text file and
loop through it
' calling the UpdateABCMergeDB for each line, passing it an array
' of values that were read from a delimited file...then I return
here
' once the entire file has been read and delt with.
LoopTextFile()

'******* THIS is the point where I expect changes that I saw occur
' in the update procedure to "stick". But when the process
' concludes, w/ no errors, NONE of the new records are in
' the database and NONE of the updated records are updated???
objDataSet.AcceptChanges()
'Clear Dataviews
objDBView1 = Nothing
objDBView2 = Nothing
objAccessDB.CloseConnection()
End Using
-----------------------------------------------------------------------
' WDABase snippent - for clarity
'Class WDABase Constructor:
Public Sub New()
'Build the SQL connection string and initialize the Connection
object
Connection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ABC merges 97.mdb;")
End Sub
---------------------------------------------------------------------------
'Procedure to set up DBViews for each table to be searched
Private Sub SetABCDataViews()
Try
'Set up DataView for Subscriber
objAccessDB.OpenConnection()
'Get all Subscribers in a DataReader object
objAccessDB.SQL = "SELECT SubscriberID, SSN, MemberName,
GroupNumber, " & _
"GroupName FROM tblSubscriber"
'Fill the DataSet
objAccessDB.FillDataSet(objDataSet, "Subscriber")
'Set DataView one
objDBView1.Table = objDataSet.Tables(0)
objDBView1.Sort = "SubscriberID, GroupNumber"
Application.DoEvents()
MainForm.Refresh()
'Set up DataView for Group
objAccessDB.Command = Nothing
'Get all Groups in a DataReader object
objAccessDB.SQL = "SELECT GroupNumber, GroupName " & _
"FROM tblGroups"
'Fill the DataTable
objAccessDB.FillDataSet(objDataSet, "Group")
'Set DataView one
objDBView2.Table = objDataSet.Tables(1)
objDBView2.Sort = "GroupNumber"

Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try

End Sub
--------------------------------------------------------------------
' Perform Updates - Called from the procedure that loops my datafile, which
' passes in an array of values. When new records are change or updated, I
' examine the IntelliSense field values and the proper data is in the
associated
' record in the DataViews. So, this logic seems to be working fine...
Private Sub UpdateABCMergeDB(ByRef Values As String())
Try
'Find this item's row(s)
Dim SearchValues As Object() = {Values(0), Values(3)}
Dim FoundRows As DataRowView() = objDBView1.FindRows(SearchValues)
If FoundRows.Length = 0 Then
'There were no hits, add this row
Dim NewRow As DataRowView = objDBView1.AddNew
NewRow(0) = Values(0)
NewRow(1) = Values(1)
NewRow(2) = Values(2).ToUpper
NewRow(3) = Values(4)
NewRow(4) = Values(4).ToUpper
NewRow.EndEdit()
Else
'There were hits, loop them and modify the values
For Each RowToUpdate As DataRowView In FoundRows
RowToUpdate.BeginEdit()
RowToUpdate(1) = Values(1)
RowToUpdate(2) = Values(2).ToUpper
RowToUpdate(4) = Values(4).ToUpper
RowToUpdate.EndEdit()
Next
End If

'Get all Groups in a DataReader object
'Find this item's row(s)
FoundRows = Nothing
FoundRows = objDBView2.FindRows(Values(3))
If FoundRows.Length = 0 Then
'There were no hits, add this row
Dim NewRow As DataRowView = objDBView2.AddNew
NewRow(0) = Values(3)
NewRow(1) = Values(4).ToUpper
NewRow.EndEdit()
Else
'There were hits, loop them and modify the values
For Each RowToUpdate As DataRowView In FoundRows
RowToUpdate.BeginEdit()
RowToUpdate(1) = Values(4).ToUpper
RowToUpdate.EndEdit()
Next
End If


Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try

End Sub

 

[Kerry Moorman]

Paul,

That's a lot of code to look at, but I don't see any sql insert or update
statements anywhere. How are you persisting your dataset changes back to the
database?

Also, AcceptChanges is a method that you almost never want to call, unless
you know exactly what it is doing, since its name is completely misleading.
Why are you calling AcceptChanges?

Kerry Moorman

 

[Paul Engel]

The basis of the logic I have used is the Microsoft MSDN Library. It seems
to make it clear the the DataView is the most flexible object to use for
adding and updating information to a relational database. They inidate that
you derive a dataview from a dataset (which seems to be working for me as I
examine the data in the intellisense Fields collection). Then it indicates
that the method that causes the data to be updated in the underlying
dataview is "EndEdit()", which I am calling, then it suggests using the
AcceptChanges at the DataSet level. I've pasted the text from which I drew
this example from MSDN. Is if flawed?

---------Excerpt Follow----------------
You can use the DataView to add, delete, or modify rows of data in the
underlying table. The ability to use the DataView to modify data in the
underlying table is controlled by setting one of three Boolean properties of
the DataView. These properties are AllowNew, AllowEdit, and AllowDelete.
They are set to true by default.

If AllowNew is true, you can use the AddNew method of the DataView to create
a new DataRowView. Note that a new row is not actually added to the
underlying DataTable until the EndEdit method of the DataRowView is called.
If the CancelEdit method of the DataRowView is called, the new row is
discarded. Note also that you can edit only one DataRowView at a time. If
you call the AddNew or BeginEdit method of the DataRowView while a pending
row exists, EndEdit is implicitly called on the pending row. When EndEdit is
called, the changes are applied to the underlying DataTable and can later be
committed or rejected using the AcceptChanges or RejectChanges methods of
the DataTable, DataSet, or DataRow object. If AllowNew is false, an
exception is thrown if you call the AddNew method of the DataRowView.

If AllowEdit is true, you can modify the contents of a DataRow via the
DataRowView. You can confirm changes to the underlying row using
DataRowView.EndEdit or reject the changes using DataRowView.CancelEdit. Note
that only one row can be edited at a time. If you call the AddNew or
BeginEdit methods of the DataRowView while a pending row exists, EndEdit is
implicitly called on the pending row. When EndEdit is called, proposed
changes are placed in the Current row version of the underlying DataRow and
can later be committed or rejected using the AcceptChanges or RejectChanges
methods of the DataTable, DataSet, or DataRow object. If AllowEdit is false,
an exception is thrown if you attempt to modify a value in the DataView.

When an existing DataRowView is being edited, events of the underlying
DataTable will still be raised with the proposed changes. Note that if you
call EndEdit or CancelEdit on the underlying DataRow, pending changes will
be applied or canceled regardless of whether EndEdit or CancelEdit is called
on the DataRowView.

If AllowDelete is true, you can delete rows from the DataView by using the
Delete method of the DataView or DataRowView object, and the rows are
deleted from the underlying DataTable. You can later commit or reject the
deletes using AcceptChanges or RejectChanges respectively. If AllowDelete is
false, an exception is thrown if you call the Delete method of the DataView
or DataRowView.

The following code example disables using the DataView to delete rows and
adds a new row to the underlying table using the DataView.

 

[Kerry Moorman]

Paul,

In the excerpt you posted, the term "underlying table" means the datatable
that the dataview was created from, not the actual table in the database.

You are successfully updating your datatable. In order to persist the
datatable's data back to the database, there is a good bit more work that
needs to be done.

You might want to investigate the dataadapter, especially its IsertCommand,
UpdateCommand and DeleteCommand properties and its Update method.

Again, I will also mention that you need to really understand what
AcceptChanges is doing before calling it.

Kerry Moorman

 

[Cor Ligthert[MVP]]

Paul,

What is the version you are using, this looks a lot an article from version
2002 where the DataSet and its members were often seen as a kind of stand
alone verhicle, that could be used seperated from a database.

Cor

 

[Paul Engel]

I'm using the version that was installed w/ my VS 2008.

 

[Paul Engel]

Thanks, Kerry. I have begun reading the Professional ADO.NET from Wrox. Lots
of great info. I'm going to get through that and then re-look at my design.
I understand your points, though, and agree that 1) I misunderstood the MSDN
article and 2) will probably find my solution in the DataAdapter class w/
the methods you list.

Thanks,
Paul