Apostrophe

[atimo]

I am using the following code in my pages"


VALUE = request.form("user_input")

Var_insert = "Insert into TABLE values (" + VALUE +")"

set rsTable = SqlData.Execute(var_insert)

It works perfectly most of the times, but if the user uses the apostrophe in his input (ie: I'm, he's, etc...) I get an SQL error.

To avoid that error I am extracting the apostrophes with
VALUE = replace(VALUE,"'",""), but that is not the solution I want, once the database will not be able to store a correct English spelling.

Can I get some help with this issue?

[chil]

This should solve your problem:

SQLName= Replace(TestName, "'", "''")

This is:
Replace(TestName, [double quote][single quote][double quote] , [double quote][single quote][single quote][double quote])

The single quote is used/intended to be used as a delimiter for text strings. However when the delimiter is required to be part of the text string it must be preceeded by another single quote to force the required single quote to be taken as a string character and not a delimiter.

I hope this helps.

[atimo]

Yes! It helped.

The solution was easier than I expected. Thank you very much.

Does this rule apply to other characters as well?
I am having trouble with the % character too.

[chil]

No, this does not always work as it also matters where the deliminator is being used. If the deliminator is being used within a field name then the field name should be encapsulated within open and closing brackets [] .

Experiment with it, you might be surprised with the results.

Try the %, ", and ' as in:

85%

168"

168'- 4 3/4"

all the above are to be taken as string values.

[atimo]

I am still having trouble with the % sign.

I am trying to build a SQL statement in the code, and it ignores the % sign.

Here it is:

var_where = var_where + " and " + Var_label(var_n) + " like '%"+ Var_field(var_n)+ "%' "


It returns something like this:

and field like 'value'

when it should return

and field like '%value%'


What can I do?

[chil]

var_where = var_where + " and " + Var_label(var_n) + " like '%"+ Var_field(var_n)+ "%' "

You can also try:

var_where = var_where + " and " + Var_label(var_n) + " like '" + chr(37) + Var_field(var_n) + chr(37) + "'"

I find that if I hardcode the special characters, I do not have any problems. However, I am not sure if this works in every condition.

Hal

[atimo]

Once again thank you very much.

I did what you said, and it worked.


Have a nice day.