What is the best practice when using vb6/ado/ms sql server stored procedures to retrieve updatable recordsets that can be filtered and sorted?

0 pts.
Tags:
SQL
Visual Basic
I am having trouble implementing ado filter and sort on a recordset returned from a stored procedure. The following 2 lines are the same for both adUseClient and adUseServer. prsCommissions.CursorType = adOpenStatic prsCommissions.LockType = adLockOptimistic I found that if I use a client side record set like this: "prsCommissions.CursorLocation = adUseClient" that the recordset does not update, but does sort and filter. If I use a server side recordset like this: "prsCommissions.CursorLocation = adUseServer" that the recordset updates, but does not sort or filter. What is the best practice when using stored procedures to retrieve updatable recordsets that can be filtered and sorted? Thanks in advance!
ASKED: July 28, 2005  1:45 PM
UPDATED: July 28, 2005  2:43 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Congratulations! You are about to advance to the next level of understanding. You have stumbled into an area that you need to explore in depth, especially if you are planning on programming for many years to come. A proper explanation of cursor locations would take many pages to explain the implications. Here is a short answer:

adUseServer requires a round trip to the SQL server each time you move to the next row. Look at this in SQL profiler and you will see lots of activity or what some would call *overhead*. You are prevented from re-sorting or filtering since the cursor is managed by the SQL server.

In comparison, when reading large datasets you will notice tremendous speed advantages with adUseClient. With adUseClient the server can stream the result set to you in big chunks then move on. Because your recordset and cursor are now on the client you can sort and filter quickly using the clients cpu and ram.

The final answer also depends on how many concurrent users will be accesing the sorted/filtered dataset. And how you present your data for updates to the user.

I’d recommend that you dive into CursorTypes and see if you cant find an answer there. If you have good primary keys in the recordest you should be able to update rows. Even after they are sorted.

Understanding these settings in ADO is very important to your success as a programmer. Dive in and good luck.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following