ADO Recordset problem

I am having trouble returning data from an Oracle table...

I can get data with the following:

Code:

---

Option Explicit
Public adoDWConnection As ADODB.Connection
Public adoDWRecordset As ADODB.Recordset

Private Sub Command1_Click()
    OpenDWConnection
    MsgBox GetBalance(Me.txtAccount)
End Sub

Sub OpenDWConnection()
    Dim strConnection As String
   
    Set adoDWConnection = New ADODB.Connection
   
    strConnection = "Provider=MSDASQL.1;Data Source=DATA_WAREHOUSE"
    adoDWConnection.Open strConnection, "MyLoginID", "MyPassword"
   
End Sub
Function GetBalance(strAcctNbr As String) As String

    Set adoDWRecordset = New ADODB.Recordset
   
    Dim strSQL As String
   
    strSQL = "SELECT acct_status_id from adhoc_view.curr_acct_status where acct_nbr = '" & strAcctNbr & "'"
    adoDWRecordset.Open strSQL, adoDWConnection, adOpenDynamic
       
    GetBalance = adoDWRecordset![acct_status_id]
   
    adoDWRecordset.Close
End Function

---

But I can NOT get data back if I change my SQL to

Code:

---

select acct_nbr, bill_amt from adhoc_view.curr_bill where acct_nbr = '" & strAcctNbr & "'"

---

and change the return to

Code:

---

GetBalance = adoDWRecordset![bill_amt]

---

What I get is error number 3021: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

The SQL returns a valid record if I run it thru TOAD, so I know it works.

Why would this table not return results?

Thanks!

1)
select acct_nbr, bill_amt from adhoc_view.curr_bill where acct_nbr = '" & strAcctNbr & "'"

I'm assuming that acct_nbr is a number you DON'T need the single quotes (') around the value. If it is a string then its perfectly ok - see (3) below


2)
You did no checks to confirm you have a record to return.

VB Code:

if adoRst.state=adstateopen then
  if Not adoRst.eof then
   GetBalance = adoDWRecordset("acct_status_id")
  end if
[i] adoRst.Close[/i]
End if

First I check to make sure the recordset is open,
then I check for records (EOF - End of file = no records if its set on open of a recordset)
I changed the Bang (!) to a more nicer way of reading code - probably around the same amount of time to process it...
Also note that I closed the recordset after I've finished with it.

3)
So assuming that a recordset is opening, it means that account number doesn't exist

Vince

1) It's a string...(Stupid billing system)
2) .State = 1
.eof = true
.bof = true
.recordcount = -1
3) Account was valid, as TOAD gave me a return (As did SQL Plus)

I tried all your suggestions, but none worked.

What DID work is this:
http://www.able-consulting.com/ADO_C...riverForOracle

That's right, I used the Connections page from your Signature line and now it works!!

Thanks for the help!