ASP characters breaking queries.

How to get around the problem with ' " and?

[chris_f]

Ok,

My problems are beginning again (sign). I've got a problem. It you all know access then an insert query would look like this.

INSERT INTO TableName (field1, field2, field3) VALUES (NumberValue, AnotherNumber, 'String')

I have to use single quotes because running the query in ASP would require double quotes to surround the query like below.

Set CnnRSIns = Server.CreateObject("ADODB.RecordSet")
CnnRSIns.ActiveConnection = Cnn
CnnRSIns.Source="INSERT INTO TableName (Field1, Field2) VALUES (" & Request.Form("Field1") & ", '" & Request.Form("Field2") & "')"
CnnRSIns.Open
Set CnnRSIns = Nothing

The problem is if someone uses a character like ' " or? it breaks the query. How do you guys get around this. For instance, in our posts here we use the symbols. Why don't they break the program. Do I have to find a way crawling the string and replacing them with HTML 4 Entities like '&xxxx;'. If so, are you any resources that would help me learn to do this (are there any examples I can look at)?

Am I missing something obvious? Is there an easier way around this?

Chris

[Woz]

yeahm that really vexxed me for a while.

You need to replace errant characters with their character number. Such as

Replace(Request.Form(strField), "'", "' & Chr(39) & '")

The major ones are
' = Chr(39) and
, = Chr(44)

Double quotes can also be a problem and requires either 4 or 6 cdouble quotes in a line to fix, something like
Replace(Request.Form(strField), """", """""")

I forget exacly how many, perhaps someone can remind me/us.

Onya
Woz

[chris_f]

Cheers Woz,

I'll give that a go shortly.

Chris

p.s. I didn't see you down the pub.

[Woz]

>p.s. I didn't see you down the pub.

Pub in UK. Woz in Australia. Too much water inbetween. <sniff>

One day.....

Onya
Woz

[chris_f]

gggrrrrrr,

The problem remains. If I use

Replace(Request.Form("CContent"), "'", "'" & Chr(39) & "'")

I get the error message

Cannot use parentheses when calling a Sub /rewiredtest/sas/addpage.asp, line 65, column 59
Replace(Request.Form("CContent"), "'", "'" & Chr(39) & "'")

I have checked the MSDN knowledge based an it said I should use

Call Replace(Request.Form("CContent"), "'", "'" & Chr(39) & "'")

however, this produces the error

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''Page Content'''.

Anyone have any ideas?

Chris

[Woz]

I think you have too many " in the line. Have another look at the line I suggest. That works on my system, ASP on PWS or IIS.

Onya
Woz

[chris_f]

Copied ... Pasted ... Working

Thanks Woz

Chris

[Dreamquick]

Replace is a function and not a sub so you need to use it like this;

sSQL = Replace( sSQL, "'", "''" )

Otherwise it doesn't do anything.

<added>
Actually if you were using it inline eg

.Open( "SELECT * FROM MyTable WHERE TextField = '" & Replace( sUserInput, "'", "''" ) & "'; )

then that would also work, but you would have problems if you put the following on a line of its own

Replace( sUserInput, "'", "''" )

</added>

- Tony

[Paully]

One suggestion: before executing newly designed SQL, I like to print it to the screen or a debug window if one is avail.

That way you can see exactly what is going on, make your SQL statement perfect and avoid those nasty SQL errors :)

[Woz]

>Replace is a function and not a sub so you need to use it like this;

True, I was assuming that was understood which is perhaps not the best assumption to make. My apologies.

Onya
Woz

[JuniorHarris]

I forget exacly how many, perhaps someone can remind me/us.

Many of the special characters can be found here [hotwired.lycos.com].