HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual Basic .NET

Article translations Article translations
Article ID: 309485 - View products that this article applies to.
This article was previously published under Q309485
Caution ADO and ADO MD have not been fully tested in a Microsoft .NET Framework environment. They may cause intermittent issues, especially in service-based applications or in multithreaded applications. The techniques that are discussed in this article should only be used as a temporary measure during migration to ADO.NET. You should only use these techniques after you have conducted complete testing to make sure that there are no compatibility issues. Any issues that are caused by using ADO or ADO MD in this manner are unsupported. For more information, see the following article in the Microsoft Knowledge Base:
840667 You receive unexpected errors when using ADO and ADO MD in a .NET Framework application
Expand all | Collapse all

On This Page

Summary

This step-by-step article demonstrates how to use the Data Link Properties dialog box to programmatically create a connection string at design time.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
  • Visual Studio .NET
  • ADO.NET fundamentals and syntax
  • ActiveX Data Objects (ADO) fundamentals and syntax

Create an ADO Connection

When you use ADO to create the connection, the data link creates a standard ADODB Connection object.
  1. Start Visual Studio .NET, and create a Windows Application in Visual Basic .NET. Form1 is created by default.
  2. From the Project menu, click Add Reference, and then add the following references:

    • Microsoft ActiveX Data Objects 2.7
    • Microsoft OLEDB Service Component 1.0 Type Library

    NOTE: After you click OK in the References dialog box, the following error message may appear:
    Could not find a primary interop assembly for the COM component 'Microsoft OLE DB Service Component 1.0 Type Library'. A primary interop assembly is not registered for this type library. Would you like to have a wrapper generated for you?
    Click Yes within the error message.
  3. Add a Button control to Form1.
  4. Add the following code to the Button1_Click event:
    Dim mydlg As New MSDASC.DataLinks()
    Dim ADOcon As New ADODB.Connection()
    
    ADOcon = mydlg.PromptNew
    ADOcon.Open()
    
    If ADOcon.State = 1 Then
          MsgBox("Connection Opened")
    Else
          MsgBox("Connection Failed")
    End If
  5. Press the F5 key, and then click Button1.
  6. Type the appropriate information in the Data Link Properties dialog box, and make sure that you select the Allow Saving Password check box.
  7. Click Test Connection.
  8. Click OK. If the connection test succeeded in the data link, a connection to the database is established, and a message box is displayed.

Create an OLE DB Connection

When you create an OLE DB connection with the OLE DB managed provider in .NET, you cannot create connections to ODBC data sources. Because ODBC has its own managed provider in .NET, you receive an error if you use the Microsoft OLEDB provider for ODBC drivers option in the Data Link Properties dialog box. In addition, you must load ADO into the application because the data link creates an ADODB Connection object that is not compatible with the OleDbConnection object. Thus, you must create an ADODB Connection and assign its ConnectionString property to the ConnectionString property of the OleDbConnection object for this to work properly.
  1. Start Visual Studio .NET, and create a Windows Application in Visual Basic .NET. Form1 is added to the project by default.
  2. From the Project menu, click Add Reference, and then add the following references:
    • Microsoft ActiveX Data Objects 2.7
    • Microsoft OLEDB Service Component 1.0 Type Library

  3. Add a Button control to Form1.
  4. Use the Imports statement on the System.Data.OleDb namespace so that you are not required to qualify declarations in this namespace later in your code. You must use the Imports statement prior to any other declarations:
    Imports System.Data.OleDb
  5. Add the following code to the Button1_Click event:
    Dim mydlg As New MSDASC.DataLinks()
    Dim OleCon As New OleDbConnection()
    Dim ADOcon As New ADODB.Connection()
    
    ADOcon = mydlg.PromptNew
    OleCon.ConnectionString = ADOcon.ConnectionString
    
    OleCon.Open()
    
    If OleCon.State = 1 Then
        MsgBox("Connection Opened")
    Else
        MsgBox("Connection Failed")
    End If
  6. Press F5, and then click Button1.
  7. Type the appropriate information in the Data Link Properties dialog box, and make sure that you select the Allow Saving Password check box.
  8. Click Test Connection.
  9. Click OK. If the connection test succeeded in the data link, a connection to the database is established, and a message box is displayed.

Additional Information

It requires additional effort to use this method to create an ODBC connection because the data link creates a connection string that is specific to OLE DB and is not compatible with the ODBC managed provider. For this to work, you must parse the ADODB connection string for the relevant information such as the user ID, password, and data source. After you obtain this information, you can use it to create a connection string that is specific to ODBC. Keep in mind that the data link only uses ODBC data source names (DSNs); thus, you cannot create a DSN-less connection through the data link.

References

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
286189 HOWTO: Invoke the OLE DB Data Link Properties Dialog Box in Visual Basic Code
283245 HOWTO: Persist Data Links Programmatically
193128 HOWTO: Create an ODBC and OLEDB Connection Prompt Control in ADO
For more general information about ADO.NET or Visual Basic .NET, refer to the following MSDN newsgroups:
microsoft.public.dotnet.framework.adonet


For more information, refer to the following book:
Ed Robinson, Michael Bond, Robert Ian Oliver Upgrading Microsoft Visual Basic 6.0 to Microsoft Visual Basic .NET
For more information, refer to the following Microsoft Training & Certification course:
Microsoft Corporation 2389 Programming with ADO.NET

Properties

Article ID: 309485 - Last Review: November 9, 2012 - Revision: 4.0
Applies to
  • Microsoft ADO.NET 1.1
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbhowtomaster KB309485

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