HOW TO: Quote a Member of the Connection String in ADO.NET By Using Visual Basic .NET (316364)

SUMMARY

This article describes how to format a string if there is a quotation mark in a member of the connection string.

back to the top

Description of the Technique

The sample code demonstrates how to format a password that includes a quotation mark. Without the proper formatting, the compiler generates build errors. The errors that you may receive include: If you are using a single quotation mark in your password, you have to use two double quotation marks to enclose the string:

password=a'b       'Not properly formatted
				

If you are using a double quotation mark in your password, you have to use two double quotation marks instead of one, and then you have to enclose the string with single quotation marks:

password=a"b       'Not properly formatted
				

back to the top

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs that you need to perform the procedure:

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
• Microsoft Visual Studio .NET
• Microsoft SQL Server 7.0 or later

The following items describe what you need to understand before you use the information that is provided in this article:

• Visual Studio .NET
• ADO.NET fundamentals and syntax

back to the top

Create the Project and Add the Code

1. Start Visual Studio .NET.
2. Create a new Windows Application in Visual Basic .NET.
3. Make sure that your project contains a reference to the System.Data namespace, and add a reference if it does not.
4. Place a CommandButton on Form1. Change the Name property of the button to btnTest, and then change the Text property to Test.
5. Use the IMPORTS statement on the namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the General Declarations section of Form1:

   Imports System
   Imports System.Data
   Imports System.Data.SqlClient
   					

6. Copy and paste the following code in the Click event of btnTest:

           Dim da As SqlDataAdapter
           Dim ds As New DataSet()
           Dim cn As New SqlConnection("server=myServer;user id=myUID;password=a"b;database=northwind")
           da = New SqlDataAdapter("select * from customers", cn)
           da.Fill(ds, "customers")
           MsgBox("connected")
   					

7. Modify the connection strings appropriately for your environment. Notice that the password has a quotation mark in it.
8. Save your project. On the Debug menu, click Start, and then run your project. Notice the build errors that you may encounter.
9. Modify the connection string to:

   ("server=myServer;user id=myUID;password='a""b';database=northwind")
   					

10. Save your project, and then run it.

back to the top