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.
-- New Info --
All of my web page -> Database linkages are stored in a file called DBDataSet.xsd. When I right-click on an empty area within this form, I get the option to Add -> TableAdapter... When I do this, I get a wizard that walks me through Choose Your Data Connection, then Choose a Command Type (I choose Use existing stored procedures), then Bind Commands to Existing Stored Procedures. I select my Select, Insert, Update and Delete sprocs, each of which accept as parameters both VarChar and NVarChar data for the appropriate columns. It then takes me to Choose Methods to Generate, and I select both Fill a DataTable and Return a DataTable.
All of this happens automatically, and a bunch of code is generated behind-the-scenes. When I search the DBDataSet.Designer.cs file for references to MotherTongueLanguageName, I don't see any code that differentiates it from other columns that are just VarChars. For example:
[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
private void InitClass() {
this.columnLanguageName = new global::System.Data.DataColumn("LanguageName", typeof(string), null, global::System.Data.MappingType.Element);
base.Columns.Add(this.columnLanguageName);
this.columnLanguageID = new global::System.Data.DataColumn("LanguageID", typeof(byte), null, global::System.Data.MappingType.Element);
base.Columns.Add(this.columnLanguageID);
this.columnLanguageAbbrev = new global::System.Data.DataColumn("LanguageAbbrev", typeof(string), null, global::System.Data.MappingType.Element);
base.Columns.Add(this.columnLanguageAbbrev);
this.columnMotherTongueLanguageName = new global::System.Data.DataColumn("MotherTongueLanguageName", typeof(string), null, global::System.Data.MappingType.Element);
base.Columns.Add(this.columnMotherTongueLanguageName);
this.columnLanguageColour = new global::System.Data.DataColumn("LanguageColour", typeof(string), null, global::System.Data.MappingType.Element);
base.Columns.Add(this.columnLanguageColour);
this.Constraints.Add(new global::System.Data.UniqueConstraint("Constraint1", new global::System.Data.DataColumn[] {
this.columnLanguageID}, true));
this.columnLanguageName.AllowDBNull = false;
this.columnLanguageName.MaxLength = 30;
this.columnLanguageID.AllowDBNull = false;
this.columnLanguageID.ReadOnly = true;
this.columnLanguageID.Unique = true;
this.columnLanguageAbbrev.AllowDBNull = false;
this.columnLanguageAbbrev.MaxLength = 3;
this.columnMotherTongueLanguageName.AllowDBNull = false;
this.columnMotherTongueLanguageName.MaxLength = 30;
this.columnLanguageColour.MaxLength = 10;
}
Both LanguageName and MotherTongueLanguageName use typeof(string), even though one is a VarChar, and the other is an NVarChar. Is this correct?
Also:
[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "4.0.0.0")]
private void InitAdapter() {
this._adapter = new global::System.Data.SqlClient.SqlDataAdapter();
global::System.Data.Common.DataTableMapping tableMapping = new global::System.Data.Common.DataTableMapping();
tableMapping.SourceTable = "Table";
tableMapping.DataSetTable = "GetLanguages";
tableMapping.ColumnMappings.Add("LanguageName", "LanguageName");
tableMapping.ColumnMappings.Add("LanguageID", "LanguageID");
tableMapping.ColumnMappings.Add("LanguageAbbrev", "LanguageAbbrev");
tableMapping.ColumnMappings.Add("MotherTongueLanguageName", "MotherTongueLanguageName");
tableMapping.ColumnMappings.Add("LanguageColour", "LanguageColour");
this._adapter.TableMappings.Add(tableMapping);
this._adapter.DeleteCommand = new global::System.Data.SqlClient.SqlCommand();
this._adapter.DeleteCommand.Connection = this.Connection;
this._adapter.DeleteCommand.CommandText = "dbo.DeleteLanguage";
this._adapter.DeleteCommand.CommandType = global::System.Data.CommandType.StoredProcedure;
this._adapter.DeleteCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RETURN_VALUE", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue, 10, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.DeleteCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@LanguageID", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.Input, 10, 0, "LanguageID", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.InsertCommand = new global::System.Data.SqlClient.SqlCommand();
this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "dbo.AddLanguage";
this._adapter.InsertCommand.CommandType = global::System.Data.CommandType.StoredProcedure;
this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RETURN_VALUE", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue, 10, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@LanguageName", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@LanguageAbbrev", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageAbbrev", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@MotherTongueLanguageName", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "MotherTongueLanguageName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.InsertCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@LanguageColour", global::System.Data.SqlDbType.VarChar, 10, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageColour", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand = new global::System.Data.SqlClient.SqlCommand();
this._adapter.UpdateCommand.Connection = this.Connection;
this._adapter.UpdateCommand.CommandText = "dbo.EditLanguage";
this._adapter.UpdateCommand.CommandType = global::System.Data.CommandType.StoredProcedure;
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RETURN_VALUE", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.ReturnValue, 10, 0, null, global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@OldLanguageID", global::System.Data.SqlDbType.Int, 4, global::System.Data.ParameterDirection.Input, 10, 0, "LanguageID", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@OldLanguageName", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@OldLanguageAbbrev", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageAbbrev", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@OldMotherTongueLanguageName", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "MotherTongueLanguageName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@OldLanguageColour", global::System.Data.SqlDbType.VarChar, 10, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageColour", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@NewLanguageName", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@NewLanguageAbbrev", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageAbbrev", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@NewMotherTongueLanguageName", global::System.Data.SqlDbType.VarChar, 30, global::System.Data.ParameterDirection.Input, 0, 0, "MotherTongueLanguageName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@NewLanguageColour", global::System.Data.SqlDbType.VarChar, 10, global::System.Data.ParameterDirection.Input, 0, 0, "LanguageColour", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
}
Shouldn't the generated code be referencing global::System.Data.SqlDbType.NVarChar instead of global::System.Data.SqlDbType.VarChar? If I make these changes directly to the generated code, the changes get blown away as soon as I even refresh the DBDataSet.xsd file.
This is an existing project, so I need to continue with the manner by which data is fetched from and put to the database. What do I need to do to get the data from the webpage, through the TableAdapter, to the database correctly?
Found the answer I was looking for at DaniWeb.
The problem was that it wasn't clear that I needed to use the DataSet designer to change my new Unicode columns from DataType:AnsiString (the default) to DataType:String. As soon as I do that, the designer changes the ProviderType from VarChar to NVarChar. I had thought that if you were creating a new TableAdapter from scratch, that it should recognize that the column was an NVarChar and do this automatically. Now I realize I simply need to do that extra step.
Also, as we are using a separate TableAdapter for storing all of the non-CRUD stored procedures, I need to do the same with that, ensuring that the data being passed stays Unicode.