Apostrophe dilemma in text fields 1

[Guest_imported]

I've got a PHP application using a Microsoft SQL 2000 Backend, but I've had problems with apostrophe's in fields.

SET Description = '15' Tall' or SET Description = "15" Tall";

We use measurments in our descriptions and at least one will cause problems for that type of measurement! I looked in my SQL book and didn't find much on using another separator. Any ideas to allow users to use the measurement shortcuts?

 

[JohnDTampaBay]

In order to get the apostrophe in the field, replace it with two single apostrophes in the SET command:

SET Description = '15'' Tall'

The alternative is to SET QUOTED_IDENTIFER OFF so that

SET Description = "15' Tall"

becomes a valid statement.
--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) selected

 

[ucinv]

I had a similiar problem in my Access 2000 w/SQL SERVER 7 app. Maybe this function will help you do something similar:

Public Function HandleApostro(astring)
If Not IsNull(astring) Then

If InStr(1, astring, "'&quot > 0 Then
HandleApostro = Replace(astring, "'", "''&quot
Else
HandleApostro = astring
End If
Else
HandleApostro = ""
End If

End Function

Basically, it "replaces" the apostrophe with a double-apostrophe which the system will handle. The replacement is not written into the table, but you could alter the code a bit if you want to replace permanently. I call the function within my dynamic SQL queries, ex:

combo1.rowsource="Select * from tblOrder where company = '" & HandleApostro(me.company) & "'"

I used to get errors when I used the string below if the company field contained an apostrophe.

combo1.rowsource="Select * from tblOrder where company = '" & me.company & "'"

Good luck!

 

[cyberbiker]

I see this is an older post, and I hope I am not commiting a faux paux for reviving it, but thanks to ucinv.
You saved me a whole bunch of work puzzeling this out.
I have a virtually identical situation and this seems to work perfectly

Terry (cyberbiker)