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

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.

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

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.

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:
  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.
  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.

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 REFERENCESsection for more information.

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: For more information about how to migrate from Microsoft ActiveX Data Objects (ADO) to Microsoft ADO.NET, see the following Microsoft Web site:
Properties

Article ID: 311023 - Last Review: Jun 9, 2009 - Revision: 1

Feedback