Access/VB Question

[kmcivor]

I have an .asp page that gathers data from users and enters it into an Access database. The input fields are 'text' and the database fields are 'text'. But if a user puts some sort of punctuation such as an appostrophy or comma Access returns an error: "Syntax error (missing operator) in query expression "
Do I need to replace select characters with ascii?

[parksie]

Try putting quotes (") around the string they supply.

[kmcivor]

Here's the code:
strSQL = "UPDATE Customer SET WirelessNumber = '" & Input_text & "'

where whould additional quotes go?

The problem occurs when Input_text has a quote, apostrophy or comma. Something like 'today's entry'.

[parksie]

Try surrounding it in double-quotes.

[kmcivor]

As you can see strSQL is a concatenation of strings and variables. I can't double quote the whole thing because the concatenations won't work.
I tried triple quoting the one variable I'm having trouble with (" " " & variable & " " ") but that results in a MS Access error.
I also tried enclosing the single quotes in double quotes (" ' " & variable & " ' ") but VBScript treats everything after the first single quote as a remark or comment.

The SQL works fine if I enter it directly into Access.

H
e
l
p
.
.
.

do this:

Input_text = Replace (Input_text, "'", "''")

strSQL = "UPDATE Customer SET WirelessNumber = '" & Input_text & "'

It doesn't look proper here so I'll describe what I'm doing. I'm replacing each single quote in Input_text with two single quotes.

I'm not sure what all the reserve characters in Access are, but you may also want to just create a function that will handle all reserved characters.

An example (for SQL server) is:

Input_text = FixStr(Input-text)

Function FixStr (strSQL as string)

FixStr = Replace(strSQL, "'", "''") 'replace single quote with two single quotes
FixStr = Replace(FixStr, "%", "[%]") 'use %as literal
FixStr = Replace(FixStr, "_", "[_]") 'use underscore as literal

End Function

It also looks like you don't have the last single quote in

strSQL = "UPDATE Customer SET WirelessNumber = '" & Input_text & "'

inside the double quote, it should be

.....Input_text & '"

[kmcivor]

Thanks! That fixed it. Your explanation was quite clear. I appreciate your help.

"..So Arnie says to Jack..."