'80040e14' Need help removing single quotes

[dburnham]

I have created the following code based on reaserching how to send Form responses to a DB and an e-mail at the same time. I am almost there but have hit a roadblock. I now receive the following error:

*******BEGIN ERROR********
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''magoo''675'test description for the confirmation screen'','','DB Web Solutions','','0')'.

/submission_form_test.asp, line 115
**********End Error*******

I have fiqured out that the error is a result of the single quotes coming from the query. I even found a code snippet to fix this. However, I am not sure that I know how/where to apply the snippet. I will paiste the snippet and my code below. Appreciate any assistance that anyone can offer.

David Burnham

******CODE SNIPPET**********
<%
Function SafeSQL( _
ByVal strToRenderSafe _
)

SafeSQL = Replace(strToRenderSafe, "'", "''")

End Function
'
' ...
'
strVar = Request.Form("txtField1")
strVar =SafeSQL(strVar)
%>
*******END SNIPPET********


*********MY CODE IS*******
<%

Function ParseBody(strText)
'=================================================
' This function replaces the Chr(13) with a <br>
' tag in whatever string is passed to it.
'=================================================
strText = Replace(strText, Chr(13), "<br>")
ParseBody = strText
End Function


Dim myConnString
Dim myConnection
Dim mySQL

'========================================================

myConnString = Application("paypal_invoice_ConnectionString")

'========================================================
Set myConnection = Server.CreateObject("ADODB.Connection")

'========================================================

myConnection.Open myConnString

'========================================================

mySQL= "INSERT INTO InvoiceTable "
mySQL= mySQL & "(invoicenumber,invoicepicture,Password,invoicelocation,amount,paypal_shipping_currency,description,paypal_url,paypal_price,company,paypal_item,paid) "
mySQL= mySQL & "VALUES ('" & Request.Form("invoicenumber") & "','"
mySQL= mySQL & Request.Form("invoicepicture") & "'"
mySQL= mySQL & Request.Form("Password") & "'"
mySQL= mySQL & Request.Form("invoicelocation") & "'"
mySQL= mySQL & Request.Form("amount") & "'"
mySQL= mySQL & Request.Form("description") & "'"
mySQL= mySQL & Request.Form("paypal_url") & "'"
mySQL= mySQL & ",'" & Request.Form("paypal_price") & "'"
mySQL= mySQL & ",'" & Request.Form("company") & "','"
mySQL= mySQL & Request.Form("paypal_item") & "','"
mySQL= mySQL & Request.Form("paid") & "')"

'========================================================

myConnection.Execute mySQL

'=== Close the connection.
myConnection.Close

'=== Set the connection equal to Nothing.

Set myConnection = Nothing




'===================================================================
' Send the results to e-mail.
' Use CDONTS to create and send a message based on information
' entered into the form. The following lines compose and send
' the e-mail.
'===================================================================


Dim myCDONTSMail
Dim strFrom
Dim strTo
Dim strSubject
Dim strBody

strFrom="dburnham4739@yahoo.com"
strTo="david@db-websolutions.com"
strSubject = "Send to E-mail and Database fro Automated Facility"


strBody="The following information was submitted:" & Chr(13)
strBody = strBody & Request.Form("invoicenumber") & " "
strBody = strBody & Request.Form("company")
strBody = strBody & Chr(13) & Request.Form("Password") & Chr(13)
strBody = strBody & Request.Form("description") & Chr(13)
strBody = strBody & Request.Form("amount") & Chr(13)
strBody = strBody & Request.Form("invoicepicture") & Chr(13)
strBody = strBody & Request.Form("paid") & Chr(13)
strBody = strBody & Chr(13) & "Thank you for submitting your data."

'====================================================================
' The SET statement creates the CDONTS mail object in preparation
' for sending the e-mail message.
'====================================================================
Set myCDONTSMail = CreateObject("CDONTS.NewMail")

'====================================================================
' The following line sends the mail message using the source e-mail,
' destination e-mail, subject, and body that were defined earlier.
'====================================================================
myCDONTSMail.Send strFrom,strTo,strSubject,strBody

'=== Set the CDONTS mail object to NOTHING to free resources.
Set myCDONTSMail = Nothing



%>

</head>

<body bgcolor="#FFCC99">

<p><font face="Verdana" color="#FF0000"><b>Thank you for submitting your
information!<br>
</b></font><font face="Verdana" size="2">You will receive an e-mail
shortly.&nbsp; The e-mail was sent using the following information:</font></p>
<b><b><font face="Verdana" size="2">Sent To:
<% '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Response.Write Request.Form("company")
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%><br>
From&nbsp;&nbsp;&nbsp; : Microsoft PSS Sample Page</font></b></p>
<p><b><font face="Verdana" size="2">Subject: Send to Database and E-mail</font></b></p>
<p><b><font face="Verdana" size="2">Content:

<% '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Call the ParseBody function and pass the strBody string to it.
' This will replace the Chr(13) characters with <br> tags in the HTML.
Response.Write(ParseBody(strBody))
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%>
</font></b></p>
<hr noshade size="1" style="color: #000000">
<p>&nbsp;</p>

</b>

</body>

</html>

 

[Genimuse]

You need to replace every instance of a single quote -- ' -- with two single quotes in a row -- '' -- when you store it in the database. When you read it back out it will come out as one single quote.

So you'd put your SafeSQL function around every instance of text being inserted into the database -- that is, any time you're creating a sql string you'll want to put the SafeSQL function around anything inside single quotes (any text being inserted). Like:

mySQL= mySQL & ",'" & SafeSQL(Request.Form("company")) & "','"