Solved

Problem updating a MS Access table from VB.Net 2003

Posted on 2007-03-27
5
260 Views
Last Modified: 2008-03-17
Hi,
I am having trouble getting a MS Access Table to updat from VB.NET 2003. Below is my code. This sub is called every 5 seconds and the Datagrid gets updated correctly. (Two of the columns 'Volts' and 'Reading" get numbers that are randomly generated eslsewhere in code) . If I put a breakpoint in and go back to MS Access I don't see any changes to these two columns. I have indicated the line where I would expect the database table to be updated.
Please show me what I am doing wrong.
Thanks,
Charlie

Public Sub FillSensorGrids()
       
        Dim i%, j%, a$
        Dim conn As New OleDb.OleDbConnection(constr)
        Dim qry$
        qry$ = "SELECT * FROM [Inst_Check_Sensors_EMU]"
        Dim daFSGemu As New OleDb.OleDbDataAdapter(qry$, conn)
        Dim dtFSGemu As New DataTable
        daFSGemu.Fill(dtFSGemu)
        Dim dsFSGemu As New DataSet("dsFSGemu")
   
        If SensorTab.SelectedTab Is TabPageEMU Then
            'EMU Tab is Selected
            i% = 0
           
            For Each dr As DataRow In dtFSGemu.Rows
                dr.Item("Volts") = Format(AinElec!(i%), "####.0.00")
                dr.Item("Reading") = Format(AinPhys(i%, UnitsEMU%), "####0.00")
                dr.AcceptChanges()
                dtFSGemu.AcceptChanges()

                i% = i% + 1
            Next

            'update the Access Database Table 'Inst_Check_Sensors_EMU
            daFSGemu.Update(dtFSGemu)  '<- This is where I think the Access Table would get updated

            DataGrid5.DataSource = dtFSGemu
            DataGrid5.Update()

        ElseIf SensorTab.SelectedTab Is TabPageISO Then
                    'ISO Tab is Selected
        End If
0
Comment
Question by:charlieb01
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 18799929
A dataadapter works by checking the .RowState flags in the datatable.  Those flags are set when changes are made to the rows: .Modified, Deleted, .Added.  AcceptChanges resets all .RowState flags to unchanged.  As you are calling .AcceptChanges immediately after you have made any changes, the .RowState flags are effectively cancelled, and the dataadapter thinks it has nothing to do.

Roger
0
 

Author Comment

by:charlieb01
ID: 18800334
Roger,

Do I need a CommandBuilder and can you give me an example using my code above as to the order in which these commands need to happen?

Thanks,
Charlie
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18800695
Yes, you do need a commandbuilder - sorry, I hadn't noticed you were trying to use a New DataAdapter without one.  Stick

       Dim cb As New OleDb.OleDbCommandBuilder(daFSGemu)

immediately after

       Dim daFSGemu As New OleDb.OleDbDataAdapter(qry$, conn)

Don't call .AcceptChanges on dr at all.  If you want to call .AcceptChanges on dtFSGemu, do it AFTER not BEFORE you've called

            daFSGemu.Update(dtFSGemu)

And, for the code that you show,  the line

        Dim dsFSGemu As New DataSet("dsFSGemu")

is superfluous.

Roger
0
 

Author Comment

by:charlieb01
ID: 18801160
Roger,

That seems to work, thanks. My updated code is below.

One final question:
This Sub FillSensorGrids() is part of a form code and it gets called every 5 seconds based on a timer.
Should the lines of code in between the sets of  '******************************
be called each time or would it be better to have them called only once the first time this Sub is executed? If one time only is better then I could just set up a static var, set it to TRUE after these lines are executed and check it upon entry to the Sub. I suppose I could also put this code in a module and call it once from the form upon entry into this Sub FillSensorGrids()

I guess my question is: do these values persist after the Sub has ended. I am concerned about causing too much overhead. This test software will run continuously for days or weeks at a time.

Any thoughts?

Thanks,
Charlie


Public Sub FillSensorGrids()
        Dim i%, j%, a$
        '***********************************************************************
        '   Set up the connection, query, dataAdapter, datatable, CommandBuilder
        Dim conn As New OleDb.OleDbConnection(constr)
        Dim qry$
        qry$ = "SELECT * FROM [Inst_Check_Sensors_EMU]"
        Dim daFSGemu As New OleDb.OleDbDataAdapter(qry$, conn)
        Dim cbFSGemu As New OleDb.OleDbCommandBuilder(daFSGemu)
        Dim dtFSGemu As New DataTable
        '
        '***********************************************************************

        daFSGemu.Fill(dtFSGemu)

        If SensorTab.SelectedTab Is TabPageEMU Then
            'EMU Tab is Selected
            i% = 0
           
            For Each dr As DataRow In dtFSGemu.Rows
                dr.Item("Volts") = Format(AinElec!(i%), "####.0.00")
                dr.Item("Reading") = Format(AinPhys(i%, UnitsEMU%), "####0.00")
                i% = i% + 1
            Next

            DataGrid5.DataSource = dtFSGemu
            DataGrid5.Update()

            'update the Access Database Table 'Inst_Check_Sensors_EMU
            daFSGemu.Update(dtFSGemu)
            dtFSGemu.AcceptChanges()

        ElseIf SensorTab.SelectedTab Is TabPageISO Then
                    'ISO Tab is Selected
        End If
End Sub
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18801316
No, declared as they are, they won't persist after the sub has ended.  But they would persist if (a) you declared and instantiated them at application or form level or (b) you declared them as static within the sub.  I don't think the memory overhead would be any different in either case.  Obviously operational overhead will be reduced with either approach rather than declaring/instantiating anew every time.  And, if you do want to reduce overhead, you really only need this line

            DataGrid5.DataSource = dtFSGemu

once, provided dtFSGemu does persist one way or another.  And I'm not sure that this line

            DataGrid5.Update()

should really be necessary.  It should update automatically when any of the data to which it is bound changes.  But it's probably worth experimenting on that point.

One point I ought to add.  Despite its name, a DataAdapter's .Fill method actually refreshes the data.  That means it goes back to the database but only brings over fresh any rows which do not already exist in their database version in the datatable concerned.  If you want to make sure it really is a fresh "Fill", you ought to put

        dtFSGemu.Clear

just before

       daFSGemu.Fill(dtFSGemu)

It will depend on details I don't know about other aspects of your app, but that may be significant for your purpose.

Roger
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question