Updating a DataSet from a Database
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
DisIsHoody wrote:
load the records into a dataset
Yeah, don't do that. Redesign your application; do it the right way.
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
DisIsHoody wrote:
database contains >10,000 records
DisIsHoody wrote:
Previously we were using Access as the front end
I suspect your database design is a complete disaster, probably evolved over a number of years by a power user (possibly yourself) and now you want to use it seriously. I make the STRONG suggestion to have a professional look at it. Your database probably needs a complete redesign, and your existing data migrated to the new structure. PLEASE do not do this yourself, get a professional otherwise you will be back here in a year or two needing to do it all over again.
Never underestimate the power of human stupidity RAH
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
I know the pain of migrating an application from MS-Access to SQL server ... I'm in the middle of it now. You might want to take a strategy like this ... You have a grid full of records that a user may update. Each record should have two additional fields: modified_datetime,modified_by Create a button (or something) which puts the record into "edit" mode; at this time, re-fetch the record so that you are sure you have the most current version. During the "save" of the record, you will need to re-fetch the record and check that the modified_datetime has not changed, if no change then update the record with your new values and updated modified_datetime,modified_by values. If someone has modified the record, you can inform your user that someone has already modified the record and he/she has to "re-edit" the record. Something to think about. I also agree with the other members that you might want to take this time to re-design your database design and not do a straight Ms-Access to MS-SQL import.
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
Not a very good Problem you are experiencing. I once came across the Problem , but mine was different , i was having Concurrency issues with 50 000 users. Luckily the System was written in a manner that another use must not See other user changes when he is busy until he is done and Committed the Data. The Problem here is that a Dataset is ADO.NET Feature and it means you connect to the database and take Data and disconnect , and when you want to do some updates, you reconnect and do the Update and disconnect again. So it will be a bit Difficult to Update the Data on other machines unless they query again. This is more difficult in Windows Applications. This is what i like about CodeProject, People Come with Real Problems , i think this will lead me to write an Article about this, Hope to write it soon. i will see what i can do tonight. Good Luck
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
Believe me...I know the database needs a whole re-write. It was originally designed by the president of the company when the formed the business. We have been trying to get him to hire a professional to completely re-write it, however from his perspective it works right now so why should we change it. So unfortunately I'm stuck working with the database in it's current format. As for the idea of having a column to monitor the data/time the record was last updated...I think this will work pretty good for the time being. Thanks for the idea. I appreciate all the responses. Dominick
-
I'm looking into creating a custom database application that will access our companies database which is located on a SQL server. Previously we were using Access as the front end, however we are going to be implementing new 3rd party software that will our servers will need to communicate with, however to try and program this from access was going to be difficult. So now on to my question. When my program load, it runs a query against the database to load the records into a dataset. I have no problem with loading records, updating records, or creating records. However I do have a problem if Person #1 is using my software and Person #2 updates/changes a record in the database after Person #1 has loaded my software. Person #1 will not see the changes Person #2 made unless they restart the program or I force the software to reload the entire database. However, I have no way of knowing if someone made changes to a record in order to update the dataset. Is there a way to have my software automatically detect changes to a record and automatically update the dataset? Or will I have to have the program update the dataset every few minutes? The problem is that the database contains >10,000 records and to have it update every few minutes could severely slow down the program, especially if someone is working over VPN. Any ideas would be greatly appreciated. BTW I am working in VB.NET 3.5.
Sounds to me like the program should be re-written, not the database. Of course this may be a simplistic accessment because I am not familiar with your problem domain, but, why would you have the program always load the entire database rather than just the record currently being worked on? Most progams that work with a database have some sort of search function to allow a user to find the record they need (returning only minimal identifying data), then a detail form/page that is used to work on that record. This way you are never loading much more data than you need and since the record is getting loaded just before working on it and saved immediately after, it should always be relatively current. The only concurrancy problem you have then is if two users are working on the same record at once. Hope this helps.
Kevin Rucker, Application Programmer QSS Group, Inc. United States Coast Guard OSC Kevin.D.Rucker@uscg.mil "Programming is an art form that fights back." -- Chad Hower