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:
For example: The word "Don't" must be formatted 'Dont' 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
%>
REFERENCES
For additional information, please see the following article(s) in the Microsoft Knowledge Base:
Keywords: kbCodeSnippet kbDatabase kberrmsg kbhowto kbScript KB190742
Technology: kbASPsearch kbAudDeveloper kbiis400 kbiis500 kbiisSearch kbOLEDBProvSearch kbOLEDBSearch kbSQLServSearch
