access query to allow apostrophes

[Johnny26652]

Is it  possible to use the replace function in the sql that generates the recordsource for the table so that apostrophes are allowed, but won’t cause an error in the recordsource? any help?

thanks

[rdivilbiss]

ASP or PHP

[rdivilbiss]

INSERT INTO companys ( company, address )
VALUES (Replace("O'Brians Tavern","'","''") , "123 Any Street")

That's Replace("O'Brians Tavern","single quote","single quote single quote")

[Johnny26652]

i am using replace function in asp to replace apostrophes, double quotes but i was told to do it with sql query.. so i can save a name say O'Malley   as O'Malley in the table..also any other double quotes or period or question mark?

[rdivilbiss]

Well I gave you the query, but that stores O''Mally in the database.

If you use parameterized SQL, you don't have to worry about it.


<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
Set cmd = Server.CreateObject("ADODB.Command")
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open "database connection string"
Set cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO table (name) VALUES (@name);

Set param = cmd.CreateParameter("name",adVarChar,adParamInput,CLng(40),"O'Mally")
cmd.Parameters.Append param

cmd.Execute numAffected,,adExecuteNoRecords

[Johnny26652]

I am using access.. i am not familiar with parameterized SQL, does it work with access? where can i learn more about it?

my textbox may contain double quotes, question mark  something like that how can i handle stuff like that?

thanks rod

[rdivilbiss]

parameterized SQL works with access.   Being a web developer, and based on previous questions I was assuming a web form is involved here.  Is that correct?

[Johnny26652]

You are correct!!  where do i start now?

[rdivilbiss]

http://www.rodsdot.com/ee/parameterized_sql.asp

http://www.rodsdot.com/ee/parameterized-sql-library.asp

[Johnny26652]

thanks rod. i will go through your articles.. i am sure i have seen the second link tutorial in master/detail topic in asp section but wasn't sure if it was for access... again appreciate your help.

[rdivilbiss]

This uses access and calls the param.. SQL directly...no library.

Of course I like to hide the gory details with the library, which was link two.

[rdivilbiss]

Here is recordset retrieval using the library.  (Scroll down for the code)

http://www.rodsdot.com/ee/database3.asp

Another recordset retrieval using a parameter.

http://www.rodsdot.com/ee/access_checkbox.asp

Getting a recordset and performing an update using the library

http://www.rodsdot.com/ee/access_checkbox1.asp

That should be enough to get you going.

[rdivilbiss]

INSERT INTO  companys (company) VALUES (Replace("The apostrophe O'Mally and O'Brian Tavern","'","''"));

Stores:

The apostrophe O''Mally and O''Brian Tavern

for me.

That is a valid string... e.g. double quote The apostrophe O single quote Mally and O single quote Brian Tavern double quote.

Your post was

single quote single quote the apostrophe o single quote single quote mally and double quote O single quote Brian single quote Tavern

which is kind of a weird string, so I have no idea what you would get from access.

[Johnny26652]

test ''the apostrophe o''mally and "O'Brian' Tavern

Function testApostrophe(str)

   If Not isNull(str) Then
      str = replace(str,"'","''")
   End If
   CQuote = str
End Function


stores  in the database as

test the apostrophe omally and OBrian Tavern


no quotes at all..


what i want my access query do is store O'Mally as O'Mally and not O''Mally

is it possible? thanks rod

[rdivilbiss]

No, without parameterized queries you'll have to store O''Malley and upom retrieving the recordset do

Replace(rs{name),"''","'")

That is Access and MS SQL's way of escaping the single quote.

Other database engines may do that differently, but you're stuck with the design of Access.

The reason you can do it with parameterized queries is the parameter @name = O'Malley is not treated as part of the SQL.

The way you are doing your query O'Malley IS part of the SQL command and must be escaped to avoid breaking the query.

This is not unlike, in VBScript/ASP id you want a double quote in your string you have to write:

"Quote ""It is better to say nothing an be thought a fool, than to open your mouth and remove all doubt"""

Rod