Connecting to an oracle database from visual basic 2005

I am trying to connect to an oracle 8i database from within visual basic 2005. The code i am using to try to connect is listd below

for the data source i am useing the name provided in the tnsnames.ora file.

I havent sent a query to it yet because I cannot get that far. I just ran the debugger to see if i got it to connect but I keep getting a pop box with this in it:

Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Am I using the right connection string? If not what would be the correct way of doing it. ODP.net is not available because according to oracle it is only for versions 9 or higher.

Imports System.Data.Odbc
 
Dim connection As OdbcConnection
        Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"
        connection = New Odbc.OdbcConnection(sConnString)
        connection.Open()

Open in new window

rolltide0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yossarian-22Commented:
Check out this website. Here you'll find different connection strings for all kind of scenarios: http://www.connectionstrings.com/?carrier=oracle
0
ajexpertCommented:
Hi,
The best way to obtain connection string is
Drop data adapter and dataset on the Windows form.
Create a dataset by attaching dataadapter.  In the properties of dataset you should be able to view the connection string

Hope it helps
0
rolltide0Author Commented:
No go, same error. I tried several connect strings on that website as well as the connect string from the data adapter, and that error :

 Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed

is still there.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Devinder Singh VirdiLead Oracle DBA TeamCommented:
First Check the status of your database, whather it is on, and are you able to connect from sql-prompt.
second do you need to set some enviornment parameter like oracle_sid, oracle_home etc.
Are you working on same machine where db is present or some other.
0
rolltide0Author Commented:
I am working on a workstation that does not host the database.  One thing of note, My access to the database is read only.  I know I can connect to the database and it is running because

1) I can open the application that uses it
2) if the database could not accept connections half the office would be beating down my door
3) I can successfully create a connection under the data Connections in the server explorer

I have tried many different connect strings and all with the same error.
I thought the sid was set using the "data source = sid" which has already be tried.
0
ajexpertCommented:
OK.
It seems you are trying to connect thru DSN.
You have to create DSN on your machine with name MyOracleDB.  I belive you know how to create DSN.
If DSN is setup correctly, the connection string should work.

Hope it helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rolltide0Author Commented:
Ok I made an entry in the system DSN as follows:

Data Source Name : cutacms
Description : <blank>
User Name: <user name>
Server: cutacms

I used Microsoft ODBC for Oracle driver

cutacms is defined in tnsnames.ora

The connection string setup I used is as follows:

Dim connection As Odbc.OdbcConnection
Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=cutacms;User Id=<user name>;Password=<password>;"
connection = New Odbc.OdbcConnection(sConnString)
connection.Open()

It is still giving the same error.
0
Mark GeerlingsDatabase AdministratorCommented:
It certainly is possible to connect to Oracle databases from ODBC-based applications, like VB.  I'm no expert on VB, but I have created and used ODBC-based connections to various Oracle databases from Oracle8 - Oracle11.  I have used both the Microsoft ODBC driver for Oracle as well as the Oracle Client since I've found that some applications worked better with one than the other.

One possible problem may be that Oracle8 now is considered rather old, and maybe the ODBC client you are using is not compatible with Oracle 8.  Do you have an Oracle8 client that you can install?  If yes,  you will need to configure SQL*Net in the Oracle client before you can use the Oracle ODBC driver.  Use the wizard, and supply the host name (or IP address) the database name (or SID) and the port (1521 is the default port for Oracle).  Then create a System DNS using the Oracle ODBC driver that is part of the Oracle Client install.
0
Mark GeerlingsDatabase AdministratorCommented:
Oops!  I meant a "System DSN", not a "SYSTEM DNS".
0
ajexpertCommented:
OK,
Here is another way of testing your DSN.
Steps
1.  Right Click on Desktop to create new text file.
2.  Rename it as tstdsn.udl.  You will see the icon is changed
3.  Double click on tstdsn.udl
4.  Data Link Properties PopUp will open.  Make sure you are on Connection Tab
5.  Select Use data source name radio button
6.  From the drop down select the name of your DSN i.e. cutacms
7.  Now, Enter information to log on to Server, i.e. Username and Password.  Remember these username and password are the same while you connect to DB from sql prompt
8.  Click on Test Connection

If your connection is not succesful. You have either not made ur DSN setup properly or you have to test the oracle connection from SQL Prompt

Hope this helps
0
rolltide0Author Commented:
I made the file, and the test was successful, so the problem has to be in my code somewhere. My hands are tied with using Oracle 8 unfortuantely and I cannot install anything else on my workstation as it may conflict with the current program we use to interface with the database.

Know what little I know about VB my question is now

1) am I using the right import statement
2) am I using the right provider in my connection string
3) is there anything else I could be missing?

Here is what I currently have after numerous tests and trials:

Imports System.Data.odbc

Dim connection As Odbc.OdbcConnection
        Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=cutacms;User Id=uid;Password=pwd;"
        connection = New Odbc.OdbcConnection(sConnString)
        connection.Open()

0
rolltide0Author Commented:
***UPDATE***
I got the security error fixed buy going into .net 2.0 and setting the all_code segment to full control, but I still having connection issues, now I am getting:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

As I mentioned before I can connect to the database by making a data connection in server explorer, so I went to the properties and copied the connect string like it was suggested above and still getting that error. As a refresher, I am using the following string and credentials:

Data Source Name : cutacms
Description : <blank>
User Name: <user name>
Server: cutacms

I used Microsoft ODBC for Oracle driver

cutacms is defined in tnsnames.ora

The connection string setup I used is as follows:

Dim connection As Odbc.OdbcConnection
Dim sConnString As String = "Provider=OraOLEDB.Oracle;Data Source=cutacms;User Id=<user name>;Password=<password>;"
connection = New Odbc.OdbcConnection(sConnString)
connection.Open()

I have also tried the string "Provider=MSDAORA;Data Source=cutacms;User ID=uid;Password = pwd;"
0
ajexpertCommented:
OK,
Let me know if  cutacms is then name of database you are trying to connect to?
0
rolltide0Author Commented:
cutacms is the sid of the database as defined by tnsnames.ora. I copied part of the file to list it below just in case it will help

 (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = logistics-svr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CUTACMS)
0
ajexpertCommented:
Can you put IP address instead of host name.

Also did you tried to test DSN as steps mentioned in 11.05.2008 at 10:33PM EST, ID: 22892201
0
rolltide0Author Commented:
Yes the DSN is working correctly. subbing in the ip address does not work either.
0
ajexpertCommented:
OK,
So you are able to connect via DSN and it seems its syntax issue.
Please refer to link http://www.vbdotnetforums.com/oracle/3883-oracle-connection-string-syntax.html
0
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Are you able to connect database using ADODB object instead of odbc?
0
rolltide0Author Commented:
I got it. I had to change the connect string to "Driver={Microsoft ODBC for Oracle};Server=cutacms;Uid=uid;Pwd=pwd;"

So, to summarize this is what fixed this issue:
1) Create a DSN
2) full full access to the all_code segment in .net 2.0
3) change the connect string to the listed above (which happened to be the last connect string listed on the connectionstrings website left for me to use)


Man, working with SQL server is so much easier. Thanks for all the help, Im sure Ill be back soon with other issues.
0
ajexpertCommented:
Great!!
I am glad that I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.