[RESOLVED] SQL Search with ' in the String Variable

[Hojo]

Hello All,

I have a program that displays / edits the company and contact details of our customers.

I have a simple SQL line that retreives all the contacts at a specific company when that company is selected.

ADOContacts.RecordSource = "Select [SURNAME], [FIRST NAME], [COMPANY], [CONTACT ID] FROM Contacts WHERE Contacts.COMPANY='" & LblCompanyName.Caption & "' ORDER BY [SURNAME]"

I have now found a limitation I didn't think about where we have a customer with an apostrophe in it's name. EG. Mary's Company.

So when the SQL query gets sent with this customer selected I receive an error because of the extra apostrophe.

"Select [SURNAME], [FIRST NAME], [COMPANY], [CONTACT ID] FROM Contacts WHERE Contacts.COMPANY='Mary's Company' ORDER BY [SURNAME]"

It thinks the company name is 'MARY' and wonders why the s Company' is there.

Is there a way to avoid this error or do i just need to not allow aphostrophes in the Company Name field?

Thanks for your help,

Cheers,

Hojo.

[LaVolpe]

Handle apostrophes with a Replace() call:

ADOContacts.RecordSource = "Select [SURNAME], [FIRST NAME], [COMPANY], " & _
        "[CONTACT ID] FROM Contacts WHERE Contacts.COMPANY='" & _
        Replace$(LblCompanyName.Caption, "'", "''") & "' ORDER BY [SURNAME]"

You want to use double apostrophes in this case

[Hojo]

Thanks LaVolpe.

Works a treat.

I love simple solutions.

Thanks again,

Cheers,

Hojo

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

While it may seem that the issue is solved, there are many more waiting for you - especially if somebody malicious uses your program.

For further explanation (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).