Problem with a ' in a databasefield. (SOLVED)

[ShotokanTuning]

Hello,

I had a problem and I couldn't figure out what it was.
But after many mindbreaking I found out it was about a ' in a particular field.

I've got a Access database and in there is a table with a field of the Memo type. Now this field for example contyains the following text:

Many COA's have not been booked properly.
Result: A COA without values

Now it has an end of line but that isn't the problem I'm facing it's the '.

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

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Test' Test' Test''.

/Kamsystem/include/connect.asp, line 29
line 29 is the submitting of the Update querry.


I'm doing an UPDATE on a table with the memo field. If that field contains a ' it gives an error, I think it's because all the dfata behind the comma isn't inserted anywhere. So it cancels the update.

Shouldn't it be possble to have ' inserted and updated in a memo field?

I can't figure out how.

Thanks.

[si_the_geek]

Any time you put text data into SQL, replace any single quotes in the data with two single quotes (eg: '').

so to put in this text:
'Test' Test' Test'
you need to do something like this:

Update table Set field = '''Test'' Test'' Test'''

[Ecniv]

Another alternative is using the Replace function to replace all single quotes (') with the apostrophy (`) which works well for storing text. Then you need the same replace on the search field to replace any single quotes before searching the db.

Just another option.

Vince

[si_the_geek]

the trouble with that is that you are changing the data, replacing single quotes with two simply alters the SQL

[ShotokanTuning]

This works when updating:

Function FormatText (inputText)
Dim textString, descr
dateString = inputText & ""
dateString = replace(dateString, "'", "''")
FormatText=dateString
End Function

Thanks for helping me out.