Problem with passing text field that includes single quotation marks?

Do something like this...

' Classic ASP Example
' <summary>Takes in a string and properly cleans it for insertion into SQL.</summary>
' <remarks>This will work withNULL values and will also add single quotes surrounding the string data.</remarks>
Public Function CleanSql(ByRef data)
If (IsNull(data)) Then
CleanSql = "NULL"
Else
CleanSql = "'" &amp; Replace(data, "'", "''") &amp; "'"
End If
End Function

or


'ASP.net Example
' <summary>Takes in a string and properly cleans it for insertion into SQL.</summary>
' <remarks>This will work with NULL values and will also add single quotes surrounding the string data.</remarks>
Public Function CleanSql(ByVal data As String) As String
If (data Is Nothing) Then Return "NULL"
Return String.Format("'{0}'", data.Replace("'", "''")
End Function


Then change your code to this...

query ="insert into S1_Demographics_Data (S1_Q1_Company, S1_Q1_Code, S1_Q1_text"
query = query &amp; ")"
query = query &amp; " VALUES"
query = query &amp; "("
query = query &amp; CleanSql(Request.Form("S1.Q1.Company")) &amp; ", "
query = query &amp; CleanSql(Request.Form("S1.Q1.Code*")) &amp; ", "
query = query &amp; CleanSql(Request.Form("S1_Q1_text"))
query = query &amp; ")"











Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

Post Edited (Joel Thoms) : 3/9/2006 10:24:45 PM GMT

 

Hi guys I am somewhat desperate and have no idea how to solve this.
I am an asp newbe no doubt.

Now I am trying to make a textarea field insert into my access database. I can do that normally but
I get an syntax error if I include (') single quotation marks as is the case for example with 'don't'.

Can anyone help, to somehow avoid this single quotation problem. Even if I was to replace this character with another one such as a '.' would be ok!!!

Here is my code:

<!--ASP CODE INSERTION AND FORM COMPLETION -->
<%
Dim connection, query, data, input, store, dbpath

Set connection = Server.CreateObject('ADODB.Connection')

Call connection.Open('davidpender_D1_Survey')


'Section 1 input code
query ='insert into S1_Demographics_Data (S1_Q1_Company, S1_Q1_Code, S1_Q1_text'

query = query & ')'
query = query & ' VALUES'
query = query & '('
query = query & ' ' ' & Request.Form('S1.Q1.Company') & '', '
query = query & ' ' ' & Request.Form('S1.Q1.Code*') & '', '
query = query & ' ' ' & Request.Form('S1_Q1_text') & '''
query = query & ')'

connection.Execute query
call connection.Close()
%>

Now S1_Q1_text is field of concern

 

Thanks will certainly give it shot!!!