asked on

Apostrophes Again, ARGH....

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

Lee

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 Schneider

ASKER

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!

ASKER CERTIFIED SOLUTION

Lee

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Bob Schneider

ASKER

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

Lee

No problem.

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

Bob Schneider

ASKER

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

Lee

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 Schneider

ASKER

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