Saving Unicode in SQL using ASP

H

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
 
A

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?
 
H

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
 
A

Aaron Bertrand [SQL Server MVP]

INSERT table_name(column_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
 
H

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?
 
M

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.Codepage=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.
 
H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top