Solved

Help with connection to Oracle

Posted on 2000-05-03
18
258 Views
Last Modified: 2008-02-26
Our company has been using Oracle (version 7.3) on a Unix Platform (sun) with an old 4GL language through a telenet session providing a character based application.  I want to move toward a different front end and am thinking VB is the answer using ActiveX.  Unfortunately I am having trouble connecting to the Oracle database.  I have installed Oracle Net and tried setting up a System DSN using Microsoft ODBC Driver for Oracle, but am not sure if I set it up correctly.   I have filled it out as follows: Data Source Name - MYDEV (this is the alias is used in setting up Oracle Net), Description MY DEV, User name dev (this is the the name of my Oracle Database), Connect string sun1 (Wasn't sure what to put here.  This is the system name that Oracle resides on).  When I try and use Oracle NetTest to see if I am connected it always fails. Any help would be appreciated.

Thanks

HS
0
Comment
Question by:hstein
  • 7
  • 3
  • 3
  • +4
18 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 2774069
perhaps an easy way to try and connect would be to stick an ado data control on your form, go to its properties window, click custom, and try to build the connection to a particular database from there.  it allows, after you have gone through a few steps, to test your connection.  just click on projects -> components and check off the microsoft ado data control box and stick it on your form.  
0
 

Author Comment

by:hstein
ID: 2774220
Thanks for the suggestion. I tried, but am still getting an error connecting.  I clicked on connection string, clicked on build, selected Microsoft OLE DB Provider for Oracle, clicked on next, entered the server name - sun1 (this is where it seems confusing to me.  The only way for sun1 to resolve itself is using tcp/ip and yet I created an alias in oracle net named MYDEV).  Then I entered user name and password and clicked Test Connection.  I received the following error: Test connection failed because of an error in initializing provider. ORA-1254: TNS: could not resolve service name.

When I entered my alias instead (MYDEV) I received the following error: Test connection failed because of an error in initialzing provider. ORA-01017: invalid username/password:login denied (I have verified that my login and password work in my telenet session).


HS
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2774260
0
 

Author Comment

by:hstein
ID: 2774312
I read the article.  I am running on an windows 95 system as are most of the clients in my organization.  I was hoping that when the VB program was completed using ADO I could compile and roll it out without having to setup each PC.  Isn't it possible to connect to Oracle on the '95 platform.  

HS
0
 
LVL 1

Expert Comment

by:NancyShah
ID: 2774316
Hi hstein,

Just try this ..actually what I feel
is that if you want to use ADO no need of ODBC.
Install your Oracle client on your machine.In this it will ask for the ip address of the server.
once a client is installed you will have 2 thnings with you
1) UserName ==one which you made while installing Client
2) password

Use these 2 things in the Connect string of ADO.
WHere you will need to enter username and password
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2774329
yes in that case when you go to build the string with the adodc, forego the server entry and just try a username/password entry
0
 

Author Comment

by:hstein
ID: 2774574
Maybe I don't have the right Oracle program.  I used Oracle Net Easy Configuration.  This doesn't require a user name or password, just the tcp/ip connection, an alias name,  and the Database instance.  

Hs
0
 
LVL 1

Accepted Solution

by:
karlww earned 30 total points
ID: 2774661
We use Oracle8 on VMS
Our DSN is setup using the MS Oracle driver from MDAC 2.1, using your example we would have a setup like this;

In ODBC Administrator
=====================
Data Source Name: MyDev
Description: DEV Database DSN
Username: (blank)
Server: DEV (name of the Oracle DB)

--------

Using VB/ADO and ODBC driver

Dim cn As New ADODB.Connection

cn.ConnectionString = "DSN=MyDev;UID=scott;PWD=tiger" ' to borrow from the famous Oracle example
cn.Open

or using VB/ADO and MS Oracle provider (in which case you don't need the DSN at all)

cn.ConnectionString = "Provider=MSDAORA;Data Source=dev;User Id=scott;Password=tiger"
cn.Open



0
 

Expert Comment

by:lkey
ID: 2776137
Have you established the relationship between your computer and the instance names on your server?  With NT/98/95 there is a utility called easyconfig that creates a relationship with the TNSNAMES.ORA file.  Unless this relationship is created no ODBC will be able to resolve the connection.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:hstein
ID: 2777390
I can't get the connection to work.  Maybe I have the wrong Oracle net client - Oracle Net8 Easy Config.  When I open it and pick TCP/IP , then enter the IP address and keep the port number at 1521 (the default), then enter the SID which is DEV.  When I press test and enter login/password I get the following message:The test did not succeed. ORA-01017: invalid username/password; logon denied.  I know my name and  password are valid as I login using a telenet session.

Any ideas?

Thanks

Hs
0
 
LVL 1

Expert Comment

by:karlww
ID: 2778051
Does SQL*Plus even work from your computer?  It sounds like lkey is right.  You've got to get the Oracle networking configured before VB even has a chance.
0
 

Author Comment

by:hstein
ID: 2789080
Adjusted points from 75 to 150
0
 

Author Comment

by:hstein
ID: 2789081
I can't seem to connect.  Is there a different version of SQL *Net I should be using.  My Oracle version is 7.3.  Could this be causing the problem?
0
 

Expert Comment

by:rfyl
ID: 2802481
Have you checked that your client computer can 'see' the server?

Try opening a DOS command prompt and typing in 'PING sun1'.  (Assuming sun1 is the Oracle server name).

Hopefully this will return that it was able to ping the server.  If it has not then you need to check your TCP/IP configuration.

Otherwise, I do not know what groups are available here, but as the others have said - your problem is not with Visual Basic, you have a networking/Oracle connection problem.
0
 

Author Comment

by:hstein
ID: 2802510
Thank you all.  I have made the connection!! It did turn out to be a problem with the Sun1.  I will be asking that all the points be split among all those that answered.

Thanks
0
 
LVL 3

Expert Comment

by:darinw
ID: 2808518
Reveiwing question.

darinw
0
 
LVL 3

Expert Comment

by:darinw
ID: 2814643
Community Support has reduced points from 150 to 30
0
 
LVL 3

Expert Comment

by:darinw
ID: 2814644
Reducing points to 30 to allow for split.

hstein - You can now accept the proposed answer to award the first Expert. To award the other Experts, you can create new questions in this topic area with a title of 'For ExpertName -- 10338168' using the appropriate Experts username.

Remember, the Accept Comment as Answer button is in the header of the comment.

For your convenience, you can use this link to create the new question (right click and open in a new window):
http://www.experts-exchange.com/bin/NewQForm?ta=31

darinw
Customer Service
0

Featured Post

What Security Threats Are You Missing?

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

762 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

19 Experts available now in Live!

Get 1:1 Help Now