Unknown error in VB when using SQL 7

[AndyHicks]

I have a VB 6 application that uses ADO to read and write data from SQL 7.

Every know and then I get the following error number raised when read data from SQL 7:

Error No. : -2147467000.
Error Description : Unspecified error

The types of select statement that generates this error is "Select * from [Calculations] Where [CaseId] = 3229"

The really funny thing is that when I take the same SQL select statement and run it from Query Analyzer it works fine!

Any help/pointers would be gratefully received.

Andy

 

[Targol]

I you replace the query by anotherone (SELECT id FROM AnotherTable), does the error still occurs ? Water is not bad as long as it stays out human body ;-)

 

[AndyHicks]

Hi Targol,

If you mean does the error occur again in the application, then the answer is no.

You can try the same piece of code and the error doesn't occur. Then after a period of time, the error occurs again.

Very weird!!

Cheers
Andy

 

[Targol]

Then, I think it's due to connections problems, watch theses points :
1) did you open your recordset with the rights params ?
2) did you close well all your recordsets before this one ?
Water is not bad as long as it stays out human body ;-)

 

[AndyHicks]

Hi Targol,

I have a standard class library that is responsible for opening, reading, writing, etc data from the SQL 7 database.

So basically the code does the following:

Creates a ADODB.Connection object
Creates a ADODB.RecordSet object

Sets the RecordSet object properties to
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = <select statement required>

Sets the .ActiveConnection property to the ADODB.Connection object created above

Then calls the .Open method

Sets the .ActiveConnection property to Nothing

Closes the Connection object via the .Close method

The sets the Connection object to Nothing.

Passes the RecordSet object back to a calling routine.

The parameters for these routine are:
Public Function SetTable(ByVal p_cTable As String,
Optional ByVal p_cSelectClause As String, _
Optional ByVal p_cWhereClause As String, _
Optional ByVal p_cOrderBy As String, _
Optional ByRef p_bSuccessFailure As Boolean, _
Optional ByVal p_bJoinOnTables As Boolean) _
As ADODB.Recordset

Can you see anything in what I've described that would case itermitant problems?

----

As I said earlier, this works fine and then bang you get the system error.

The other thing is that I've never seen the problem happen on the machines used in the office that I work at.

 

[agsuresh]

Please Check the Connection TimeOut parameter
More Over If I remenber right this could be a bug with ado
check MSDN for an answer.

 

[AndyHicks]

Hi Agsuresh

Thanks for the suggestion, however, I would have thought that a timeout error would raise an Err.Number of -2147217871?

I've already added code for this in the data handler.

I've also had a look on TechNet and MSDN and can't see any TimeOut bugs, apart from the ones that mention Err object returning a numner of -2147217871.

If you think of anything else, please let me know.

Cheers
Andy