We help IT Professionals succeed at work.

Apostrophes Again, ARGH....

Medium Priority
559 Views
Last Modified: 2012-05-12
Now I can't escape apostrophes in this code...and why is the database accepting them without throwing an error?

	sRprtCmnts = Request.Form.Item("rprt_cmnts")
	If Not sRprtCmnts & "" = "" Then sRprtCmnts = Replace(sRprtCmnts,"'","''")
	
	sql = "INSERT INTO GrdbkRprts (ClassesID, RprtName, RprtType, RprtStyle, PrntView, RprtCmnts, Generated, SchoolSubTermsID) VALUES ("
	sql = sql & lThisClass & ", '" & sRprtName & "', 'Progress Report', '" & sRprtStyle & "', '" & sPrntView & "', '" 
	sql = sql & sRprtCmnts & "', '" & Now() & "', " & lThisSubTerm & ")"
	Set rs = conn.Execute(sql)
	Set rs = Nothing

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
Missing '

sql = "INSERT INTO GrdbkRprts (ClassesID, RprtName, RprtType, RprtStyle, PrntView, RprtCmnts, Generated, SchoolSubTermsID) VALUES ("

should be

sql = "INSERT INTO GrdbkRprts (ClassesID, RprtName, RprtType, RprtStyle, PrntView, RprtCmnts, Generated, SchoolSubTermsID) VALUES ('"
Bob SchneiderCo-Owner

Author

Commented:
I disagree, I think...since the first field is always numeric the single quote should not be needed on either side of the value, right?  Note that a single quote does not follow the value...  Please let me know if I am wrong.  If I am right then is there an issue with the db set-up?  This is how I escape all of my single quotes!
CERTIFIED EXPERT
Commented:
Actually you are right. I misread the first param as being the report comments.

Before you execute the query, output it to the screen with a response.write to see what it contains. Then paste that into SQL management Studio to see if the query runs.
Bob SchneiderCo-Owner

Author

Commented:
It is returning what it should return but it is showing single quotes in the db.  Once I save the report that it is correcting it "fixes" it.  Must be something somehwere else in my code.  Probably crossing up variables or whatever.

Thanks for your help...
CERTIFIED EXPERT

Commented:
No problem.

Sometimes you've gotta hate code. I do it for a living and it does my head in!
Bob SchneiderCo-Owner

Author

Commented:
Ok now I am really puzzled.  Check the attachment.  The first image is my code, with response.write...the second image is the screen shot which appears to be writing exactly what I want it to write..the final image is what the db shows... apost-issue.docx apost-issue.docx
CERTIFIED EXPERT

Commented:
This is correct. When you put two single quotes together in a string and send to the database like that the one escapes the other so that when it is inserted into the database, SQL strips one off as it is used as an escape character. So what you see stored is correct.

If you want two quotes stored you need to send 4 across. 2 for the quotes and 2 to escape them.

The easiest is to have SQL replace the single quotes with 2 single quotes when you read it out and then do what you're doing now to write it in.

Bob SchneiderCo-Owner

Author

Commented:
I did not know that...sometimes when I look in the db there are double quotes and sometimes there are not.  Thanks for the insight...

Explore More ContentExplore courses, solutions, and other research materials related to this topic.