Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rbarlow
Starting Member
26 Posts |
 Posted - 2007-05-22 : 21:22:13
|
| Hey,I was wondering what is the best way to update records if an ADO recordset is used to access the data. I've been told that I should use stored procedures rather than the Update method of the recordset. A problem that I've run into using stored procedures is once I have updated the data, I need to requery the recordset in order for the changes to be reflected. If I don't then once the user moves to the next record and moves back again, the changes will be gone. The front end application and database is also separated by a slow internet connection so I also want to limit the traffic going back and forth for performance reasons.Any thoughts/comments are appreciated! |
|
|
Dallr
Yak Posting Veteran
87 Posts |
Posted - 2007-05-23 : 07:54:24
|
| Hum, That seems strange. NOt sure if I can help but I would suggest you post you ADO code.Dallr |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-23 : 08:04:02
|
| Well, if you are inclined to use Recordset's update method, I will suggest you should rather go for UpdateBatch method which is more efficient performance wise and results in less network round-trips.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
rbarlow
Starting Member
26 Posts |
Posted - 2007-05-23 : 12:24:15
|
| After looking this over again after a good nights sleep... I've realized that I'm using two different connections to the database. The first to query the records into a recordset (using a stored proc) which is then used by the user to navigate back and forth through the records (using MoveNext/MovePrevious). The fields are not data binded because of the bad network connectivity so the data is loaded manually from the recordset. The second one is created and used to call a stored proc to update the record when the user does edits and presses the save button. So once the updates are complete, when you move to a different record and move back again, the changes are not shown because its still loading the data from the original recordset.The way I got around this problem was requerying the entire original recordset after calling the update proc and navigating to the current record using FindFirst but I want to try to avoid that because we're trying to limit the traffic going back and forth.Sorry I didn't say this in my original post.. it was the end of the day and my brain was fried!!So now I'm still left with how to solve this problem. I read last night that if a dynamic cursor for the recordset is used then it would include any changes to the data that other users have done but that didn't work. |
|
|
|
|
|
|
ADO Recordset Update