Updating database and using filewatcher

Cory Kimble

I am trying to fill a dataset and update a dataset to change values in a database. I have a third party program entering records in mydatabase. My program fills a dataset. I do some processes and update the dataset to update the mydatabase. All works well, until I factor in the fact that I want to watch the database and update a field when a record has a certain value so, I watch to see if any changes and Onchanged is called then I test the varables and if the record has a certain value I want to change a different value. But, then the Onchanged event is called again and I am in a endless loop of calling the method, changing the variable, calling the method, changing the variable. What would you suggest?

Naji El Kotob

Hi, I am not sure if I understand your request clearly, but I think you need a query notification, did you try the SQLDependency class, it helps in refreshing the cached data when the original database changed. for more details please check this url http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx[^] hope this helps :)

NajiCo http://www.InsideVB.NET[^] It's nice 2b important, but it's more important 2b nice...

Dave Kreskowiak

That won't work on an Access database.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

Dave Kreskowiak

There's no reliable way to do this in Access. Access doesn't support triggers, so your kind of screwed. You could use the FileSystemWatcher, but there's a problem with this. In order to get this to work, your Changed event would have to set a flag when it see's a change made and need to make its own change. This flag would tell the Changed event that it should ignore the event when it gets called again and then reset the flag.

Private Sub ChangeEvent(blah, blah) Handles ....
Static updatingDatabase As Boolean = False

' A change to the database has been detected.
' Check the flag to see if WE made the change, or if an outside
' source made the change.
If updatingDatabase Then
    ' We're making the change.  Reset the flag
    ' and do nothing.
    updatingDatabase = False
Else
    ' An outside source made the change.
    ' Set the flag and make our changes to the database.
    updatingDatabase = True
    ' Do you database update here.
End If

End Sub

Now, there is a large problem with this. If the outside source makes a change to the database while this code is running, you can't tell the different between your code making the change and the outside source making the change. It's entirely possible that the outside source changes the database and your code will never see it. There is simply no way to do this reliably in Access. SQL Server, even the FREE Express Edition, support Triggers, that will easily allow you to do this in the SQL code of your database.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007