Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Connecting to an oracle database from visual basic 2005

Posted on 2008-11-05
20
Medium Priority
?
529 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:rolltide0
  • 8
  • 7
  • 2
  • +2
20 Comments
 
LVL 3

Assisted Solution

by:Yossarian-22
Yossarian-22 earned 200 total points
ID: 22889538
Check out this website. Here you'll find different connection strings for all kind of scenarios: http://www.connectionstrings.com/?carrier=oracle
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 22889831
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
 

Author Comment

by:rolltide0
ID: 22889967
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 22890313
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
 

Author Comment

by:rolltide0
ID: 22890391
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
 
LVL 14

Accepted Solution

by:
ajexpert earned 1800 total points
ID: 22890451
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
 

Author Comment

by:rolltide0
ID: 22890729
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22891977
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 22891981
Oops!  I meant a "System DSN", not a "SYSTEM DNS".
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 22892201
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
 

Author Comment

by:rolltide0
ID: 22892504
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
 

Author Comment

by:rolltide0
ID: 22895661
***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
 
LVL 14

Expert Comment

by:ajexpert
ID: 22895731
OK,
Let me know if  cutacms is then name of database you are trying to connect to?
0
 

Author Comment

by:rolltide0
ID: 22896025
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 22896215
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
 

Author Comment

by:rolltide0
ID: 22896336
Yes the DSN is working correctly. subbing in the ip address does not work either.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 22896497
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
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 22896571
Are you able to connect database using ADODB object instead of odbc?
0
 

Author Comment

by:rolltide0
ID: 22896765
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 22896802
Great!!
I am glad that I could help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

572 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