I have tens of thousands of records on a SQL Server database, and I wish to create an interface so an end user can edit the records and move back and forth between them, very similarly to the interface in MS Access. The end user will have the software locally and it will access the data on a DB Server over the internet.
I’m not sure how to approach the situation. I tried to use the SqlDataAdapter and set the SelectCommand to return all the records, and then fill a dataset with the results and then bind each of my textboxes to a field in that dataset.
This works fine, however the time it takes first to initialise and display the first record, and then the time it takes to move back and forth between records is unacceptable even over a 256k link.
I then tried to manually generate a SQL statement to return the desired record when and where it is needed (i.e., move to next or last record). This gives a much better response time, however when I fill my dataset with the record that the SQL statement returns, I the data bindings don’t refresh.
I have checked and the correct data is in the dataset but it seems that the bindings aren’t picking up the change and updating the values in the textboxes. I have tried to refresh the bindings using the CurrencyManager but it still does not work. Is it to do with the Fill method that I'm using with the adapter?
I was wondering if there is another way of approaching the problem, or perhaps there is something I was doing wrong in my 2 attempts. Either way is acceptable, but if I use the later one, how could I make the bound textboxes reflect the changes in the dataset?
Your help will be much appreciated.