Error in in select data from table in SQL Server

问题:

I use this code to select from a table in SQL Server 2008:

sqlcomm.CommandText = "select [objId] from [tablename] where href = @href"

The type of href in my table is ntext and I use this code to select:

 sqlcomm.Parameters.Add("@href", SqlDbType.NVarChar);
 sqlcomm.Parameters["@href"].Value = 'somestring';
 IDataReader reader = sqlcomm.ExecuteReader();

But it errors:

The data types ntext and varchar are incompatible in the equal to operator

When I use SqlDbType.NText it have same error. I can't change type of href column in my table. When I use like @href it works, but I couldn't use LIKE because I want to exact match.

How can I solve my problem?

回答1:

The error is what it says. You cannot compare an NTEXT column with a NVarchar parameter.

sqlcomm.CommandText="select [objId] from [tablename] where cast(href as nvarchar(max))=@href"

ntext, text, and image (Transact-SQL) have been deprecated for a while now. Look to convert them to n/varchar(max) and varbinary(max) as soon as practicable.

回答2:

You can't use ntext columns in a where clause in SQL. I'd suggest converting the column in the SQL statement if you are unable to change the existing column type e.g.CAST(href AS nvarchar(max)).