INSERT special characters into SQL Database (Protect my Tic)

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.Was it a quote that didn't go through?|||

Quote:

Originally Posted by Arielle

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.

hi Arielle,

if you are using sql server2000 for backend activities, it accepts all the special characters while inserting a record,except for ' single quotes. You would have noticed that it throws errors, when we try to insert something like this:

eg:
insert into <tablename> values(val1,val2,'the student's of csc dept have scored the highest')

unclosed quotation mark before the character string...
incorrect syntax near...

if you want to insert your message that contains apostophe character from your asp page to sql server table, then you will have to use the Replace() function in your asp page.

Replace(string,find,replacewith)
where,
string: the string to be searched
find: the part of the string that will be replaced
replacewith: the replacement substring

eg: dim txt,crreason
txt=request("txtCrReason")
crreason=Replace(txt,"'","''")
...and send the value stored in the variable 'crreason' to your insert query.

try this and let me know if it has solved your problem. Gud Luck!

cheers,
jai

insert special characters in database

I am writing a web application and everytime I attempt to insert some special characters in the database I get errors. Is there a way around this?Depends what you mean. If you mean unicode and you are attempting to insert into CHar or VarChar fields then you need to use NVarChar or NChar. BoL has all the gory details. Otherwise you'll need to porovide more details.

HTH|||it seems everytime I include an apostrophe the system gives me an error..not sure why this is happening

my second question is-- when someone types this:

I cant wait
to leave
this place

--It come out like this
I cant wait to leave this place

How can I make the system print all the tab keys and return carriage?

thank you for any responses|||You need to escape apostrophes.

"I can't wait to leave this place"
must be submitted as
"I can''t wait to leave this place"

HTH|||The latter problem is probably a presentation issue - your FE needs to interpret the carriage returns & line feeds.|||hi everybody,

This is Rajen, hope it will be nice sharing ideas with u all. Feeling nice to join u all .:beer: