HOW TO: Manage Quotation Marks in Concatenated SQL Literals by Using ADO.NET and Visual Basic .NET (311023)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual Basic .NET (2002)
  • Microsoft Visual Basic .NET (2003)
  • Microsoft .NET Framework Class Libraries
  • Microsoft .NET Framework Class Libraries 1.1
This article was previously published under Q311023
For a Microsoft Visual C# .NET version of this article, see 311021.
For a Microsoft Visual Basic 6.0 version of this article, see 178070.

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data.SqlClient
  • System.Text
  • System.Data.OleDb

IN THIS TASK

SUMMARY

This article demonstrates how you can manage quotation marks in string values when you concatenate SQL statements. The article provides a sample solution that uses the Sql managed provider (Microsoft SQL Server). This article also provides a Microsoft Access sample that uses the Microsoft OLE DB Provider and related data access classes.

back to the top The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft Windows XP, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, or Microsoft Windows 2000 Advanced Server
  • Microsoft .NET Framework
  • Microsoft Access
  • Microsoft SQL Server
back to the top

Create a Visual Basic .NET Console Application

These steps demonstrate how to create a new Visual Basic .NET Console Application named QuoteDemo.
  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, point to New, and then click Project.
  3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click Console Application under Templates.
  4. In the Name text box, type QuoteDemo.
  5. Click OK to open the project.
back to the top

Samples That Manage Quotation Marks in Concatenated SQL Statements

In this section, you create a sample that demonstrates how to use the Replace method of the String class. This sample replaces single quotation marks with two adjacent single quotation marks to make sure that the data is formatted correctly when you concatenate values for a SQL statement. The code in this demonstration uses a SQL INSERT statement on the Employees table of the Northwind database.

Both of the SQL Server and the Access samples that are contained in this article reference the FixString method. You must include this method for either sample to function properly. The following steps demonstrate how to do this:
  1. Right-click Module1.vb in Solution Explorer, and then click View Code.
  2. Add the following method to the module: 
    Private Function FixString(ByVal SourceString As String, ByVal StringToReplace As String, ByVal StringReplacement As String)
        SourceString = SourceString.Replace(StringToReplace, StringReplacement)
        Return SourceString
End Function
    The preceding method uses the Replace method of the String object. You can use this method to replace characters with other choices. The input parameters include the original string (SourceString), the string that you want to replace (StringToReplace), and the string that you want to use (StringReplacement).

    NOTE: For more complex string substitutions, see the following Microsoft .NET Class Library documentation:

    StringBuilder Class
    http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTextStringBuilderClassTopic.asp?frame=true

back to the top
  1. Add the following namespace references to Module1.vb:
    Imports System.Data.SqlClient
Imports System.Text
  2. Add the following method to Module1.vb:
        Private Sub RunSqlDemo()
        'Create a variable to hold the last name and the first name.
        Dim Fname As String = "Susan"
        Dim Lname As String

        Console.Write("Type Last Name (SQL Server Sample): ")
        Lname = Console.ReadLine()

        'Call FixString to replace a single quotation mark with two
        'adjacent single quotation marks.
        Lname = FixString(Lname, "'", "''")
        Console.WriteLine("Modified value: " & Lname)

        'Although the Fname variable in this sample is fine,
        'run Replace on this variable also.
        Fname = FixString(Fname, "'", "''")

        'Use a StringBuilder object to build the SQL statement.
        Dim sb As New StringBuilder()
        sb.Append("INSERT INTO Employees(LastName, FirstName) Values( '")
        sb.Append(Lname)
        sb.Append("','")
        sb.Append(Fname)
        sb.Append("')")

        'Present the SQL statement to the console, and include the modified
        'values.
        Console.WriteLine("SQL string: " & sb.ToString())

        'Create the connection string.
        Dim myConStr As String = "Server=localhost;Database=Northwind;UID=myUserID;PWD=myPassword"
        Dim myConn As SqlConnection = New SqlConnection(myConStr)
        Dim myCmd As SqlCommand = New SqlCommand(sb.ToString(), myConn)

        Try
            'Open the connection.
            myConn.Open()
            myCmd.ExecuteNonQuery()
            Console.WriteLine("Values inserted into table (SQL Server Sample)")
        Finally
            Try
                myConn.Close()
            Catch
            End Try
        End Try
    End Sub
    NOTE: Modify the SQL Server connection string to properly reflect your environment.

  3. To call the RunSqlDemo method, add the following code to the Main method of the module: 
    RunSqlDemo()
  4. On the File menu, click Save All to save the solution.
  5. On the Build menu, click Build Solution.
  6. On the Debug menu, click Start Without Debugging to run the application.
  7. When you are prompted to request a value for the last name, type the name O'Conner, and then press ENTER.

    You receive a message that includes the following information:

    • How the FixString method modifies the last name value.
    • How the modified string appears in the SQL statement.
    • Confirmation that the value is added to the database.
    Notice that the last name is modified to O''Conner. SQL Server interprets the two adjacent single quotation marks as an embedded single quotation mark.
back to the top
  1. Add the System.Data.OleDb and the System.Text namespace references to Module1.vb. The namespace references listing appears similar to the following listing if you created the SQL Server sample in the previous section: 
    Imports System.Data.SqlClient
Imports System.Text
Imports System.Data.OleDb
    The System.Data.SqlClient namespace reference is not required if you only intend to follow the Access code sample.
  2. Add the following method to Module1.vb:
        Private Sub RunAccessDemo()
        'Create a variable to hold the last name and the first name.
        Dim Fname As String = "Susan"
        Dim Lname As String

        Console.Write("Type Last Name (Access Sample): ")
        Lname = Console.ReadLine()

        'Call FixString to replace a single quotation mark with two         'adjacent single quotation marks.
        Lname = FixString(Lname, "'", "''")
        Console.WriteLine("Modified value: " & Lname)

        'Although the Fname variable in this sample is fine, 
        'run Replace on the variable also.
        Fname = FixString(Fname, "'", "''")

        'Use a StringBuilder object to build the SQL statement.
        Dim sb As New StringBuilder()
        sb.Append("INSERT INTO Employees(LastName, FirstName) Values( '")
        sb.Append(Lname)
        sb.Append("','")
        sb.Append(Fname)
        sb.Append("')")

        'Present the SQL statement to the console, and include the modified 
        'values.
        Console.WriteLine("SQL string: " & sb.ToString())

        'Create the connection string.
        Dim myConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=C:\MyDatabases\NWIND.MDB"

        Dim myConn As OleDbConnection = New OleDbConnection(myConStr)
        Dim myCmd As OleDbCommand = New OleDbCommand(sb.ToString(), myConn)

        Try
            'Open the connection.
            myConn.Open()
            myCmd.ExecuteNonQuery()
            Console.WriteLine("Values inserted into Employees table! (Access Sample)")
        Finally
            Try
                myConn.Close()
            Catch
            End Try
        End Try
    End Sub
    NOTE: Modify the SQL Server connection string to properly reflect your environment.

  3. To call the RunAccessDemo method, add the following code to the Main method of the module: 
    RunAccessDemo()
    NOTE: If you followed the steps to run the previous SQL Server sample, and if you do not want to run the RunSqlDemo method again, you can comment out the call to the RunSqlDemo method in the Main method.

  4. On the File menu, click Save All to save the solution.
  5. On the Build menu, click Build Solution.
  6. On the Debug menu, select Start Without Debugging to run the application.
  7. When you are prompted to request a value for the last name, type the name O'Conner, and then press ENTER.

    You receive a message that includes the following information:

    • How the FixString method modifies the last name value.
    • How the modified string appears in the SQL statement.
    • Confirmation that the value is added to the database.
    Notice that the last name is modified to O''Conner. The database interprets the two adjacent single quotation marks as an embedded single quotation mark.

    You can modify this sample so that the single quotation mark delimiters in the SQL statement are replaced by a double quotation mark. However, this sample cannot handle situations where a double quotation mark is contained in the input string.
back to the top

Troubleshooting

  • If you do not replace the single quotation mark with two adjacent single quotation marks, you receive a syntax error that is similar to either of the following error messages:
    System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'Conner'. Unclosed quotation mark before the character string ')

    -or-

    Unhandled Exception: System.Data.OleDb.OleDbException: Syntax Error (missing operator) in query expression ''O'Conner', 'Susan')'
  • When you use the Access sample in this article, if you press ENTER without providing any input when you are prompted, you may receive the following error message (or a similar one):
    Field TableName.FieldName cannot be a zero-length string.
    Access interprets an empty string field as a zero-length string. This error occurs if the AllowZeroLength property of the database field is not set to Yes. See the REFERENCES section for more information.
back to the top

REFERENCES

For additional information about the AllowZeroLength property in Access, click the article number below to view the article in the Microsoft Knowledge Base:

209098 ACC2000: Using the AllowZeroLength and Required Properties

For documentation and samples about common tasks in ADO.NET programming, see the following Microsoft Web site:

ADO.NET Support Center
http://support.microsoft.com/default.aspx?xmlid=fh%3BEN-US%3Badonet

For more information about how to migrate from Microsoft ActiveX Data Objects (ADO) to Microsoft ADO.NET, see the following Microsoft Web site:

Overview of ADO.NET
http://msdn.microsoft.com/library/en-us/dnanchor/html/adonetanchor.asp

back to the top
Modification Type:MajorLast Reviewed:4/20/2006
Keywords:kbHOWTOmaster kbSqlClient kbSystemData KB311023 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.