Trouble refreshing dataset

[peterk]

Hello all,

I'm new to vb.net and I'm having trouble with my adapter.fill statements.

I have two tables in an Access database where the number of records are virtually identical. I have a query that links the two tables and displays the records that are not identical. One of the two tables gets updated automatically by a punch clock and I need this query to show the difference and print it out. When I add the record to the first table; it works but the query isn't refreshed and I end up printing the same record!

Here is my code:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] anyRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = dsTimeStamp.tblLastPrint.NewRow[/SIZE]
 
[SIZE=2]anyRow("ANNO") = txtYear.Text[/SIZE]
[SIZE=2]anyRow("EVENTTYPE") = txtEventType.Text[/SIZE]
[SIZE=2]anyRow("FLAGEXPORT") = txtFlagExport.Text[/SIZE]
[SIZE=2]anyRow("GIORNO") = txtDay.Text[/SIZE]
[SIZE=2]anyRow("MESE") = txtMonth.Text[/SIZE]
[SIZE=2]anyRow("MINUTO") = txtMinute.Text[/SIZE]
[SIZE=2]anyRow("ORA") = txtHour.Text[/SIZE]
[SIZE=2]anyRow("PERNO") = txtPerno.Text[/SIZE]
[SIZE=2]anyRow("SECONDO") = txtSecond.Text[/SIZE]
[SIZE=2]anyRow("SOURCE_SYS") = txtSource.Text[/SIZE]
[SIZE=2]anyRow("TERMID") = txtTermID.Text[/SIZE]
[SIZE=2]anyRow("TIMEID_NO") = txtTimeIDNo.Text[/SIZE]
 
[SIZE=2]dsTimeStamp.tblLastPrint.Rows.Add(anyRow)[/SIZE]
 
[SIZE=2]OleDbDataAdapter2.Update(dsTimeStamp, "tblLastPrint")[/SIZE]
 
[SIZE=2]dsTimeStamp.tblLastPrint.AcceptChanges()[/SIZE]
 
[SIZE=2]TimeStamp(sStamp)[/SIZE]
 
[SIZE=2]OleDbDataAdapter1.Fill(DataSet11, "qryPunchDetails")[/SIZE]

When I run the application, my queryPunchDetails contains all the records that need to be printed. For example if I have 10 records in the query, it
prints the first one then, instead of having 9 left, it adds 9 to the dataset and I end up with 19! If I close and reopen the app. I have the correct number of records which is 9.

Please help or point me to the right tutorial because i've searched everywhere including that guy who has the tutorials in his sig. with no result!

Thanks in advance.

 

[cjard]

I've read your post twice, but i'm still confused by this:

When I run the application, my queryPunchDetails contains all the records that need to be printed. For example if I have 10 records in the query, it
prints the first one then, instead of having 9 left, it adds 9 to the dataset and I end up with 19! If I close and reopen the app. I have the correct number of records which is 9.

When you say "need to be printed" do you mean, literally paper flying out of a printer? It adds 9 what? 9 as in a number? like 3+9 = 12, so your dataset contains the number 3 and now contians the number 12?

I guess it would help me if you explain more about the concept of what youre trying to do. What are those text boxes for? Why do you download data from the database? What are you trying to achieve by sending a new row to the database? What is the query text of qryXXX? Which table areyou updating? The same one as the punch clock updates?

If you find it hard to explain further, then I dont mind you posting the project youre working on and I'll take a look - just reduce the size as much as possible to save the forum's bandwidth. Dont post exes etc..

 

[peterk]

Sorry for the confusion, I'll try and make it clearer:

There is a punch clock in our company that records people's punch in and out of work/break.

My task is to have a dotmatrix printer print the latest scan from the reader. (punch clock) Similar to a point of sales printer like a cash receipt

The data is stored in one table (tblPunch) in an access database which I cannot edit (it was developped by an Italian company and we are not supossed to have access to it). Each time someone passes their employee id in front of the card reader the details are instantly saved in the access table (tblPunch).

I created another access DB with a link to tblPunch and created another table called tblLastPrint. tblLastPrint contains the same records as tblPunch from tblPunch except the latest ones.

Since I can't modify the tblPunch table directly with a "LastPrinted" field, I created a query called qryPunchDetails (I linked the two tables together and show the difference in the two). This gives me the number of records that are not yet saved in the tblPunch table (still left to print).

For example, qryPunchDetails has 10 records. Meaning there are 10 records to print out and 10 records to store in the tblLastPrint table. As soon as I store the 10 records in tblLastPrint, I will no longer have any records to print until someone swips their card in front of the reader.

When I load the app. all of the text boxes (txtMonth.Text) are bound to qryPunchDetails. I then add a new row to tblLastPrint and print out the details of that record. I should now have 9 records left to print. This is where the problem occurs. When I fill the DataAdapter, I should have 9 records showing (I also have a datagrid bound to qryPunchDetails) but instead I still have the 10 original records plus 9! So I end up with 19 records left to print. And the text boxes are still bound to the first record that I already printed. When I look at qryPunchDetails in Access, I see 9 records.

Hope this clears it up.
What am I doing wrong?

Thanks for taking the time.

 

[cjard]

Erm.. I'm not sure youre going about this is 100% the right way. Why not just, at regular intervals,. do this:


SELECT * FROM tblPunch WHERE someDate > a_stored_date
'print results
a_stored_date = DateTime.Now
'wait 5 minutes
'run query again


do you see what im doing here? Im finding the records not printed by looking up all entries since the last time that I printed. I know the time I last printed and i remember this time, updating each time i print

 

[peterk]

Thanks for your reply,

I asked my collegue, who come back from sick leave today and he said add this line:

dataset11.clear

Obviously it worked, IT problems are often solved so simply.

BTW, your solution was tried before (which is why I named the other table tblLastPrint) and I can't remember why but I couldn't get it to work.

I'll give it another try; I don't particularly like my current method.


Thanks again!

P.S. Thanks for the links to the walkthroughs it really helped!

 

[cjard]

I was going to suggest clearing the dataset, but there were a few reasons I didnt:

1) Adapters (i'm used to) have a ClearBeforeFill property that defaults to true. Filling a datatable causes it to be cleared by the adapter first
2) I wasnt convinced that your method was an efficient or elegant colution to the problem (no offence intended) - giving you a one line answer might not make you rethink your solution

If you ahve another crack at the solotion i proposed, and you get stuck, then feel free to return for advice