Solved

Problem updating a MS Access table from VB.Net 2003

Posted on 2007-03-27
5
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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