Apostrophes causing syntax errors

[goldfische]

Hello,
I have a question about retrieving last names from an access 2000 database that contain an apostrophe ', like O’Connor. Is there a simple way to have VB bypass all the apostrophes in the database so VB does not think it is a typo in the sql? Thank you for any help given.

 

[CCLINT]

Use an ADODB Cammand and Parameter object.

Or, replace the surrounding single quotes with double quotes.

Instead of:
"...WHERE Name='O'Conner'"

use:
"...WHERE Name=""O'Conner"""

This will then send:

...WHERE Name="O'Conner"

to the provider. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[goldfische]

Hello again,
I didn't think to add this information at the time of my question (I'm sorry) but I am choosing the clients names that are concatenated in a combo box, and then fill the appropriate text boxes on the form. Here is the code I am using to compare the users chosen client to fill the text boxes, but if the user chooses the one with a last name that contains an apostrophe this is where I get the syntax error. Every other name without any syntax works fine. Thank you CClint for the fast reply

'clientsql statement
"Where clients.LAST_NAME= '" & Mid(cmbxNames.Text, 1, InStr(1, cmbxNames.Text, ",", vbTextCompare) - 1) & "' " & _
"AND clients.FIRST_NAME= '" & Mid(cmbxNames.Text, InStr(1, cmbxNames.Text, " ", vbTextCompare) + 1) & "' "

 

[johnwm]

Replace single apostrophe with 2 apostrophes. For O'Connor send O''Connor (That's 2 apostrophes not a quotation mark)

If you are getting parameter from textbox use something like:

strSQL = "Select * From tblThis Where fldName = '" & Replace(Text1.Text, Chr(39), Chr(39) & Chr(39)) & "';"

I set up Global constants for this:

Public Const gstrTick As String = "'"
Public Const gstr2Tick As String = "''"
then the Replace becomes :
...Replace(Text1.Text, gstrTick, gstr2Tick) ...
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

Of course there are several ways of doing this, under different conditions.

And, what happens if I also have data with double apostrophes or data with double quotations, or if you change providers?

Use an ADODB Cammand and Parameter object and you will not have to replace anything.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CassandraR]

Hi

Here's another suggestion, one that I have used with textboxes.

txtClient.Text = Replace(txtClient.Text, "'", Chr(180))

The Chr(180) changes the standard apostrophe to an apostrophe that VB doesn't recognize as an apostrophe. Before reading a textbox and stuffing it into a database record, I convert apostrophes. When reading from the database, do no conversions -- treat Chr(180) as Chr(180) in you strings.

Cassie

 

[goldfische]

All of the replies have been most helpful, I have everything reading in and out including Apostophes, I ended up using Global constaints as John suggested. I will try chr(180) for inputs on text boxes too. Thank you all again.