Solved

VB6 - Oracle 11g connection not working in app

Posted on 2014-10-22
9
633 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 76

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 76

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 76

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 76

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

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.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article will show, step by step, how to integrate R code into a R Sweave document
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…

810 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