Problem using ADO connection to Oracle server

[dinu2g]

Hi !

I have a big problem in reading data from a Oracle 8.i table using Ado
I have something like that (VB6):

Dim cnn as new adodb.connection
Dim rst as new adodb.recordset

cnn.open "Provider=MSDAORA.1;User ID=sys;Data Source=PROVA_192.168.3.22;Persist Security Info=False"

rst.open "Select ColOfStringType,ColOfDateType from OracleTable",cnn
if rst.eof then exit sub

msgbox rst.fields(0).value
msgbox rst.fields(1).value << - this line raise the error :&quot;Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done&quot;

Why I cannot READ data from a date type field?

Any sugestion ?

 

[dvannoy]

i dont work with Oracle but you can try

Dim cnn as adodb.connection
Dim rst as adodb.recordset

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

make sure you have your references set correctly

 

[JaxtheDog]

As Dvannoy said, you need to use the set commands. You also need a command object if you are bringing back a recordset.

Try this:

Dim cnn as adodb.connection
Dim rst as adodb.recordset
Dim ml_cmd As ADODB.Command ' command object

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

...your code to establish the connection remains here

If cnn.State = adStateOpen Then
Set ml_cmd.ActiveConnection = cnn
Else
'there is a problem with the connection
endif


ml_cmd.commandtext=&quot; &quot;Select ColOfStringType,ColOfDateType from OracleTable&quot;
set rst=ml_cmd.execute

if rst.eof then
if rst.close
set rst=nothing
NOTE: you need to clsoe the record set and set it to nohting at some point so that memory is freed befor eyou exit the sub. You cannot reuse this unles you do so.

exit sub
else
do the rest of you stuff here
and then close the recordset

 

[aata]

i'm connecting to oracle and everything is working fine with the following code. try it.

&quot;myservername&quot;, &quot;myusername&quot; and &quot;mypassword&quot; and &quot;mytable&quot; needs to be changed



Set conn = New adodb.Connection
Let conn.ConnectionString = &quot;Driver={Microsoft ODBC for Oracle};server=myservername;Uid=myusername;Pwd=mypassword&quot;
conn.Open
Set rs1 = New adodb.Recordset
rs1.Open &quot;select * from mytable&quot;, conn, adOpenDynamic, adLockOptimistic

msgbox rs1(&quot;fieldname1&quot

'use movefirst, movenext etc to step thru the records

rs1.close
set rs1=nothing
conn.close