Solved

Problem updating a MS Access table from VB.Net 2003

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

19 Experts available now in Live!

Get 1:1 Help Now