Microsoft KB Archive/311023

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

Article ID: 311023

Article Last Modified on 4/20/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft .NET Framework Class Libraries 1.1
 * 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 Requirements Create a Visual Basic .NET Console Application Samples That Manage Quotation Marks in Concatenated SQL Statements
 * Sample That Uses SQL Server
 * Sample That Uses Access

Troubleshooting REFERENCES



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:  Right-click Module1.vb in Solution Explorer, and then click View Code.  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   Add the following namespace references to Module1.vb: Imports System.Data.SqlClient Imports System.Text   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 = &quot;Susan&quot; Dim Lname As String

Console.Write(&quot;Type Last Name (SQL Server Sample): &quot;) Lname = Console.ReadLine

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

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

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

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

'Create the connection string. Dim myConStr As String = &quot;Server=localhost;Database=Northwind;UID=myUserID;PWD=myPassword&quot; 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(&quot;Values inserted into table (SQL Server Sample)&quot;) Finally Try myConn.Close Catch End Try End Try End Sub NOTE: Modify the SQL Server connection string to properly reflect your environment.

  To call the RunSqlDemo method, add the following code to the Main method of the module: RunSqlDemo </li> On the File menu, click Save All to save the solution.</li> On the Build menu, click Build Solution.</li> On the Debug menu, click Start Without Debugging to run the application.</li> 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:

<ul> How the FixString method modifies the last name value.</li> How the modified string appears in the SQL statement.</li> Confirmation that the value is added to the database.</li></ul>

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.</li></ol>

back to the top   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. </li>  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 = &quot;Susan&quot; Dim Lname As String

Console.Write(&quot;Type Last Name (Access Sample): &quot;) Lname = Console.ReadLine

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

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

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

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

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

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(&quot;Values inserted into Employees table! (Access Sample)&quot;) Finally Try myConn.Close Catch End Try End Try End Sub NOTE: Modify the SQL Server connection string to properly reflect your environment.

</li>  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.

</li> On the File menu, click Save All to save the solution.</li> On the Build menu, click Build Solution.</li> On the Debug menu, select Start Without Debugging to run the application.</li> 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:

<ul> How the FixString method modifies the last name value.</li> <li>How the modified string appears in the SQL statement.</li> <li>Confirmation that the value is added to the database.</li></ul>

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.</li></ol>

back to the top

Troubleshooting
<ul> <li>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')'

</li> <li>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  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.</li></ul>

back to the top

<div class="references_section">