TableAdapter and Unicode?

Question

ccdsystems 0 Newbie Poster

10 Years Ago

Working with Microsoft Visual Studio 2010 Professional, using C#, with Microsoft SQL Server 2008.

I have been tasked with taking an existing application, and making changes so that it can receive multiple languages in its text boxes without needing to configure ahead of time what language is to be used.

To ensure that I am doing this correctly, I have created a new table in the database, called Language. If I can get things working correctly with [Language], I should be able to get it all working. The field in question is "MotherTongueLanguageName":

CREATE TABLE [Language](
    [LanguageID] [tinyint] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [LanguageName] [varchar](30) NOT NULL,
    [MotherTongueLanguageName] [nvarchar](30) NOT NULL)

I preload the table with content:

TRUNCATE TABLE [dbo].[Language]
SET NOCOUNT ON
INSERT INTO [Language] VALUES ('English','English') -- 1
INSERT INTO [Language] VALUES ('French','Français') -- 2
INSERT INTO [Language] VALUES ('Spanish','Español') -- 3
SET NOCOUNT OFF
GO

And on my web page, it all looks perfect. When I try to enter Russian and русский from the web page, it hits the database with '???????' in the MotherTongueLanguageName. I want to be able to do this without setting a specific culture or uiCulture, if possible.

I have already edited my MasterPage.master file and have added "<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />" to the <head>, and accept-charset="utf-8" to the <form> tag.
I have even added "Content-Type: text/html; charset=utf-8" to the Custom HTTP headers in IIS, thought I'm hoping that's not actually necessary.

The web page does use a TableAdapter to connect the grids and entry fields to the database, and I'm wondering if there's anything in there that's reverting my Unicode text back to ASCII.

Any help would be appreciated. I'm fresh out of guesses, and Google has helped me just about as much as it can with the search terms I've been using.

2 Contributors
12 Replies
1K Views
1 Week Discussion Span
Latest Post 10 Years Ago Latest Post by ccdsystems

cgeier 187 Junior Poster

10 Years Ago

Collation and Unicode Support

cgeier 187 Junior Poster

10 Years Ago

Try adding "N" in front of the unicode data:

TRUNCATE TABLE [dbo].[Language]
SET NOCOUNT ON
INSERT INTO [Language] VALUES ('English',N'English') -- 1
INSERT INTO [Language] VALUES ('French',N'Français') -- 2
INSERT INTO [Language] VALUES ('Spanish',N'Español') -- 3
Insert Into [Language] Values ('Russian', N'русский')
SET NOCOUNT OFF
GO

SQL Server multi language data support

"...When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N, as documented in the SQL Server Books Online topic "Using Unicode Data". The "N" prefix stands for National Language in the SQL-92 standard, and must be uppercase. If you do not prefix a Unicode string constant with N, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string..."

nchar and nvarchar (Transact-SQL)

Using Unicode Data
Unicode constants are specified with a leading N: N'A Unicode string'.

The following is listed for older versions of SQL Server:

You must precede all Unicode strings with a prefix N ...

"...Any time you pass Unicode data to SQL Server you must prefix the Unicode string with N..."

Edited 10 Years Ago by cgeier

cgeier 187 Junior Poster

10 Years Ago

Are you using the DataSet designer? If so, check the parameters by doing the following:

Open Solution Explorer:

View
Solution Explorer
Click your dataset name (ex: DataSet1.xsd)

In Dataset Designer:

Right-click your table adapter (ex: LanguageTableAdapter) and select "Properties"
Double-click "InsertCommand" to expand it
Click "..." on the right side of "Parameters"
Select "@MotherTongueLanguageName"
Ensure "DbType" is "String" (not AnsiString)
Ensure "ProviderType" is "NVarChar" (not VarChar)

Repeat for "UpdateCommand" (both @MotherTongueLanguageName and @Original_MotherTongueLanguageName)

LanguageTableAdapterProperties.JPG 14.6 KB

Edited 10 Years Ago by cgeier

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ccdsystems 0 Newbie Poster · Answer 1 · 2014-06-03T21:48:09+00:00

At the recommendation of some Microsoft info I've found (http://msdn.microsoft.com/en-us/library/hy4kkhe0(v=vs.85).aspx) I added the following to my web.config, but it hasn't made any difference:

<configuration>
  <system.web>
    <globalization requestEncoding="utf-8" 
               responseEncoding="utf-8" 
               fileEncoding="" 
               culture="" 
               uiCulture="" 
               enableClientBasedCulture="false" 
               responseHeaderEncoding="utf-8" 
               resourceProviderFactoryType="" 
               enableBestFitResponseEncoding="false" />

ccdsystems 0 Newbie Poster · Answer 2 · 2014-06-03T22:17:28+00:00

My SQL Server database collation is SQL_Latin1_General_CP1_CI_AS, but I was under the impression that this affected sort order only, and not capability.

Seems the database refuses to accept unicode data (like русский) into an NVarChar field unless the collation is set to accept it :-( Unfortunately, we're trying to make this into a truly international piece of software, and didn't realize the database could cripple the flexibility so.

ccdsystems 0 Newbie Poster · Answer 3 · 2014-06-04T16:39:43+00:00

Thanks cgeier, but if there's a part of that link you think may apply, I'd appreciate direction as to where -- it's a large document, and I've skimmed it all, and read what I believe to be the relevant parts, before posting here.

Another piece to this puzzle, which I believe adds confusion, or maybe clarification, is that the table column in question is behaving differently whether the data is being inserted via the Edit All Rows option in SQL Server Management Studio 2012, or whether it is inserted via a script run within a new query in SQL SMS 2012:

TRUNCATE TABLE [dbo].[Language]
SET NOCOUNT ON
INSERT INTO [Language] VALUES ('Russian via script','русский')
SET NOCOUNT OFF
GO

Produces:

LanguageID  LanguageName        MotherTongueLanguageName
         1  Russian via script  ???????

However, if I insert a new row into the table manually via SQL SMS 2012, I get:

LanguageID  LanguageName                MotherTongueLanguageName
         1  Russian via script          ???????
         2  Russian via direct input    русский

This may have to do with SQL Server, and not an issue with the TableAdapter at all. Is there a different forum I should direct this to?

ccdsystems 0 Newbie Poster · Answer 4 · 2014-06-04T17:09:57+00:00

Doh! I realize where part of the problem is:

INSERT INTO [Language] VALUES ('Russian via script','русский')

needs to be:

INSERT INTO [Language] VALUES ('Russian via script',N'русский')

This makes all of the difference. Does special handling need to be applied when handling Unicode data via a TableAdapter?

ccdsystems 0 Newbie Poster · Answer 5 · 2014-06-04T17:49:32+00:00

For example, should I put an "N" in here, as I have below?

<EditItemTemplate>
    <asp:TextBox ID="tbMotherTongueLanguageName" runat="server" CausesValidation="False" Text=N'<%#Eval("MotherTongueLanguageName") %>' Width="140px" />
</EditItemTemplate>

ccdsystems 0 Newbie Poster · Answer 6 · 2014-06-04T21:10:37+00:00

Correct -- this helps with my testing data, but now I'm back to the original question.

And on my web page, it all looks perfect. When I try to enter Russian and русский from the web page, it hits the database with '???????' in the MotherTongueLanguageName. I want to be able to do this without setting a specific culture or uiCulture, if possible.

I have already edited my MasterPage.master file and have added "<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />" to the <head>, and accept-charset="utf-8" to the <form> tag.
I have even added "Content-Type: text/html; charset=utf-8" to the Custom HTTP headers in IIS, thought I'm hoping that's not actually necessary.

The web page does use a TableAdapter to connect the grids and entry fields to the database, and I'm wondering if there's anything in there that's reverting my Unicode text back to ASCII.

I'm now wondering if I need to do anything special with the TableAdapter code that was generated by Visual Studio 2010.

cgeier 187 Junior Poster · Answer 7 · 2014-06-05T03:55:14+00:00

This may be of use:
Globalization Step-by-Step

cgeier 187 Junior Poster · Answer 8 · 2014-06-05T06:24:04+00:00

The following seems to work:

Create a new project:

Click "File" (in menu)
Select "New Project"
Double-click "Visual C#" to expand
Click "Web"
Select "ASP .NET Web Application"
Enter a name, and click "OK"

Open Solution Explorer:

Click "View" (in menu)
Select "Solution Explorer"

Add a DataSet to the project:

Click "Project" (in menu)
Select "Add New Item"
Under "Installed Templates" (left side), under "Visual C#", Click "Data"
Select "DataSet"
Click "Add"
Click "View" (in menu)
Select "ToolBox"
Double-click "TableAdapter"
Select an existing connection (or click "New Connection" and fill out the form)
Click Next
Click Next
Leave "Use SQL Statements" radio button checked. Click "Next"
Click "QueryBuilder"
Select "Language" table
Click "Add"
Click "Close"
Check "LanguageID", "LanguageName", and "MotherTongueLanguageName" checkboxes
Click "OK"
Click "Next"
Click "Finish"

Add a web page to display our data:

Click "Project" (in menu)
Select "Add new item"
Under "Installed Templates" (left side), under "Visual C#", Click "Web"
Select "Web Form"
For name enter: "DisplayLanguageInfo.aspx"
Click "Add"
In the "DisplayLanguageInfo.aspx" source code (source tab), replace:
```
<form id="form1" runat="server">
<div>

</div>
</form>
```

With the following:

    <form id="form1" runat="server">
        <p>
        <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle">
           <HeaderStyle CssClass="HeaderStyle" />
           <AlternatingRowStyle CssClass="AlternatingRowStyle" />
        </asp:GridView>
        &nbsp;
        </p>
    </form>

In "Solution Explorer":

Right-click "AddLanguageInfo.aspx"
Select "View Code" (this will show "DisplayLanguageInfo.aspx.cs")

Replace:

protected void Page_Load(object sender, EventArgs e)
{

}

With the following:

protected void Page_Load(object sender, EventArgs e)
{
    DataSet1TableAdapters.LanguageTableAdapter languageAdapter = new DataSet1TableAdapters.LanguageTableAdapter();
    GridView1.DataSource = languageAdapter.GetData();
    GridView1.DataBind();
}

Add a web page to enter our data:

Click "Project" (in menu)
Select "Add new item"
Under "Installed Templates" (left side), under "Visual C#", Click "Web"
Select "Web Form"
For name enter: "AddLanguageInfo.aspx"
Click "Add"

In the "AddLanguageInfo.aspx" source code (source tab), replace:

    <form id="form1" runat="server">
    <div>

    </div>
    </form>

With the following:

    <form id="form1" runat="server">
    <div style="height: 35px">

        Language Name:
        <asp:TextBox ID="languageNameTextBox" runat="server" style="margin-left: 14px"></asp:TextBox>

    </div>
    <p>
        Language Data:
        <asp:TextBox ID="languageUnicodeDataTextBox" runat="server" 
            style="margin-left: 23px"></asp:TextBox>
    </p>
    <p>
        &nbsp;</p>
    <p>
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" 
            style="margin-left: 156px" Text="Button" />
    </p>
    <p>
        <asp:Label ID="statusLbl" runat="server"></asp:Label>
    </p>
    </form>

Click the "Design" tab (below the code)
Double-click the button named "Button" to create the "Click" event (alternatively, right-click "Button" and select "Properties". In the "Properties" window, click the lightning bolt, then double-click on the word "Click")

In "AddLanguageInfo.aspx.cs", replace:

protected void Button1_Click(object sender, EventArgs e)
{

}

With the following:

protected void Button1_Click(object sender, EventArgs e)
{
    DataSet1TableAdapters.LanguageTableAdapter languageAdapter = new DataSet1TableAdapters.LanguageTableAdapter();
    //int newProductId = Convert.ToInt32(languageAdapter.Insert(Request["languageNameTextBox"], Request["languageUnicodeDataTextBox"]));

    int newProductId = Convert.ToInt32(languageAdapter.Insert(languageNameTextBox.Text, languageUnicodeDataTextBox.Text));
    statusLbl.Text = "Status: '" + languageNameTextBox.Text + "' added";
}

Note: "languageNameTextBox" is a textbox with ID="languageNameTextBox" and "languageUnicodeTextBox" is a textbox with ID="languageUnicodeTextBox".

Save project:

Click "File" (in menu)
Select "Save All"

To Run:
Click "Debug" (in menu)
Select "Start Debugging"
Replace the url with the url for your web page (if necessary)

ex:
http://localhost:14453/AddLanguageInfo.aspx

http://localhost:13959/DisplayLanguageInfo.aspx

Resource:
Tutorial 1: Creating a Data Access Layer

ccdsystems 0 Newbie Poster · Answer 9 · 2014-06-11T17:17:09+00:00

Thank you, cgeier, for the amazingly thorough assistance. I have confirmed from your reply regarding the DataSet Designer that my column in question is indeed being assigned as an AnsiString/VarChar instead of String/NVarChar. I will start with making just those changes, and see how far it takes me, and if that isn't sufficient, I will wade through your very complete follow-up post.