HOWTO: Insert Into SQL with Embedded Single Quotes from ASP

ID: Q190742

The information in this article applies to:

Microsoft Data Access Components
Active Server Pages
Microsoft OLE DB Provider for SQL Server
Microsoft Internet Information Server versions 4.0, 5.0

SUMMARY

This article shows how to place text strings into a SQL Server table that contain 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
   %>

REFERENCES

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q152620 BUG: Inserting to a 255-byte String w/ Embedded quot

Q147687 HOWTO: Query for Literal Special Characters in a Where Clause

Q113901 VB3 Querying for Literal Special Characters in Where Clause

Q178070 HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals

152620 147687 113901 178070

Additional query words:

Keywords : kberrmsg kbADO kbASP kbDatabase kbScript kbSQLServ kbGrpASP kbCodeSnippet kbiis400 kbiis500
Version : WINDOWS:; winnt:
Platform : WINDOWS winnt
Issue type : kbhowto