ADO 2.5 with Oracle 8i

When I was working with same code with Oracle 7.3 instance, there was absolutely no problem, but when
I have connected to Oracle 8i, this is not returning any rows, though it used to return when its connected
to Oracle 7. All the schema and data is duplicated in Oracle 8i instance.

The code is like this:

Private Sub Command1_Click()
Dim adoConn As ADODB.Connection
Dim adoRecSet As New ADODB.Recordset

Set adoConn = New ADODB.Connection

adoConn.ConnectionString = "Provider=MSDAORA;Data Source=ftdev11_dev8.world;User ID=apacapa; Password=apa;"
adoConn.Open

Form1.Cls

If adoConn.State = adStateOpen Then
Form1.Print "Connection open"
Else
Form1.Print "Connection Failed"
End If

adoRecSet.CursorLocation = adUseClient
adoRecSet.CursorType = adOpenForwardOnly
adoRecSet.LockType = adLockReadOnly

Set adoRecSet = adoConn.Execute("Select SDN_SDN_ID from SMS_NEW_DNIS where SDN_SAC_SEQ_ID = 94351 FOR
UPDATE NOWAIT")

Do While Not adoRecSet.EOF
Debug.Print adoRecSet(0)
adoRecSet.MoveNext
Loop

MsgBox "# of records " & adoRecSet.RecordCount

End Sub

1.The connection is open, after clicking the command button. But its not returning any records when
I am using "FOR UPDATE NOWAIT" clause in the select stmt. The same worked well with Oracle 7.

2. When I remove that clause in the SELECT statement, its retuning relevant records, but still showing
the recordcount = -1.

Please tell me what I should do to make the SELECT statement work with "FOR UPDATE NOWAIT" clause.

Thank you in advance for any suggestions.

Yamini
[email protected]

Yamini

Not every recordset supports RecordCount property. That is why when you are getting recordcount = -1, it might be OK. If you need recordcount, use SQL statement.
About clause FOR UPDATE NOWAIT. I never used it. May be it is not supported in Oracle 8i

Iouri Boutchkine
[email protected]

Thank you for your response.

Locking is supported in Oracle 8i. I have learned that thru Oracle TechNet Documentation. Oracle8i supports the locking the same way it did in Oracle 7.x.

This is just for your information. If you or anybody wants to try this code, the platform I am working on is:

VB 5.0 SP3
ADO 2.5
Oracle 8i

Thank you

Yamini

The adOpenForwardOnly cursor type does not support RecordCount, that is why you got RecordCount=-1. Try to set it to adOpenStatic.


Regards,

Michi
MCSE, MCDBA

Hi Michi:

Thank you for your response.

I have even tried adOpenStatic, no change. Anyway, I am not really bothered abour record count. What I really want is, I should be able to retrieve the records.

Any help will be highly appreciated.

Again, the platform I am working on is:
VB 5.0 SP3
ADo 2.5
Oracle 8i

Thank you.

Yamini