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

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

   <%
   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
   %>