Special Character Entry in Ms Access or Sql

[bnitishpai]

I am currently working on a very simple project. But I am having problem with special characters. The one character which I find very annoying is ' . This single quote / aphostrophe is ruining my project while entering names like "James D'souza". Can anyone please help me in recording this special character in MS Access 2003 table? I need the 'insert' statement query or if not the vb.net coding along with the concerned event for recording the same. Thanks in advance.

[Reverend Jim]

In order to prevent errors on ' all you have to do is replace each ' with two '. Instead of using

name

use

name.Replace("'","''")

If you do that SQL won't complain.

[bnitishpai]

Thanks Mr. Reverend Jim. This replace function is really useful. But I just want to know whether you can still insert records containing these wildcard characters? I mean to say, is it literally possible to record the name of "James D'souza" in either SQL or in MS Access along with the aphostrophe?????

[Reverend Jim]

' isn't a wildcard character. If you take the string "D'Souza" and change it to "D''Souza" then it will go into the table as "D'Souza".

[bnitishpai]

Oh! Yeah! I never noticed that it is saved as "D'souza". That's very foolish of me. Thanks. Now while retrieving the data also, the same 'Repleace' function will be used. Thank you Mr. Jim. Discusssion solved. :)

[Reverend Jim]

You don't need to use Replace when you retrieve the value. If the value of a field is (for example) "D'Souza" then when you retrieve that value into a string, the string will contain "D'Souza". You only have to do the replace in the query string because SQL uses ' to delimit string values as in

SELECT * FROM myTable WHERE lastName = 'D''Souza'

But, if you were creating the query from a text field you would have to do

Dim query As String = "SELECT * FROM myTable " & _
    "WHERE lastName = '" & txtLastName.Text.Replace("'","''") & "'"

[bnitishpai]

Yeah, that is true. But, unfortunately, as far as SQL and Access goes, I have to write this code in each and every single place where I am expecting a D'Souza. This is inevitably the sad part which you can't avoid.

[pedders]

I beleive you dont need to put the whole code in every place, I've placed the following in my application:

    Public Function ApostopheDB(ByVal Str As String) As String
        'formats apostrophes so it doesn't cause errors
        ApostopheDB = Str.Replace("'", "''")
    End Function

Then when I'm inserting data in to the database i use the following in the SQL string eg:

Contact='" & ApostopheDB(TXTCName.Text) & "'

A little less in the way of code changes required.