Saving Unicode in SQL using ASP

[hilio]

I have an asp application that should allow the user to enter Unicode
characters.

The characters appear correctly in the browser.
When saved in sql 2000 thought they are converted to question marks
etc.
When I enter the characters directly in SQL (using enterprise manager),
they appear correctly in sql as well as in the browser when the asp
page retrieves them.
As soon as I hit save they get overwritten in sql incorrectly.

Any help would be appreciated.
Thanks

[Aaron Bertrand [SQL Server MVP]]

What does your UPDATE / INSERT statement look like? Are you prefixing your
strings with N prefix to denote Unicode or, better yet, using a Command
object with the correct parameter type?

<hi***@cambridg esoft.com> wrote in message
news:11******** *************@o 13g2000cwo.goog legroups.com...

I have an asp application that should allow the user to enter Unicode
characters.

The characters appear correctly in the browser.
When saved in sql 2000 thought they are converted to question marks
etc.
When I enter the characters directly in SQL (using enterprise manager),
they appear correctly in sql as well as in the browser when the asp
page retrieves them.
As soon as I hit save they get overwritten in sql incorrectly.

Any help would be appreciated.
Thanks

[hilio]

I didn't know that my UPDATE/INSERT should look any different. Can you
give an example of what they should look like?

Thanks

[Aaron Bertrand [SQL Server MVP]]

INSERT table_name(colu mn_name) SELECT N'¥'

You need to determine if the unicode character is getting lost between ASP
pages or between the second ASP page and the call to the
update/insert/stored procedure statement.

A very basic and common debugging technique is to response.write sql instead
of conn.execute sql.

A

"hilio" <hi***@cambridg esoft.com> wrote in message
news:11******** *************@g 47g2000cwa.goog legroups.com...

I didn't know that my UPDATE/INSERT should look any different. Can you
give an example of what they should look like?

Thanks

[hilio]

Aaron, thanks for the suggestion. I tried response.write and it
displayed different characters, not the characters that are entered by
the user. Would this indicate that the web server is not interpreting
the characters correctly?

[Mark Schupp]

first make sure that you are using a character set in the browser that
supports the characters. Then make sure that you are using a code page in
ASP that supports them as well. Simplest way is to use UTF-8

in ASP script
Session.Codepag e=65001

then send to browser:
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">

Now you should see the same characters back when you write the SQL statement
back.

Now you need to get the data into the database. If you have SQLServer set to
a collation that supports your characterset then it should work now. If not,
you will need to use NCHAR or NTEXT columns. Either use a parameterized
command object or when building dynamic SQL prefix the data string with "N"
to tell SQLServer that the data is unicode.

--
--Mark Schupp
"hilio" <hi***@cambridg esoft.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

Aaron, thanks for the suggestion. I tried response.write and it
displayed different characters, not the characters that are entered by
the user. Would this indicate that the web server is not interpreting
the characters correctly?

[hilio]

Thank you both for the replies.
I implemented your suggestions and it now works. I learned that SQL
requires the N prefix for updates and inserts of UNICODE data.

Thank you all