VB.net 2003 c/w Framework 1.1 and MS Access db
We have a commercial program that does our Acounting and Time Sheets
(Timberline). At least once a day our Accounting department runs a Script
file that Exports info into an MS Access db file and which has 7 Tables (stuff
like Project info, Project numbers, User names, etc).
I wrote a Time Sheet entry application to read this info and to allow
Employees to write their Time Sheet data into another MS Access db that gets
Imported back into Timberline...all works fine.
I then wrote another application that I read the Timberline exported file
(that has 7 Tables) and build a new Access db into a Single Table (I do this
to simply speed up my Time Sheet application as there is at least a 2-3 second
load hit for each Table that I have to read when my application starts). This
'update' application runs on my PC and every few minutes checks for an updated
Timberline Export and then creates my single table db. I basically read each
table and dump the info into an Array... then write the Array to a new db
file.
Now my update application sort of works fine. I've found out that it will
work flawlessly the FIRST time it does an update... but in subsequent updates
the 7 Tables Data appears to multiple. In other words, the first time, say
there is User "A"... the second time the update happens, User "A" is shown
Twice, etc.
QUESTION IS:
I've tried figuring out what happens and the only thing I can think of is that
somehow the application is re-reading the table info while keeping previous
table info in memory... IF SO... how do I 'CLEAR' out the 7 tables from memory
before I re-read the updated info???? I've tried closing the Connection for
the 7 tables file but that doesn't appear to work...
Here is some of my applicable Code:
In an UPDATE data adapters and schema's are done (7 of them) --- I used the
Wizard to open my Connections and Db files, etc.
' Master Category Table
DaMstrCat.Fill(DsMstrCat.MASTER_JCM_STANDARD_CATEG ORY)
' Master Cost Code Table
' Select ONLY Current Codes (having Boolean as 0)
daMstrCode.SelectCommand.Parameters(0).Value = 0
daMstrCode.Fill(DsMstrCode.MASTER_JCM_STANDARD_COS T_CODE)
Then each table info is put into an Array (one sub-routine per table for ease
of programming):
Private Sub MstrCat()
TextBox3.Text = DsMstrCat.MASTER_JCM_STANDARD_CATEGORY.Rows.Count
If TextBox3.Text > ArraySize Then
ArraySize = TextBox3.Text
ReDim Preserve ProjArray(16, ArraySize)
End If
Dim dtDataTbl As DataTable = DsMstrCat.MASTER_JCM_STANDARD_CATEGORY
' Get Info from Data Base
For cntr = 0 To DsMstrCat.MASTER_JCM_STANDARD_CATEGORY.Rows.Count - 1
Dim dr As DataRow = dtDataTbl.Rows(cntr)
ProjArray(9, cntr) = dr("category")
ProjArray(10, cntr) = dr("description")
Next ' cntr
End Sub
Then I write the Array to my new 1 Table db --- I'm currently writing each
line (about 450 lines) to my db one at a time as I thought that was the
problem):
For cntr = 0 To ArraySize - 1
' Open Connection and ADD New Data
OleDbConnection1.Open()
With DsDataUpdate.Tables("TSUpdate")
Dim NewDr As DataRow = .NewRow
NewDr("Employee") = ProjArray(0, cntr)
NewDr("Employee_Name") = ProjArray(1, cntr)
NewDr("Class") = ProjArray(2, cntr)
NewDr("Chargeout_Level") = ProjArray(3, cntr)
NewDr("Pay_ID") = ProjArray(4, cntr)
NewDr("Employee1") = ProjArray(5, cntr)
NewDr("Pay_Type") = ProjArray(6, cntr)
NewDr("Pay_ID1") = ProjArray(7, cntr)
NewDr("Amount") = Val(ProjArray(8, cntr))
NewDr("category") = ProjArray(9, cntr)
NewDr("Description") = ProjArray(10, cntr)
NewDr("Cost_Code") = ProjArray(11, cntr)
NewDr("Description1") = ProjArray(12, cntr)
NewDr("Group_Cost_Code") = System.Convert.ToBoolean(ProjArray(13,
cntr))
NewDr("Job") = ProjArray(14, cntr)
NewDr("Description2") = ProjArray(15, cntr)
NewDr("Status") = ProjArray(16, cntr)
.Rows.Add(NewDr)
End With
' Send Changes to DataBase and Disconnect
Try
daDataUpdate.Update(DsDataUpdate, "TSUpdate")
'Carry out your code to do stuff to database here...
Catch dbEx As System.Data.OleDb.OleDbException
MessageBox.Show(dbEx.Message)
End Try
OleDbConnection1.Close()
' Next Line
Next cntr
Then I clear all my variables:
' Reset Variables
Erase ProjArray
cntr = 0
cntrA = 0
ArraySize = 0
fd1 = ""
Regards,
Bruce
