Solved

VB6 - Oracle 11g connection not working in app

Posted on 2014-10-22
9
605 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

758 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

22 Experts available now in Live!

Get 1:1 Help Now