473,372 Members | 805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

HELP: duplicated info when re-reading Access db Tables

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
Nov 21 '05 #1
1 1550
Mr. B.

I thought that the most change for the problem you have is when you don't
have the primary key in your schema.

Just my thought,

Cor
Nov 21 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: onTilt | last post by:
Hi, I have few questions and doubts,so please help: Is it true that Runtime class holds runtime information on all objects? Is it true that a subclass has more, or equal amount of...
3
by: Mirek Rusin | last post by:
....what is the best way to force duplicated unique or primary key'ed row inserts not to raise errors? duplicated rows can be ignored or updated as well - it really doesn't matter. to be...
2
by: TeknoCat | last post by:
Hey everyone, I may be repeating myself here, but if someone sent a reply then I missed it, and I can't get Outlook Express to download any messages more than 2 days old. Anyway, I'm having a...
0
by: Eric Chaves | last post by:
Hi Folks I'm tryingto use the EventLog class to wrote info on my own log (ie, not in Application). If I use the same text for Source and LogName, everything goes right. If I try to use asource...
3
by: Shapper | last post by:
Hello, I have a third party control in my page. I need to set the properties of Labels and Images in this control when: 1. User enters the Page. 2. A button is clicked which changes culture...
10
by: One Handed Man [ OHM ] | last post by:
If this is duplicated, I'm sorry because I cant find the original post I made today Anyway . . Now its my turn to ask a question I want to develop an app which will run in the system tray....
0
by: Olivier Bonneau | last post by:
Hello, I have a MDI application. when I have a few MDI children open, and then close one of them, the ControlBox for the MDI children becomes duplicated, right next to one another in the far...
2
by: ste | last post by:
VS2005, .NET2 I have a dll that is using TraceSources with no problems. The dll is linked to a webservice. The webservice creates its own traceSource and every thing looks fine.. however...
6
by: hzgt9b | last post by:
Using VS 2003, .NET: I developed a windows application that performs several actions based on an input file. The application displays a progress bar as each action executes. Based on new...
4
by: matpac4 | last post by:
I'm a bit stuck! Please can someone help. Here's what I'm trying to build: 1. Webform which puts variables into a database (each with unique ID field) 2. Webform also allows attachment which...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.