How to use the ODBC .NET Managed Provider in Visual Basic .NET and connection strings

Article translations Article translations
Article ID: 310985 - View products that this article applies to.
This article was previously published under Q310985
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article describes how to use the ODBC .NET Managed Provider in Visual Basic .NET. This article also includes samples connection strings that you can use to access your data.

The ODBC .NET Data Provider is an add-on component to the Microsoft .NET Framework Software Development Kit (SDK). It provides access to native ODBC drivers the same way that the OLE DB .NET Data Provider provides access to native OLE DB Providers. Although the ODBC .NET Data Provider is intended to work with all compliant ODBC drivers, it has only been tested with the following drivers:
  • Microsoft SQL ODBC Driver
  • Microsoft ODBC Driver for Oracle
  • Microsoft Jet ODBC Driver
NOTE: If the .NET Framework SDK (which is included with Visual Studio .NET) is not installed, the setup for this download fails. As part of the setup for this download, the Microsoft.Data.ODBC namespace is added to the Global Assembly Cache by using the Gacutil.exe utility that is included with the .NET Framework SDK.

Download the ODBC .NET Provider

  1. Download the ODBC .NET Managed Provider from the following Microsoft Web site:
    http://www.microsoft.com/downloads/details.aspx?familyid=6ccd8427-1017-4f33-a062-d165078e32b1
  2. The ODBC .NET Data Provider also requires that you install Microsoft Data Access Components (MDAC) version 2.7 or later. You can download the latest version of MDAC from the following Microsoft Web site:
    http://msdn.microsoft.com/en-us/data/aa937729.aspx
  3. After you install the ODBC .NET Data Provider, proceed to the next section to create the project.

Create the project

  1. Start Microsoft Visual Studio NET.
  2. Create a new Visual Basic Windows Application project. Form1 is added to the project by default.
  3. On the Project menu, click Add Reference.
  4. On the .NET tab, click Microsoft.Data.ODBC.dll. After the Microsoft.Data.ODBC.dll assembly appears in the list of selected components, click OK.
  5. Switch to Code view, and add the following code immediately before the Public Class Form1 code:
        Imports System.Data
        Imports Microsoft.Data.ODBC
    					
  6. Add four Button controls to Form1, and label these controls SQL Server, Jet, Oracle and DSN respectively.

Connection string samples

  1. Add the following code to the SQL Server button:
         Dim cn As OdbcConnection
         cn = New OdbcConnection("DRIVER={SQL Server};SERVER=MySQLServer;UID=sa;" & _
                                 "PWD=mypassword;DATABASE=northwind;")
    
         Dim mystring As String = "select * from Customers"
         Dim cmd As OdbcCommand = New OdbcCommand(mystring)
         cn.Open()
         MsgBox("Connected")
         cn.Close()
    					
  2. Add the following code to the Jet button:
         Dim cn as OdbcConnection
         cn = New OdbcConnection ("Driver={Microsoft Access Driver (*.mdb)};" & _
              "DBQ=D:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;UID=;PWD=")
         Dim mystring As String = "Select * from Titles"
         Dim cmd as OdbcCommand = New OdbcCommand(mystring)
         cn.Open()
         MsgBox("Connected")
         cn.Close()
    					
  3. Add the following code to the Oracle button:
         Dim cn as OdbcConnection
         cn= New OdbcConnection ("Driver = {Microsoft ODBC for Oracle};" & _
                                 "Server=myOracleserver;uid=myuid;pwd=mypwd")
         Dim myString as String = "Select * from Customers"
         dim cmd as OdbcCommand = New OdbcCommand(myString)
         cn.open()
         MsgBox "Connected"
         cn.Close
    					
  4. Add the following code to the DSN button:
         Dim cn as OdbcConnection
         cn = New OdbcConnection ("dsn=MyDSN;uid=sa;pwd=myPassword;")
         Dim mystring As String = "Select * from customers"
         Dim cmd As OdbcCommand = New OdbcCommand(mystring)
         cn.Open()
         MsgBox("Connected")
         cn.Close()
    					
  5. Modify the OdbcConnection strings as appropriate for your environment.

Test the client application

  1. Press the F5 key to compile and to run the application.
  2. Click each button. A message box appears, which states that you have successfully connected to your data.

Troubleshooting

If you encounter a problem when you connect to your data source (for example, if you use an incorrect password, User ID, or database name), you receive the following generic error message unless you trap for a specific error message:
An unhandled exception of type 'Microsoft.Data.ODBC.OdbcException' occurred in Microsoft.Data.ODBC.dll. Additional information: System Error
To provide more information about the error and to assist in troubleshooting, you can add a try-catch-finally block to the code. For example:
    Try
    cn.Open()
    Catch ex as OdbcException
    MsgBox(ex.Message)
    Finally
    cn.Close()
    End Try
				

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:
168336 How to open ADO connection and recordset objects
For more information about .NET managed providers, refer to the .NET Developer's Center or the following Microsoft Web site:
Inside .NET Managed Providers
http://msdn.microsoft.com/en-us/library/ms810268.aspx
For more information about .NET, visit the .NET newsgroups. The microsoft.public.dotnet.framework.odbcnet newsgroup has been set up for this release.

Properties

Article ID: 310985 - Last Review: September 19, 2005 - Revision: 4.3
APPLIES TO
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbhowtomaster kbsystemdata KB310985

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com