RE Quotation marks

  • Thread starter Thread starter LP
  • Start date Start date
L

LP

Hi,

I need to use ' or " in my query. Access allows me to key in eg "Mother's
boy" but when using the find command to search for the field with ' or "
VB gives errors. eg: rs.find "name = '" & field & "'"
and if the field contains ' or " VB stopped working.

I remember you need to use double "" if you there is a " in your record.
I've forgotton about the rules. Can someone help?

thanks
 
The simplest way around your delima is to use paramaterized queries. This
technique is already build into ADODB and ready for your use. In this way,
you will not have to remember the rules for quotes, dates, numbers or the
like.

Another way to manage strings is with the assistance of a helper function.
You simply carry it with you in all your code projects, then you won't have
to remember the rules.

public function FixSQLString(input as string) as string

'Remember to test for stand alone semicolons as they can present potential
SQL Injection Attacks.

dim sTemp as string = input

'Close single quotes
stemp = stemp.replace("'", "''")

'Close double quotes
stemp = stemp.replace("""", """""")

return stemp

end function

Now when you are building a SQL query or a datatable filter, you can simply
pass the test value to the function first

rs.find("name = '" & FixSQLString(field) & "'")

or even better

rs.find (string.format("name='{0}'", FixSQLString(field))

I challenge your use of the ADO 2x-3x library instead of ADODB.net 1x-2x
libraries. Both support paramterized queries but only ADODB will give you
rich native support in .Net and follow along the general thinking of
development in MS technology space.
 
Thanks Amdrit. It works.

AMDRIT said:
The simplest way around your delima is to use paramaterized queries. This
technique is already build into ADODB and ready for your use. In this
way, you will not have to remember the rules for quotes, dates, numbers or
the like.

Another way to manage strings is with the assistance of a helper function.
You simply carry it with you in all your code projects, then you won't
have to remember the rules.

public function FixSQLString(input as string) as string

'Remember to test for stand alone semicolons as they can present potential
SQL Injection Attacks.

dim sTemp as string = input

'Close single quotes
stemp = stemp.replace("'", "''")

'Close double quotes
stemp = stemp.replace("""", """""")

return stemp

end function

Now when you are building a SQL query or a datatable filter, you can
simply pass the test value to the function first

rs.find("name = '" & FixSQLString(field) & "'")

or even better

rs.find (string.format("name='{0}'", FixSQLString(field))

I challenge your use of the ADO 2x-3x library instead of ADODB.net 1x-2x
libraries. Both support paramterized queries but only ADODB will give you
rich native support in .Net and follow along the general thinking of
development in MS technology space.
Click to expand...
 
Back
Top