--> ' <--- How can I save data which includes this mark with SQL

[seljuk]

I have a textbox and I want that user enter and information in it and then press a command button to save it but if the textbox has --> ' <-- this mark.Program doesn't save that information and gives an error message

Thanks For Your Helps

[RobDog888]

I'd like to know also. I am having problems
using it in a SQL query. It thinks the single quote '
is designating the end of the SQL query string
that I'm passing into Access. I'm working on
searching the string for the '
(using the Instr function) and if it finds it,
replace it with the ASCII equivalent "chr(39)" in that position represented by a constant.
I havent got it to work yet but if I do I'll post it
for you or maybe with the logic I laid out you might
do better then me.

[gaffa]

I think its two in a row ('') or three in a row (''')

Can never remember which....

- gaffa

[Slaine]

One of my developers had the same problem a while back.

I did some digging in the documentation and found that you can play around with the QUOTED_IDENTIFIER property to get varying results but the following is a clip from the SQL documentation (Check "Delimited Identifiers" in the online books for further information).

When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotation marks and single quotation marks in SQL statements:

Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
To maintain compatibility with existing applications, SQL Server is not rigorous in its enforcement of this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier; however, this practice is not recommended.

Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
If the character string contains an embedded single quotation mark, insert an additional single quotation mark in front of the embedded mark:

SELECT * FROM "My Table"

WHERE "Last Name" = 'O''Brien'



When QUOTED_IDENTIFIER is OFF, SQL Server follows these rules for the use of double and single quotation marks:

Quotation marks cannot be used to delimit identifiers. Instead, use brackets as delimiters.
Either single or double quotation marks can be used to enclose character strings.
If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks:

SELECT * FROM [My Table]

WHERE [Last Name] = "O'Brien"



Bracketed delimiters can always be used, regardless of the setting of QUOTED_IDENTIFIER.

[Slaine]

Whoops,

I should have said that the above is relevent to SQL-Server 7. I don't use Access so I'm not sure whether the same rules apply but you may be able to use the information as a starting point.

Cheers

[Frans C]

In Access you have to preceed every single quote by another one, so it behaves the same as SQL Server when QUOTED_IDENTIFIER is set OFF.
You have to write a function that replaces every single quote by two single quotes. In vb6 this is easy, you can use the Replace function. In earlier versions you have to loop using a function like Instr.