Solved

ADO and RDO  problems

Posted on 1998-12-01
10
1,192 Views
Last Modified: 2013-12-25
1. With Oracle as the back end database,I have not been able to use the MovePrevious/Page Functions/Move  function with ADO . Is there any way out so that Move Prev/Move/Page function can be used with ADO on Oracle also.

2. RDO is the other option. But there is another problem.
In the object browser of VB, although the application and the class shown as RDO.rdoconnection  , the class registered in the Windows 95 registry is "MicrosoftRDO.RdoConnection".
With Dim var As class_name syntax both works. But with Set Var name=  CreateObject("application_name.class_name) , only the class "MIcrosoftRDO.RdoConnection" works. Is there any suggestion what is the reason? This is happening on a Windows 95 machine.

3. The same application is not running on WinNT 4.0 ,Service Pack1a.
It has VB5.0 installed in it.
But neither of these statement work there.
Dim Conn As New RDO.rdoConnection /
Dim Conn As New MicrosoftRDO.RdoConnection /
Set Conn = CreateObject("RDO.rdoConnection")  /
Set Conn = CreateObject("RDO.rdoConnection").

In WinNT , I have been able to use the Remote data control only. But the remote data object cannot be used. Another observation is that, in the registry of WinNT, the classe registered is only  "MicrosoftRDO.RdoConnection2.0". So it is expected that the above statements won't work. But CreateObject with 2.0 class also does not work.

Any suggestion will be highly appreciated.


0
Comment
Question by:puna
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 4

Expert Comment

by:tomook
Comment Utility
1) By default, you are getting a forward-only cursor. Some server-side cursor drivers do not support backwards scrollable cursors. We tend to use client-side cursors to get around this problem. My DBAs tell me that there is a setting in Oracle 7.3 and higher to allow backward scrolling cursors, but that is all I know about that. We have never successfully created a server-side backwards scrollable cursor with Oracle.

2) I encourage you to use Set X = New RDO.rdoConnection rather than CreateObject. This is happening because the ProgID is different from the type library name. RDO is the type library name, and MicrosoftRDO is the ProgID (technically, MicrosoftRDO.rdoConnection is the ProgID). Essentially, the ProgID is what is put in the registry under HKEY_CLASSES_ROOT. The type library name is actually stamped on the type library. When declaring variables, VB uses the type library name. Smart programmers make the both the ProgID and type library names the same. No further comment on Microsoft, except to say this is not the first time they have done this. (See the VB Extensibility model) Using the Set X = New ... avoids the ProgID problems.

Additional Note: because of some bugs with VB, do not use Dim X As New. Separate the Dim and New.

3) It sounds like RDO is not registered properly.
0
 

Author Comment

by:puna
Comment Utility
Thanks for your valuable suggestions.

1. The backward scrooling as you suggested , should depend on whether Oracle supports backward cursor.But usually, this ADO or other data access method, puts the data in some temporary buffer/array, where they control the forward or backward scrolling. In that case, itr should not depend whether the datbase support backward scrolling.
Do you give some suggestion how to use Client side cursor. Probably RDO use client side cursor.Can we use client side cursor with ADO?

2. Thanks for the ProgId, Type library clarification. In CreateObject, probably we have to mention the ProgID instead of TypeLib. We are using all these from Active server pages which supports VBscript. There we have to use CreateObject method to create an object dynamically.

3. I too feel, it might be registration problem. But I have reinstalled VB no. of times so that it registers all RDo dll.
I have also tried to register using Regsvr32. In that case is it sufficient to register msrdo20.dll only or we need to register some more supporting dlls. In the NT machine Remote Data Control is working fine.




0
 

Expert Comment

by:GeoffS
Comment Utility
We use VB50 RDO to access SYBASE.
Below is a likeness of a function we use to create a new rdo connection. Its certainly not complete (no error checking) but I think it addresses most of the key attributes of the rdo connection. The strODBC variables are loaded (in our case) from registry settings.

hth - Geoff


Calling Syntax:
DIM cnMyConnect as rdoConnection
Set cnMyConnect = RDOConnector(rdUseServer)




Public Function RDOConnector(Optional intCursorDriver As Variant) As rdoConnection

Dim cn As rdoConnection
Dim sConnect As String

If IsMissing(intCursorDriver) Then
    intCursorDriver = rdUseOdbc
End If

sConnect = "DSN=" & strODBC_DSN & ";"
sConnect = "Driver=" & strODBC_DRIVER & ";"
sConnect = sConnect & "Server=" & strODBC_SERVER & ";"
sConnect = sConnect & "UID=" & strODBC_USER & ";"
sConnect = sConnect & "PWD=" & strODBC_PSWD & ";"
sConnect = sConnect & "Database=" & strODBC_DATABASE & ";"

Set cn = NEW rdoConnection
cn.Connect = sConnect
cn.CursorDriver = intCursorDriver
On Error GoTo NoConnection
cn.EstablishConnection rdDriverNoPrompt, , rdAsyncEnable
Set RDOConnector = cn

End Function

0
 

Expert Comment

by:GeoffS
Comment Utility
We use VB50 RDO to access SYBASE.
Below is a likeness of a function we use to create a new rdo connection. Its certainly not complete (no error checking) but I think it addresses most of the key attributes of the rdo connection. The strODBC variables are loaded (in our case) from registry settings.

hth - Geoff


Calling Syntax:
DIM cnMyConnect as rdoConnection
Set cnMyConnect = RDOConnector(rdUseServer)




Public Function RDOConnector(Optional intCursorDriver As Variant) As rdoConnection

Dim cn As rdoConnection
Dim sConnect As String

If IsMissing(intCursorDriver) Then
    intCursorDriver = rdUseOdbc
End If

sConnect = "DSN=" & strODBC_DSN & ";"
sConnect = "Driver=" & strODBC_DRIVER & ";"
sConnect = sConnect & "Server=" & strODBC_SERVER & ";"
sConnect = sConnect & "UID=" & strODBC_USER & ";"
sConnect = sConnect & "PWD=" & strODBC_PSWD & ";"
sConnect = sConnect & "Database=" & strODBC_DATABASE & ";"

Set cn = NEW rdoConnection
cn.Connect = sConnect
cn.CursorDriver = intCursorDriver
On Error GoTo NoConnection
cn.EstablishConnection rdDriverNoPrompt, , rdAsyncEnable
Set RDOConnector = cn

End Function

0
 
LVL 4

Expert Comment

by:tomook
Comment Utility
1. To use client-side cursors with ADO, set the CursorLocation property of a Connection = adUseClient

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:puna
Comment Utility
Thanks for all you feedback.

1. I haven't found any CursorLocation property of Connection object in ADO1.0.
Could you please elaborate?

2. Thanks for the Code samples. But I have to instantiate using CreateObject, since the Active Server Page VbScript does not support Dim Var As Var_type object creation.
I have been able to do the same with CreateObject but  on Win NT it is stll creating the problem. Is there any way(except installing VB , which is not solving the problem) to register the RDO dll/dllls.

3. Do you have any idea, how we can use CreateObject method for Data access using DAO?
Is there any restriction/limitation in the use CreateObject method compared to "Dim As New Class " object creation. Is it that, for only few classes we can instantiate using CreateObject method.
 
0
 

Expert Comment

by:rurajase
Comment Utility
Regarding ur question about Cursors, I had a similar problem.Now, I dont think ADO2.0 allows u to use adClientCursor.It gives a nasty error like "Insufficient base table information for updating or refreshing".Here is the code I implemented....

Private sub Form1_Load()

      Dim con As ADODB.Connection
      Dim com As ADODB.Command
      Dim res As ADODB.Recordset
      Dim par As ADODB.Parameter


      Set con = New ADODB.Connection
      Set com = New ADODB.Command
      Set res = New ADODB.Recordset


      con.ConnectionString = "dsn=amfm;usid=amfm_central;pwd=**;"
      con.CursorLocation = adUseClient 'It works when I use a    server-side cursor
      con.Open

      set com.ActiveConnection = con
      com.CommandText = "select * from dummy"
      com.CommandType = adCmdText


      res.Open com, , adOpenStatic, adLockOptimistic, -1
      res(1) = "5"
      res.Update 'This line causes the error.


      res.Close
      con.close
End Sub

The problem with changing it to a server-side cursor is that I am unable to populate the TDBGrid Pro 5.0 Control with the records retrieved above. Surely I can do this in my code but it doesnt solve the problem of trying to update in the TDBGridControl.It seems I can modify only the first row in the grid!!The moment I move to the next record in the grid there is a page fault error and VB crashes!

0
 

Expert Comment

by:rurajase
Comment Utility
Regarding ur question about Cursors, I had a similar problem.Now, I dont think ADO2.0 allows u to use adClientCursor.It gives a nasty error like "Insufficient base table information for updating or refreshing".Here is the code I implemented....

Private sub Form1_Load()

      Dim con As ADODB.Connection
      Dim com As ADODB.Command
      Dim res As ADODB.Recordset
      Dim par As ADODB.Parameter


      Set con = New ADODB.Connection
      Set com = New ADODB.Command
      Set res = New ADODB.Recordset


      con.ConnectionString = "dsn=amfm;usid=amfm_central;pwd=**;"
      con.CursorLocation = adUseClient 'It works when I use a    server-side cursor
      con.Open

      set com.ActiveConnection = con
      com.CommandText = "select * from dummy"
      com.CommandType = adCmdText


      res.Open com, , adOpenStatic, adLockOptimistic, -1
      res(1) = "5"
      res.Update 'This line causes the error.


      res.Close
      con.close
End Sub

The problem with changing it to a server-side cursor is that I am unable to populate the TDBGrid Pro 5.0 Control with the records retrieved above. Surely I can do this in my code but it doesnt solve the problem of trying to update in the TDBGridControl.It seems I can modify only the first row in the grid!!The moment I move to the next record in the grid there is a page fault error and VB crashes!

0
 
LVL 2

Accepted Solution

by:
majed100598 earned 150 total points
Comment Utility
With Oracle 8 Cd there is acontrol called oracle data control,
its the best control i use with oracle try to use it and all your problems well be removed
0
 
LVL 4

Expert Comment

by:tomook
Comment Utility
You mention you are using ADO 1.0. Have you tried 2.0? It is much better.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now