ASP /. SQL does not like an apostrophe

when building your insert statement, you must replace the single apostrophes with double apostrophes...

so, like this:

comment = Request.Form("txtComment")

sSQL = "INSERT INTO myTable (comments) VALUES ('"&replace(comment, "'", "''")&"'"

regards,
peh803

the reason, of course, being that a single quote will break a sql string unless escaped -- the escape syntax for single quotes in T-SQL is just to make each occurrence of once single quote into two single quotes.

For years of VB and ASP coding, I've had a little function called TrimSQL. All this does is replacement of single quotes with two single quotes. I use TrimSQL on every sql call, thus:

rs.open TrimSQL("blah blah blah")
conn.execute TrimSQL(sSQL)

<%
function TrimSQL(sSQL)

      TrimSQL = trim(replace(sSQL,"'","''"))

'-- debug code after here
'e.g.:  response.write "<br>SQL call @" & now() & ": " & sSQL & "<br>"

end function
%>

Using a function in this way, and EVERY time you talk to the database means you'll never need to worry about the 'ol Apostrophe problem again. Additionally, TrimSQL can be modified for debugging purposes, for instance, to log every SQL call made by your code. Just put TrimSQL in with your other common functions and <!--#include the file on every asp page.

rmsewan,

I like that function.  Can you help me a little bit more as to how I can implement it into my code?  I have a stored procedure that does the insert.  Here is part of the code:

Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")


strSQL = "spInsert" & chr(39) & name& chr(39) & ", " & chr(39) & comment & chr(39)
objRS.Open strSQL, objConn, , adCmdStoredProc

Allow me to adjust the code for you, but please give alll the points to rmsewan:


Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")


strSQL = "spInsert" & chr(39) & name& chr(39) & ", " & chr(39) & comment & chr(39)
objRS.Open TrimSQL(strSQL), objConn, , adCmdStoredProc



Of course, you need to add this to your file as well:

function TrimSQL(sSQL)

      TrimSQL = trim(replace(sSQL,"'","''"))

'-- debug code after here
'e.g.:  response.write "<br>SQL call @" & now() & ": " & sSQL & "<br>"

end function

I take it that your stored proc is returning a resultset, hence your use of a recordset to execute the proc? I'm Oracle for the most part and we don't have the ability to easily return result sets from procedures, so I always use conn.execute

strSQL = "spInsert '" & TrimSQL(name) & "','" & TrimSQL(comment) & "'"
(i don't see why you're throwing the apostrophes in with CHR(39) )

Now, it would be possible to write a much smarter function that would parse and fix an entire SQL statement for you - but I've never had the need.

rmcewan,  Thank you for correcting me!  I feel like a total idiot for doing objRS.Open TrimSQL(strSQL)!!!

And thank you too divt.