What is correct syntax for SQL SELECT statement to read Unicode?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We need to read a SQL database containing a mix of English words and Chinese
Characters. We think we need to use the N'xxxx' to read the Unicode.

We have one place where the SELECT statement works fine. Here is that
statement:
dim objCmdCategories as new SQLDataAdapter ("select distinct
N'product_category' from " & _
"tblproducts where product_status=1 order by product_category", objConn)

However, we can't seem to get another SELECT statement to work. Here is the
2nd SELECT statuement:
myCommand = new SQLCommand("SELECT product_id, N'product_category',
product_name, product_weight, product_featured, product_status FROM
tblProducts ", myConnection)

This works fine if we take out the N'xxxx' . But if we leave it in, we get
the following .Net error:

A field or property with the name 'product_category' was not found on the
selected datasource.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.Web.HttpException: A field or property with the
name 'product_category' was not found on the selected datasource.

Source Error:


Line 15: myConnection.Open()
Line 16: dgProducts.DataSource = myCommand.ExecuteReader()
Line 17: dgProducts.DataBind() <=== This is highlighted Red
Line 18: myConnection.Close()
Line 19: end sub

Question: How to read the Unicode field in this SELECT statement?
 
You are partly wrong. The N'xxxx' syntax is only for string constants
inside Stored Procedures and Function on SQL-Server. (I don't know if you
can use the N'xxxx' syntax for the *Name* of the column if it is itself
Unicode).

For reading Unicode, the fields must be of type nchar, nvarchar or ntext
instead of char, varchar or text. This is for Unicode 16, taking into
account that if it is UTF8, the client will properly decode it and translate
it to UTF16 before sending it to the SQL-Server. Other fields, like binary,
varbinary and image, can also be used for storing UTF8 but you will lose
some capabilities, like Sorting.

For more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_dataencoding.asp
 
Back
Top