Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15

    Unanswered: Chinese Characters in SQL 2000

    Hi All

    I have to develop a website which allows users to enter their comments into Chinese languages. I need sql2000 to support the chinese characters.

    I am experienced .net web programmer, but have very little knowledge on the database side. Would really appreciate any help on this

    THanks
    Jignesh

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    All you should need to do is use Unicode character types (NCHAR, NVARCHAR, and NTEXT) and SQL Server should handle the Chinese characters gracefully. Getting SQL Server to provide Chinese error messages is a bit more complicted, but let's deal with one problem at a time!

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    I have declared the table column as nvarchar. I was searching on google and found out that the only thing I need to do is declare them as either nvarchar or nchar etc..

    I guess it does not work in my case. I am sure I am doing something wrong, but not sure what.

  4. #4
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    I installed windows XP language bar on my machine. So when I go to enterprise manager, I can type in chinese characters in the database directly and then can view them on my ASP.net page.

    But when I try to insert a record from an ASP.net page by typing chinese characters in the text box, they are all converted to ??????.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Something between your keyboard and the web page control is only passing 8 bit values. Since you can successfully enter Unicode (the Chinese characters) via SQL Enterprise Mangler, I think we can assume everything is Ok as far as your OS, which leads me to think the problem is either in your browser or beyond (probably the web page).

    -PatP

  6. #6
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    I guess you are right. The problem seems to be in the web page. I just created a sample ASP.net webpage, which have one text box and a button.

    When user clicks chinese characters in textbox with help of language bar and press enter, the data goes to database. But the data are converted to ??????????

  7. #7
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    I was doing some more trials and found out that only time when I can enter proper data in SQL is when I go to enterprise manager and type in the data in the cell myself ( just like Excel).

    But when I run the following query
    Insert into TestTable(Comments, Language) values('彰','chinese') even on the enterprise manager or query analyzer, the chinese characters are converted to ???????

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, let's cut SQL Server out of the problem altogether for a moment. try to construct a "hello world" sort of web page with just a text control and a button. Have it simply allow the user to enter text into a control, then create a popup that contains that text when the user presses the button. This will allow you to get the web page working first, then we can concentrate on getting the data back and forth from the database.

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    Hi Pat

    I tried exactly what you said. The code is below:

    Private Sub btnTemp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemp.Click
    ShowMessage(txtComment.Text)
    End Sub


    Private Sub ShowMessage(ByVal message As String)
    Dim scriptString As String = "<script language=JavaScript>"
    scriptString += "alert('" + message + "');"
    scriptString += "</script>"
    Page.RegisterStartupScript("ShowMessage", scriptString)
    End Sub

    The code works absolutely fine. I enter chinese characters in textbox and onclick of a button show a messagebox with javascript, which shows the chinese text perfectly fine.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good! Now, can you modify that same sample script so that a new button simply sticks the Textbox contents into a column in the database? If you can do that, then modify the existing button to pull the data from the column and display that data from the column.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    I guess thats when it fails. Below is my code:

    **********************The same button click event ***************
    Dim sqlConnection1 As SqlConnection = New SqlConnection("Data Source=(local); Initial Catalog = ChineseTest; UID=jop; PWD=jop2279")
    Dim cmd As SqlCommand = sqlConnection1.CreateCommand
    sqlConnection1.Open()
    cmd.CommandType = CommandType.Text
    cmd.CommandText = "Insert into TestTable (Comments, Lang)values('" + txtComment.Text + "','Chinese')"
    cmd.ExecuteNonQuery()
    sqlConnection1.Close()
    ************************************************** ***********************

    But when I go to the database, I can only see >???????

  12. #12
    Join Date
    Aug 2004
    Posts
    8
    Try running the statement in Query Analyzer like this
    Insert into TestTable(Comments, Language) values(N'彰','chinese')
    and see if that works.

    How are you constructing your INSERT statement in your asp.net code?
    Is it something like

    string cmd="Insert into your_table(Comments) values('"+someTextBox.Text"')";
    // Execute cmd

    ?

    Edit: I see from your post that that is the case. You should really consider using a parameterized query for this. It ought to solve your unicode issue AND help protect you against SQL injection attacks.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for the jolly factor, I'd apply the latest MDAC to both the web server, and the client PC (this is just "iron underware" for the development environment, it is not needed in production).

    -PatP

  14. #14
    Join Date
    Jul 2003
    Location
    Jacksonville, FL
    Posts
    15
    I changed the SQL statement as per ded's instruction to:

    Insert into TestTable(Comments, Language) values(N'彰','chinese')

    and it start working fine. I can run the same query from ASP.net webpage and it works fine too..

    Thanks for your support guys. Without you, it would have been impossible for me to figure out this thing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •