HOW TO: Manage Quotation Marks in Concatenated SQL Literals by Using ADO.NET and Visual C# .NET (311021)

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) and provides a Microsoft Access sample that uses the Microsoft OLE DB Provider and related data access classes.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows XP Professional
• Microsoft .NET Framework
• Microsoft Access
• Microsoft SQL Server

back to the top

Create a Visual C# .NET Console Application

These steps demonstrate how to create a new Visual C# .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 C# 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 in this article reference the FixString method. You must include this method for either sample to function properly. To do this, add the following method to the default Class1.cs file:

private static string FixString(string SourceString , string StringToReplace, string StringReplacement)
{
	SourceString = SourceString.Replace(StringToReplace, StringReplacement);
	return(SourceString);
}
				

By default, this class is added to the project when you use Visual Studio .NET. 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: back to the top

Sample That Uses SQL Server

1. Add the System.Data.SqlClient and the System.Text namespace references to the class. If you are using Visual Studio .NET, the resulting namespace listing appears as follows:

   using System;
   using System.Data.SqlClient;
   using System.Text;
   					

2. Add the following method to the class:

   private static void RunSqlDemo()
   {
   	//Create a variable to hold the last name and the first name.
   	string fname = "Susan";
   	string lname = "";
   
   	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.
   	StringBuilder sb  = 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.
   	string myConStr = "Server=localhost;Database=Northwind;UID=myUserID;PWD=myPassword";
   	SqlConnection myConn = new SqlConnection(myConStr);
   	SqlCommand myCmd = new SqlCommand(sb.ToString(), myConn);
   
   	try
   	{
   		//Open the connection.
   		myConn.Open();
   		myCmd.ExecuteNonQuery();
   		Console.WriteLine("Values inserted into table (SQL Server Sample)");
   	}
   	finally
   	{
   		myConn.Close();
   	}
   		
   }
   						

   NOTE: Modify the SQL Server connection string for your environment.
3. To call the RunSqlDemo method, add the following code to Main:

   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

Sample That Uses Access

1. Add the System.Data.OleDb and the System.Text namespace references to the class. If you are using Visual Studio .NET, and if you already created the SQL Server sample in the previous section, the resulting namespace listing for your class file appears as follows:

   using System;
   using System.Data.SqlClient;
   using System.Text;
   using System.Data.OleDb; 
   					

2. Add the following method to the class:

   private static void RunAccessDemo()
   {
   	//Create a variable to hold the last name and the first name.
   	string fname = "Susan";
   	string lname = "";
   
   	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.
   	StringBuilder sb = 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.
   	string myConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
   			  @"Data Source=C:\MyDatabases\NWIND.MDB";
   
   	OleDbConnection myConn = new OleDbConnection(myConStr);
   	OleDbCommand myCmd = new OleDbCommand(sb.ToString(), myConn);
   
   	try
   	{
   		//Open the connection.
   		myConn.Open();
   		myCmd.ExecuteNonQuery();
   		Console.WriteLine("Values inserted into Employees table! (Access Sample)");
   	}
   	finally
   	{
   		myConn.Close();
   	}
   }
   						

   NOTE: Modify the connection string to properly reflect your environment.
3. To call the RunAccessDemo method, add the following code to Main:

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

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