CODEWITHSUNDEEP
sql-server-2008parametersntext
gina
gina

Reputation: 23

Parameter query on nvarchar(max) shows as ntext error

I recently converted all ntext column types in my database to nvarchar(max). I then ran EXECUTE sp_refreshview for the related views.

Yet when I run the following Parameter query (from classic ASP) on a view, I get an error: Query: 

SELECT   CARID 
FROM     vwCAR 
WHERE    (1=1) 
AND      (Description LIKE '%'+ ? + '%') 
ORDER BY CARID;

Error: The data types nvarchar and ntext are incompatible in the add operator (yet there are no longer any ntext columns!)

Yet I do NOT get this error if I run the same query directly SQL Server without the ? parameter as: Query: 

SELECT  CARID 
FROM    vwCAR 
WHERE   (1=1) 
AND     (Description LIKE '%test%') 
ORDER BY CARID;

I tried using Convert in the query, but had same result: Query: 

SELECT   CARID 
FROM     vwCAR 
WHERE    (1=1) 
AND      (CONVERT(NVARCHAR(MAX), Description) LIKE N'%'+ ? + '%') 
ORDER BY CARID;

What am I doing wrong?

Additional information: I changed the type to nVarChar(4000) instead of (MAX) and everything works fine. This is a work around, but it solved the problem.

Just so I will know for the future, is it possible to run a parameter query using LIKE criteria on a nVarChar(Max) type column?

(Thank you @McNets for the post clean up .. I am new to this)

Upvotes: 0

Views: 456

Answers (2)

gina
gina

Reputation: 23

I was using the wrong field type adLongVarWChar (203) in the parameter. Should have been using adVarWChar (202) for the nvarchar(max) type.

Confusion arose when I retrieved the field type directly from the database as noted below, it returned 203 for the nvarchar(max) type, so I assumed setting the parameter based on that type would work.
For each ofield in objRS.Fields Redim Preserve FieldTypes(1,x) FieldTypes(0,x) = ofield.type FieldTypes(1,x) = ofield.definedsize x = x + 1 Next

Upvotes: 0

Altynbek S.
Altynbek S.

Reputation: 1

try to set before :

Set @value = '%' + @value + '%' ;

then use:

(description like @value )

Upvotes: 0

Related Questions