ADODB problem 2

[BarryCrosby]

All,
I've got a problem with a piece of code accessing an oracle database.

I've used the same piece of code to access other tables and it returns data successfully but when I try the following it errors saying:

Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record

Code snippet
rs.Open "INCIDENT", oconn, adOpenDynamic, adLockOptimistic, adCmdTable
Set rs = New ADODB.Recordset
rs.Open "SELECT INCIDENT.INCIDENT_ID FROM SA.INCIDENT INCIDENT WHERE INCIDENT.INCIDENT_REF > 303750", db, adOpenDynamic, adLockBatchOptimistic

Do While Not rs.EOF
MsgBox ("looping")
row = ""
For icount = 0 To rs.Fields.Count - 1
row = row & rs.Fields(icount).Value
Debug.Print rs.Fields(0).Value
Next icount
txt = txt & Mid$(row, 3) & vbCrLf
rs.MoveNext
Loop
rs.MoveLast
rs.Close

It does not even display the Msgbox ("looping").

Any ideas?

Thanks
Barry

 

[BarryCrosby]

Sorry I forgot to add if I paste the code in SQLPlus it retrieves all the INCIDENT_ID's successfully.

Thanks,
Barry

 

[neilharris]

In the first open method you use the connection oconn, in the second open method you use the connection db.

Are these both valid for connecting to the oracle database?

 

[BarryCrosby]

Here's the code:

Set db = New ADODB.Connection
With dlg_incref
If dBase <> "" Then
Conn = "UID= " & uid & ";PWD=" & pwd & ";DRIVER={Microsoft ODBC For Oracle};" _
& "SERVER=" & dBase & ";"
Else
Conn = "UID= " & uid & ";PWD=" & pwd & ";DRIVER={Microsoft ODBC For Oracle};"
End If
End With

I pass conn to the function as oconn. I've tried replacing db with oconn but get the same error. any ideas?

 

[BeginnerProgrammer]

Might try making sure you do a rs.movefirst before the Loop.

Hope this helps.

 

[dvannoy]

try this

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
etc...

or

rs.MoveFirst
Do While Not rs.EOF



also you are telling it to move last...

use
rs.movenext instead of rs.movelast

 

[dvannoy]

sorry..disregard the rs.movenext...you are doing that..

sorry

 

[JaxtheDog]

Barry,
I have had it happen that I could run an Oracle SQL using SQLPlus and not have it work with VB because the connection did not work correctly. I notice that you are using a dynamic cursor which may not be supported by your driver --generally Oracle gives you a view, not a dynamic recordset when using the microsoft providor.

Try creating your connection and then do each step explicitly. For example:

I use the following for my connection (similar to yours, which you can substitute)

Global conn As ADODB.Connection
Dim ml_cmd As ADODB.Command

(use your passwords, and ORacle database name, etc here)
conn.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=" & sDataSource & ";" & _
"user ID=" & sUserID & ";" & _
"password=" & sPassword


Set ml_cmd = New ADODB.Command
' check if conn is still connected to network
If conn.State = adStateOpen Then
Set ml_cmd.ActiveConnection = conn
else
msgbox "Connection problem"
end if

Set rs= New ADODB.Recordset
With rs
.CursorLocation = adUseClient ' default
.CursorType = adOpenForwardOnly ' default, readonly
End With

ml_cmd.commandtext="SELECT INCIDENT.INCIDENT_ID FROM SA.INCIDENT INCIDENT WHERE INCIDENT.INCIDENT_REF > 303750"

set rs=ml_cmd.execute

Then test rs to see if it is open, etc.

if rs.eof=true or rs.bof=true then
msgbox "record set empty"
else
do while not rs.eof
...do the rest of your code.


' if you get here and still raise the error, let me know and I'll take another look.

Regards,
Jax

 

[SonOfEmidec1100]

My comments are based on generalised ADODB access, not Oracle specific which I know nothing about. Anyway a couple of points that may help;

I dont understand in your code snippet the first rs.open seems to serve no purpose you immediatley re-create the recordset object.

You will get the error on the rs.movelast with an empty recordset. This implies the second query returns no rows, which is probably the case as you dont get the message box. Anyway you dont need the rs.movelast as the recordset is closed immediately afterwards.

 

[BarryCrosby]

All,
Thanks for the helpful posts.

I actually found that by chaning the SQL to be:

SELECT * FROM SA.INCIDENT INCIDENT WHERE INCIDENT.INCIDENT_REF = 323455

And then used:

Do While Not rs.EOF
row = ""
For icount = 0 To rs.Fields.Count - 1
row = row & rs.Fields(icount).Value
strIncID = rs.Fields("INCIDENT_ID").Value
Next icount
txt = txt & Mid$(row, 3) & vbCrLf
rs.MoveNext
Loop

This did the trick. I have got a feeling it may be that the INCIDENT table may return too many fields for the recordset to handle. Is this possible?

 

[Polariz666]

Could it be that the error in your original code is due to no records being found? If this is the case, the code will skip the While statement because rs.EOF is true (Skipping the Msgbox), then it will consequently crash on the rs.MoveLast statement because it's not included in any sort of protection loop such as the while loop previsouly used?

It would seem to suggest by your previous post that the change to the SQL statement allowed some records into the recordset. To protect the whole thing i'd use an If statement around the section to protect the program ferom crash:

--------------------
Do While Not rs.EOF
MsgBox ("looping")
row = ""
For icount = 0 To rs.Fields.Count - 1
row = row & rs.Fields(icount).Value
Debug.Print rs.Fields(0).Value
Next icount
txt = txt & Mid$(row, 3) & vbCrLf
rs.MoveNext
Loop
If Not rs.EOF Then
rs.MoveLast
End If

rs.Close
--------------------

Hope this is of help,

Mike.

 

[BarryCrosby]

Thanks for all the advice. Looks like my code need s bit tidying up.

Cheers,
B