Solved

VB6 - Oracle 11g connection not working in app

Posted on 2014-10-22
9
646 Views
Last Modified: 2014-10-25
Hi

I have this below code i'm using under Oracle 9i to connect my VB6 to an oracle table.

Now that i'm on Oracle OraClient11g_home1, i can't anymore.

How should I fix the code to connect the same way as i used to, but using OraClient11g_home1?

Thanks again

Private Sub Command1_Click()
  MSHFlexGrid1.Clear
    MSHFlexGrid1.Rows = 2
    MSHFlexGrid1.Cols = 2

    On Error Resume Next
    Dim oconn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer

       
     'SQL query detail:
    strSQL = "SELECT * FROM tmmgr.location"

    Set oconn = New ADODB.Connection
   ' oconn.Open "Provider=OraOLEDB.Oracle.1;Data Source=TEST_ENVIRONMENT;User Id=XXXXXXX;Password=XXXXXXXX;"
       
    RS.CursorType = adOpenStatic
    RS.CursorLocation = adUseClient
    RS.LockType = adLockOptimistic
    RS.Open strSQL, oconn, adCmdText
    Set MSHFlexGrid1.DataSource = RS
End Sub

Open in new window

0
Comment
Question by:Wilder1626
  • 5
  • 4
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397125
OleDB connections haven't changed since I stopped using them years ago.

Make sure you have the 11g OleDB drivers installed.

The '.1' seems odd to me.  Try just OraOLEDB.Oracle.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40397344
How can i validate for the 11g OleDB drivers?

I actually tried like this but it does not pull anything. and no error

oconn.Open "Provider=OraOLEDB.Oracle;Data Source=TEST.ENVIRONMENT;User ID=XXXXXXX;Password=XXXXXXX;"
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397359
>>How can i validate for the 11g OleDB drivers?

Depends:
If you are using the regular Oracle client, run the installer and look at the installed options.  Look for OleDB

If you are using the Intant Cloent, look for the DLLs.  Not sure where in the registry to look to see that they have been properly installed.

>> actually tried like this but it does not pull anything. and no error


Well, no error means to me it is finding the drivers.

The first post had: TEST_ENVIRONMENT.  The latest one has TEST.ENVIRONMENT.

What is in the tnsnames.ora file?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Author Comment

by:Wilder1626
ID: 40397463
well actually, the true name is G.ENVIRONMENT. Sorry for the confusion.

oconn.Open "Provider=OraOLEDB.Oracle;Data Source=G.ENVIRONMENT;User ID=XXXXX;Password=XXXXX;"

So normally, no errors = driver installed. Correct?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397472
>>So normally, no errors = driver installed. Correct?

Cannot say for sure but it seems like a reasonable assumption to me.

You didn't say what the error was that you were receiving but if it was something like "driver not found" and that error went away, it mush have found something.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40397551
i don't have any errors. It just don't populate the data into the grid.

Is there a way to add into the connection string those below details:
SID
Protocol
Host Name
Port Number
Service Naming
User ID
Password

Normally, with all those details, i should connect.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40397564
>> It just don't populate the data into the grid.

Then one of two things:
You aren't connecting to the database you think you are.
The query you are running isn't returning any rows.

>>Is there a way to add into the connection string those below details:

I believe you can use EZConnect with OleDB connections.  I've seen references to it when I've Googled around.

Typically you rely on the tnsnames.ora file to provide all that information (well the server info, not the username and password).

Check out:
http://www.oracle.com/technetwork/database/windows/install1110720-098972.html

Connection Setup Quick Start
======================================================
 There are a number of methods to connect Oracle client to a database server. Two of the most common include EZCONNECT and TNSNAMES. EZCONNECT is the easiest to setup. TNSNAMES is much more maintainable in the long term. If you are new to Oracle, we recommend you use EZCONNECT. You only have to choose one or the other to connect.  
These quick start instructions assume you have a valid username and password for the database server.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40397723
Thanks

I will look at it and let you know shortly if i found something
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40403888
Thank you so much. I found out that i had some dll issue with oracle. I re-installed Oracle and now it work.

Thanks again for your end.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
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…

828 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