Cursor location in ADO -- large recordset

[jenlion]

I'm having a big problem with a program I wrote for a law firm. This program (VB6 SP5) accesses a SQL Server 2000 database. Uses ADO control to create a connection. Perhaps this was my first mistake... should have put it all in code rather than using the control ... but the program started out simple enough.

The connection with ADO is successful. Data is displayed mostly through bound text boxes which they may edit. Problem is that in order to allow the Find command and easy adding, I kept the cursor on the Client. Now that they have more than 20,000 records, and 20 people entering data at the same time (instead of 3 or 4), this does not work.

I've tried different types (dynamic, keyset) or cursors with no real changes in behavior.

I think that I need to move the cursor back to the server. Trouble is that if I do that I get the error "rowset does not support scrolling backward", or fetching backward, and will not let me do a simple Find. (no, I can't replace with a select -- they want to move to a particular record but be able to scroll around that record). Also gives this error when we movelast. Not sure if this is intrinsic to server location of cursor or if it's because of other things added to my code -- the requirements have changed so often on this !$(@# project that I'm thinking about starting over!!

What's the best way to allow for massive data entry, multiple views, edits, deletions, additions, etc???

Thanks for help. This project got bigger than me in a hurry.

 

[SemperFiDownUnda]

If you have massive quantities of data move the cursor to the server. A ServerSide Dynamic LockOptimistic cursor will let you scroll forward and backward. Find will not work but you can impliment that yourself but it can be expensive resource wise reading each record.

Sounds like you need a mixture of solutions.
What is the business case for them doing a find to one customer record then moving manually up or down one customer?

Remember you cann't have your cake and eat it to without baking lots of cakes in the back ground.

For me I generally try to find ways to give them views of data but really from a editing perspective I don't see why they would say "Ok I need to edit Joe Blog's information and the next customer after Joe Blog's but I'm not sure who that customer is"

If they want browsing and you have lots of people editing data then you'll need to set up some manual paging system. Its not something that you should just bandaid ontop of your system though.

 

[jenlion]

Thanks for the reply. The business case is this: If they find that one person did some sloppy data entry in the past, they want to check all the records added at about the same time, just in case they were sloppy with other entry as well. All the records close to each other in the recordset tend to have been added at the same time. Unfortunately they didn't have logins or anything that identified who was doing the original data entry.

I'm considering a "data entry" mode in which users would enter new information into a temporary table, then commit all new records at one time, and a "view/edit" mode that would keep things like they are, allowing finds, searches, scrolls, and moving to certain places in the recordset. I think that that might work. It's another "cake" ... only problem there is that they wouldn't be able to find/edit records being added currently, but they might just have to live with that. The current wait time to refresh every time they add a record is just becoming too long. Not sure how I would implement such a table, since I'm going to plan for up to 30 concurrent data-entry people, but I'm thinking about it. I suspect a couple of stored procedures would work; I might store the fields and then simple link a command button that would verify data and then run a stored procedure to dump the data. Would speed things up tremendously.

Manual paging system? What do you mean? They're paying well for me to give them exactly what they want, so if it's within my means, I'll figure it out.

I appreciate the input. Helps to know that I'm not missing something obvious! Their requests seem reasonable most of the time, but they keep changing so much that I'm having a hard time keeping up.

Jennifer