July 17, 2002 at 2:26 am
i need to put apostrophe into my records in ms sql server but it turn out tat it will cause some error such as... <the page cannot be displayed> http 500 internal error... is there any other way to replace apostrophe ???
thank you!
July 17, 2002 at 2:45 am
Hi you could use the function replace to replace a ' (quote) by 2 quotes ''
strOut= replace(strIn,"'","''")
July 17, 2002 at 2:54 am
hi thank alot,
can u explain why it don't allow ' single quote?
July 17, 2002 at 3:35 am
Hi,
a single quote is seen in sql server for separating text strings . For example
the following statement is valid
insert into tblX (A,B)
values ('one','two')
but if you have a quote in the string like this:
insert into tblX (A,B)
values ('o'reily','two')
then you got an error because sql thinks their are 3 strings to insert in 2 columns.
best regards,
Klaas-Jan
July 17, 2002 at 3:45 am
hi,
i still haf some doubts..
as i have 3 textfields to differeniate as country code, area code and number but when i put the replace code into all the three textfields in the program then when i was running the code, i try to put the apostrophe in the number textfield but it caused my db to corrupt
but it work well with just one textfield example like address.
can you giv me some advice!!!
thank you.... =)
July 17, 2002 at 3:54 am
Hi,
In a number (or decimal or float) field you could not fill in a quote.
In your code yould where you build your sql string you could something like this:
strsql="insert into tblX (A,B,C,D)" & _
"values ('" & replace(strA,"'","''") & "'," & _
"'" & replace(strB,"'","''") & "'," & _
dblC & "," & _
"'" & replace(strD,"'","''") & "'"
July 17, 2002 at 5:23 am
That's exactly right. You only do it on strings, numbers (true numbers that is!) don't have single quotes in them anyway. If you use an ADO recordset to do the updates or an ADO command object to execute a stored proc it handles the issue for you. If you just use a connection to execute a proc/sql then you have to handle any embedded quotes. The single quote is an escape character, requires special handling just as the % sign in a like statement does if you're actually trying to find a percent sign.
Andy
August 7, 2002 at 9:09 am
Wow, had the same problem and replace worked perfectly. Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply