Microsoft KB Archive/190742

= How To Insert Text String into SQL Table with Embedded Single Quotes from ASP =

PSS ID Number: 190742

Article Last Modified on 7/1/2004

-

The information in this article applies to:


 * Microsoft Active Server Pages
 * Microsoft OLE DB Provider for SQL Server
 * Microsoft Internet Information Server 4.0
 * Microsoft Internet Information Server 5.0

-



This article was previously published under Q190742



SUMMARY
This article shows how to place text strings into a SQL Server table that contains embedded single quotes. When placing a text string into a SQL Server table that contains an embedded single quote, a second single quote must be placed adjacent to it. If this is not done, SQL Server assumes that the text string ends at the first single embedded quote causing an error similar to:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax

near ...

For example: The word "Don't" must be formatted 'Don''t' to be inserted correctly into a SQL Server table.

The two single quotes adjacent to each other is interpreted by SQL Server as one single embedded quote.



MORE INFORMATION
The following function (written in VBScript) will format and return any string sent to it with the appropriate quoted format.

<%  Function padQuotes( instring ) REM This function pads an extra single quote in strings containing quotes for REM proper SQL searching.

Dim bodybuild Dim bodystring Dim Length Dim i

bodybuild = "" bodystring = instring Length = Len(bodystring) For i = 1 to length bodybuild = bodybuild & Mid(bodystring, i, 1) If Mid(bodystring, i, 1) = Chr(39) Then bodybuild = bodybuild & Mid(bodystring, i, 1) End If  Next bodystring = bodybuild padQuotes = bodystring End Function %>

