I am developing a VB.Net windows application to replace a legacy Access database. I started with the legacy database (.mdb) and made changed to tables (like deleting unused columns) and changing anniversaries from 3 columns (day, month, and year) to DataTime etc. I recognized the need to import data from the legacy system at the time when the new one goes live and wrote an import dialog to do this. And this is where my problem is. The importer correctly imports the data and writes new tables, but each time I restart the application I have the old data back.
This is the approach I took:
In the load event I fill each tableadapter in the new (target) dataset as follows:
Me.ClientInfoTableAdapter1.Fill(Me.DataSet1.ClientInfo)
I Then populate a TreeView with nodes for each table name in the dataset by iterating the dataset
For Each table As DataTable In DataSet1.Tables
Dim nm As String = table.TableName
TreeView1.Nodes.Add(nm)
Next
The user can choose a legacy database (.mdb file) using an OpenfileDialog, then ticks the tables (in the TreeView) that should be imported and presses the import button.
At this stage my code iterates the Treeview for each checked table name it instantiates a datatable as follows:
‘get a connection string to (source) legacy database
Dim fromConnection As String = GetFromConnection()
DataSet1.EnforceConstraints = False
For Each nd As TreeNode In TreeView1.Nodes
If nd.Checked Then
Dim tbl As DataTable = DataSet1.Tables(nd.Text)
ImportTable(fromConnection, tbl)
Next
DataSet1.EnforceConstraints = True
If tbl.HasErrors Then
‘handle errors
Else
�span style="color:green"> If no errors, AcceptChanges.
tbl.AcceptChanges()
End If
The ImportTable method looks like this:
Public Sub ImportTable(ByVal conn As String, ByVal tbl As DataTable)
'tbl is destination table to update, conn is the source connection string
Table_Label.Text = tbl.TableName
Using connection As New OleDbConnection(conn)
connection.Open()
tbl.Clear()
ProgressBar1.Value = 0
Select Case tbl.TableName
Case "ClientInfo"
'
'handle New Clientinfo table changes
Import_ClientInfo(tbl, connection)
. . .
Case Else
'
'do generic (unchanged table) import
Import_Generic(tbl, connection)
End Select
End Using
End Sub
In the actual import method I use a reader to get the legacy data (in rows). I clear the destination table and add new rows to it (changing some data fields in the iteration. The following is an example:
Private Sub Import_ClientInfo(ByVal tbl As DataTable, ByVal conn As OleDbConnection)
Dim t As ClientInfoDataTable = CType(tbl, ClientInfoDataTable)
'count rows and setup progress bar, also use it to verify rows
SetmaxRows("ClientInfo", conn)
Dim reader As OleDbDataReader = GetReader(conn, "SELECT * FROM ClientInfo")
If reader IsNot Nothing Then
Dim index As Integer() = GetOrdinals(t, reader)
'these columns have been changed
'birthday was stored in 3 columns (d, m, y); is now one date column
Dim di As Integer = reader.GetOrdinal("ClientBirthdayDay")
Dim mi As Integer = reader.GetOrdinal("ClientBirthdayMonth")
Dim yi As Integer = reader.GetOrdinal("ClientBirthdayYear")
Dim bd As Integer = t.ClientBirthdayColumn.Ordinal
'these column names have changed
Dim si As Integer = reader.GetOrdinal("ClientSex")
Dim gi As Integer = t.ClientGenderColumn.Ordinal
index(gi) = si
'these are new columns
Dim pi As Integer = t.IsPersonColumn.Ordinal
If reader.HasRows Then
While reader.Read()
'
' Create a new DataRow.
Dim row As ClientInfoRow
row = CType(t.NewRow(), ClientInfoRow)
'set all new fields "IsPerson = True" row(pi) = True
'
'convert the day, month, year values to a date
row(bd) = Convert_Date(reader, di, mi, yi)
'
'fill the other matching columns
FillMatchingColumns(t, reader, index, row)
t.Rows.Add(row)
End While
End If
reader.Close()
End If
End Sub
On completion of this table control returns to the Import method which does a
tbl.AcceptChanges()
and on when the user is done the ‘OK_Button�method of the dialog does a
Me.TableAdapterManager1.UpdateAll(DataSet1)
before closing.
Next time I run the application the imported changes are not there. What am I doing wrong? Obviously I have omitted code above to simplify the examples, but the concept is shown. Can someone please help me?
Ryn
Ryn
Home > Visual Basic General > Need help to update source database - access
