Solved

ADO and RDO  problems

Posted on 1998-12-01
10
1,199 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
ID: 1497886
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
ID: 1497887
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
ID: 1497888
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Expert Comment

by:GeoffS
ID: 1497889
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
ID: 1497890
1. To use client-side cursors with ADO, set the CursorLocation property of a Connection = adUseClient

0
 

Author Comment

by:puna
ID: 1497891
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
ID: 1497892
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
ID: 1497893
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
ID: 1497894
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
ID: 1497895
You mention you are using ADO 1.0. Have you tried 2.0? It is much better.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

792 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