Solved

Problem updating a MS Access table from VB.Net 2003

Posted on 2007-03-27
5
257 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now