Randomly getting -2147467259 error when running a query on an Oracle dB

[fenwah]

The entire error is as follows:

Run-Time Error '-2147467259 (80004005)
Method 'RecordCount' of Object' _ Recordset' failed

I only get this error when I do a SELECT *

If I do a SELECT and certain fields out of the DB, it runs fine. Here's my code...anybody come across this before??


Public Function dbConnect() As Boolean

'Dim Cn As New ADODB.Connection
Dim Cn As New ADODB.Connection
Dim CP As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim TempSQL As String
Dim A As String

'Connect to Oracele server begin
Conn = "DRIVER={ORACLE ODBC DRIVER};SERVER=9z_pmsd;UID=fennigbr;PWD=xxxxxxx;DBQ=9z_pmsd;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LO B=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"

Set Cn = New ADODB.Connection

With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With

If Cn.State = adStateOpen Then
MsgBox "Connection successful."
End If

TempSQL$ = "SELECT * FROM PMS.TPMS05_DD350 WHERE Piin_Spiin_ID = 'F3060202C0147'"
Set Rs = New ADODB.Recordset 'new
Rs.CursorLocation = adUseClient
Rs.Open TempSQL$, Cn, adOpenDynamic

If Rs.RecordCount > 0 Then
Rs.MoveFirst

Do

A$ = Rs("f3_phone_nm")
Debug.Print A$


Rs.MoveNext

Loop Until Rs.EOF = True
End If

Rs.Close
Set Rs = Nothing


End Function

[riis]

The RecordCount property on a recordset isn't reliable imho.
In your case you can use the following check, instead of "If Rs.RecordCount > 0 Then"

  If Not (Rs.BOF And Rs.EOF) Then

When both BOF and EOF are true, then you'll have an empty recordset, so you shouldn't proceed then.

[riis]

Oh, and if you really need the number of records on beforehand, then you could use the following SQL statement:

SELECT Count(0) AS NumRecs FROM Table

RecordCount = Rs("NumRecs").
This query should always return a value, else something is completely wrong (Or perhaps the table just doesn't exist)

[fenwah]

This is where it gets kinda weird. I have SQL*PLUS up and running. I am running the following SQL statement in SQL*PLUS:

SELECT * FROM PMS.TPMS05_DD350 WHERE Piin_Spiin_ID = 'F3060202C0147';

It correctly returns the record I am looking for.

Now if I do the following SQL in VB,

TempSQL$ = "SELECT * FROM PMS.TPMS05_DD350" ' WHERE Piin_Spiin_ID = 'F3060202C0147'"

I get TRUE for rs.EOF and rs.BOF. **BUT, I no longer get that Run-Time Error. It just says that it's not in the database and closes it out.

Now, if I run the following in VB, it will return the correct value:

TempSQL$ = "SELECT Piin_Spiin_ID FROM PMS.TPMS05_DD350" ' WHERE Piin_Spiin_ID = 'F3060202C0147'"



Weird, eh?