ADODB Recordset.Open occassionally raising Error 5 - w/ adOpenKeyset, adLockOptimistic

Daniel Wilson
Daniel Wilson used Ask the Experts™
on
Related question: http:/Q_24813268.html

In a VB6 application using ADODB, I have 1 point at which 1 customer sometimes gets Error 5,  Invalid procedure call or argument.

We have a function for calling Recordset.Open, and it's in the code hundreds of places and working fine.  Abridging very slightly, we have:

set rs = OpenDBConnection(sqlString, adOpenKeyset, adLockOptimistic)
which has ...

1000         Set ss = New Recordset
1020         ss.Open sqlString, gCn, CursorType, LockType

And in that one instance ... line 1020 sometimes raises Error 5

It's not an error assembling the sql, as that is assembled before the call to the function.  It doesn't appear to be an error in the SQL, as error 5 is a VB6 error, not a SQL Server error.

I can't have a Command object do the Execute for me here, as I need the recordset to be updateable. I know ... not the best data access model, but this has been working for a long time and is scheduled for a rewrite into .Net pretty soon, so an overhaul in VB6 doesn't appear worthwhile.

Has anyone else seen this before?

Any good ideas on resolving it?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brook BraswellApplication Development Manager

Commented:
In these cases I would have opened the RS as static rather than as keyset

Author

Commented:
Not if you have to write back to it.  I have to be able to write back to the DB in this case ... which you can't do with a Static recordset.

I use adOpenStatic, adLockReadOnly whenever I can -- but here I can't.

Other suggestions?
Hi

Try using adOpenDynamic cursor type, i have never had a problem, but then i very seldom deviate from adOpenDynamic and adLockOptimistic
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
OK, trying adOpenDynamic, adLockOptimistic

I will have to quit using RecordCount, but I can work around that.

I'll let you know how it goes ... will take a few hours to get it checked out.

thanks.
Brook BraswellApplication Development Manager

Commented:
Could you not just Get your data in this fashion and then just run an update sql using your connection?

Author

Commented:
>>Could you not just Get your data in this fashion and then just run an update sql using your connection?

Certainly ... if I'm willing to overhaul a 900-line function in a module that's going to be rewritten completely next year anyway.

I'm trying not to go there ...
The trick I've used for replacing RecordCount is to build your where clause in a seperate function.  Then after you've run your query to get the record set, run another "Select Count" query using the same where clause.  This way, if you every change the where clause, it gets changed in both places to keep your record set query and your count query in sync.  Of course this assumes that your record set will not grow/shrink while you have the records open.  But doing a "Select Count" seems to be a lot faster than doing a MoveLast/MoveFirst to get the count working.

Author

Commented:
adOpenDynamic seems to have solved it.

And, yes, I'm getting my recordcount using a separate Select Count() query.

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial