ADO problem

I'm trying to migrate from RDO to ADO, my database server is running Oracle8. I can connect ok and view field names but the recordset is not populated number of records is -1. RDO will show all the records. I've enclosed the code for both the RDO and ADO. Any help will be appreciated.

Thanks,
Craig Cederquist

'FOR ADO
Dim cn As New ADODB.Connection
Dim sql As String
Dim rc As Integer
Dim NumRecs As Integer

Const ConnString = "ODBC;UID=user;PWD=password;DSN=source"
sql = "SELECT * FROM OPERATION"
cn.ConnectionString = ConnString
cn.ConnectionTimeout = 10
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = "OPERATION"
cmd.CommandType = adCmdTable

Set rs = cmd.Execute(NumRecs)
'or rs.Open sql, cn


'FOR RDO

Dim Cn As rdoConnection
Dim En As rdoEnvironment
Dim eng As rdoEngine
Dim Conn As String
Dim Result As rdoResultset
Dim QSQL As String


Set eng = New rdoEngine
Set En = rdoEnvironments(0)

En.CursorDriver = rdUseOdbc
Set Cn = En.OpenConnection(source, rdDriverNoPrompt, False, "ODBC;UID=user;PWD=password;DSN=source")

QSQL = "Select * FROM OPERATION"
Set Result = Cn.OpenResultset(QSQL, rdOpenKeyset, rdConcurValues)

First of all, ADO returns 0 for RecordsAffected with a select statement. Second, because you are using set rs = cmd.execute, you cannot get a recordcount value. In your second example, you are not specifying a cursor type property, so ADO is using the default forwardonly type.

Use the second way and change the rs.Open Statement to:

rs.Open cmd ,,adOpenKeyset, adLockOptimistic

The adLockOptimistic part is necessary if you want to make changes to the table

Charlie Zimmerman
http://www.freevbcode.com

Thanks for the reply. I still have the same problem after making your proposed changes though. I get a run time error when doing a movefirst. I assumed it was because the recordcount property =-1, that there were no records. The source property shows the expected SQL "select * from OPERATION". Any other ideas?

Two ideas: 1) Does you table have a primary key defined? If not, this causes problems with keyset cursors.

2) Though I've never used Oracle, I've heard of people having problems with it an ADO (or at least when they try to use ADO like they are used to with other providers). I searched the MS Knowledge Base with the phrase "Oracle ADO" and came up with 80 results. It looked like there are a lot of informational articles on using Oracle with ADO, so you might want to check it out.

Also, if you find the solution, let me know as I am curious.

SUCCESS! Thanks for your help. The MS Knowledge Base had an Oracle ADO example. I changed my connect string to

Const ConnString = "UID=user;PWD=password;DRIVER={Microsoft ODBC for Oracle};SERVER=server;"

The rs was populated. The adkeyset cursor was ok.

Thanks,

Craig Cederquist
[email protected]

i think please check for the cursorlocation property of the connection object if this is set to "server side" which is by default it will always show you recordcount=-1. i think if this doesn't solve the problem then sorry

one more thing it is advisable to use recordset object rather than command.just write the following code:

dim rs as ADODB.recordset

rs.open "SqlQuery",.......rest you can know

and open your recordset.this will definetly solve your problem.