Inserting special characters into Access 97

Sach · March 24th, 2004, 10:36 AM

Hi

I'm trying to insert a ' from a web form into a table in Access but keep getting a sytax error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''SACHIN'S', 'SSS', 'SSSS', 'SSSS', 'NA', 'NA', 'NA', 'NA', 'NA')'.

Is there any way around this problem??

Any help is much appreciated. Thanks in advance.

jacob · March 24th, 2004, 10:54 AM

In my ASP projects I have these two functions...

Code:

<%
Function PreDB(text)
    PreDB = Replace(text, "'", "''")
End Function
%>

<%
Function PostDB(text)
    PostDB = Replace(text, "''", "'")
End Function
%>

They will probably give you an idea how to continue. I think I used an Access 2000 database, but I think it is the same.

Hope it helps!

Jacob.

Sach · April 7th, 2004, 07:59 AM

it worked a treat, thanks for all your help mate

Imar · April 7th, 2004, 08:07 AM

Hi Jacob,

Just wondering, but what do you use PostDB for?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Sach · April 14th, 2004, 04:02 AM

I didn't use PostDB, only the replace function.

jacob · April 14th, 2004, 06:35 AM

Imar, sorry for the late reply...

When I used classic ASP I used this as a general function that would enable me to do some commands on strings retrieved from the database. The function posted does not have much functionality, but it could have why it is nice to have one common function for these procedures!

E.g. if I would like to replace all '<'- and/or '>'-signs so that tags is not rendered when shown, or if one was developing a forum then it would be nice to be able to replace special character combinations with e.g. emoticons!

So PostDB refers to the procedures carried out after the database (when retrieving).

Jacob.

Imar · April 14th, 2004, 07:23 AM

Right, I see.
I also see the use of general replacement functions, like changing the < and >

However, when you send a double single quote to the database to insert a single quote, all that is inserted is the actual single quote. You double them just to tell the database that it should insert the literal value of the quote, and to not try to give it a special meaning.

So, the PostDB method you posted either does nothing (there is nothing to replace), or you're messing up your data by replacing two literal single quotes, with one (that is, it was the initial intention of the one who created the record to insert two single quotes).
The point is, for these kind of database inserts, escaping certain characters is a one-way process: only when you insert stuff should you need to escape them.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Spybreak! (short one) by Propellerheads (Track 2 from the album: The Matrix - Movie Soundtrack)