Data types ntext and nvarchar incompatible with Equal to operator Fix.

Objective: This article provides a quick solution to the Unhandled Exception message when the generated SQL update command by default assigns the equal (=) operator to the @Original parameter for the ntext and nvarchar data types.

Background: The generated error message would look something similar to the following: System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator. This is a common problem seen in ASP.NET when working with an existing database that worked just fine with classic ASP using good old Dreamweaver. Although it can be attributed to incorrect or faulty database design that should be taken into consideration at design time, it seems rather rigid and very annoying at the least especially if you have already spent a considerable amount of time constructing dozens of dynamic data driven pages in your site relying on the very same tables in your database. If you change the data column types in your database to remedy the problem, you will end up having to fix all of your already constructed pages to reflect the changes taking precious time out of your schedule.

So, my quick solution is to simply change all the equal (=) operators in the @Original parameter to the LIKE operator. This had worked fine for me on many occasion when I had to deal with this particular problem and I hope it saves you some time as well. Refer to the example below to get a better idea of how this is done.

Procedure: Follow instructions below to accomplish this task. This article uses Visual Web Developer Express 2008 and SQL Server Express 2005 .

Option A. Generating correct SqlDataSource at design time.

1. When you create your SqlDataSource and Auto generate the Update, Insert and Delete statements make sure you actually take a look at query being generated and make corrections before clicking the finish button. Note: in case you don’t know, when selecting the table columns in the query designer, if you forget to select the ID (primary key) column, you won’t be able to automatically generate the Update, Insert and Delete statements. So, make sure you remember to do that.
   
   
   
2. You can also do this visually by clicking the Query Builder button and make corrections to the filter options like shown below to change the = @Original.. to LIKE @Original at each instance of a nvarchar or ntext column.

   

Option B. Editing the query statement in code view.

If you have already created your SqlDataSource and spent lots of time designing your GridView or your choice of data view, you may want to consider to make your corrections in code view instead. The reason for this is that when you modify an already existing data source, Visual Web Developer will regenerate your data view overriding all your precious customization that you spent your whole afternoon on getting it just right.

1. Switch to code view in your page.
2. Locate the SqlDataSource in question.
3. Locate the UpdateCommand.
4. In the UpdateCommand find the WHERE clause and comb through everything that comes after it. Identify all the columns that are nvarchar or ntext data type and change the equal signs to LIKE. You may have to refer to your database to refresh your memory. An example is provided below:

   UpdateCommand="UPDATE [agents] SET [FirstName] = @FirstName, [LastName] = @LastName, [Designations] = @Designations, [Address] = @Address, [City] = @City, [State] = @State, [Zip] = @Zip, [HomePhone] = @HomePhone, [HomePhoneExt] = @HomePhoneExt, [CellPhone] = @CellPhone, [WorkPhone] = @WorkPhone, [WorkPhoneExt] = @WorkPhoneExt, [HomeFax] = @HomeFax, [WorkFax] = @WorkFax, [Email] = @Email, [Website] = @Website, [AgentBio] = @AgentBio, [AgentPhoto] = @AgentPhoto, [mlsURL] = @mlsURL, [mlsURL2] = @mlsURL2, [UserName] = @UserName, [Password] = @Password, [Approved] = @Approved, [AccountActive] = @AccountActive WHERE [Agent_ID] = @original_Agent_ID AND [FirstName] LIKE @original_FirstName AND [LastName] LIKE @original_LastName AND [Designations] LIKE @original_Designations AND [Address] LIKE @original_Address AND [City] LIKE @original_City AND [State] LIKE @original_State AND [Zip] LIKE @original_Zip AND [HomePhone] LIKE @original_HomePhone AND [HomePhoneExt] LIKE @original_HomePhoneExt AND [CellPhone] LIKE @original_CellPhone AND [WorkPhone] LIKE @original_WorkPhone AND [WorkPhoneExt] LIKE @original_WorkPhoneExt AND [HomeFax] = @original_HomeFax AND [WorkFax] LIKE @original_WorkFax AND [Email] LIKE @original_Email AND [Website] LIKE @original_Website AND [AgentBio] LIKE @original_AgentBio AND [AgentPhoto] LIKE @original_AgentPhoto AND [mlsURL] = @original_mlsURL AND [mlsURL2] LIKE @original_mlsURL2 AND [UserName] LIKE @original_UserName AND [Password] LIKE @original_Password AND [Approved] LIKE @original_Approved AND [AccountActive] LIKE @original_AccountActive">

Option C. Data Access Layer instead of SqlDataSource.

1. It really does not matter what kind of data access method you are using. Your task is to change your update query. If you are for instance using Data Access Layer with a DataSet, go to your App_code folder, open the DatSet in question and make your change there.

That’s it! Enjoy.