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