Hello,

i have found a problem in my program that has stumped me. If I type in a name with an apostrophe, for example o'hare then I get the following error:

runtime 3075
syntax error (missing operator) in query expression ' surname ='o'hare'

otherwise the program runs fine.

I imagine it is something to do with the apostrophe in the following statement:

"SELECT * FROM query1 " & " WHERE surname = '" & txtsurname.Text & "'" & " AND forename = '" & txtForename.Text & "'" & " and year = '" & txtYear & "'", dbOpenDynaset)

Can I use a different expression and what is the apostrophe for?

Thanks in advance

neil

That is a common problem, SQL uses the apsotrophe to surround strings.

So for example if your have a query like this

"SELECT * FROM query1 " & " WHERE surname = '" & txtsurname.Text & "'"

It becomes SELECT * FROM query1 WHERE surname='o'hare'

SQL doesn't understand the extra hare' and it becomes an error.

Either remove the single quotations or surround your strings with # instead of ' (Of course the same problem will occur if you have # in your string)

http://www.geocities.com/scirocco_ha/DataControl.htm

Hi,

I tried replacing ' with # but I just get the same error. If I remove the single quotation I get an error saying too few parameters.

any ideas?

cheers
neil

I think from memory (you'll have to play around with it a bit), but:
Try using " then ' for o'hare eg
"SELECT * FROM query1 " & " WHERE surname = """ & txtsurname.Text & """
or
"SELECT * FROM query1 " & " WHERE surname = """" & txtsurname.Text & """"

Brian.

I'd encode the txtsurname.Tex to have all single quotations doubled (in your case you'll get text - o''hare instead of o'hare) .


changing ' with " may help if you're shure tht you don't have text wtih " in txtsurname.Text , but if you use sinqle qutations to construct SQL and encode criteria text, you won't have problems in any case (even when user uses ' or " chars in text)