Delphi 7 :: ADOQuery :: MS SQL 2005 Express Help

Dear Experts,

I have a SQL 2005 database with a table.
A field in the table is of type: nvarchar(max).
When comparing values ina SELECT statement II get an error like this:
"The data types nvarchar(max) and ntext are incompatible in the equal to operator"

This is my query:
function TfrmMain.CheckIfPrejigExists(const AFileName: WideString): Boolean;
  qView.SQL.Text := 'SELECT ID from tblPrejig WHERE PrejigFileName = :PrejigFileName';
  qView.Parameters.ParamByName('PrejigFileName').Value := AFileName;
  qView.Last; qView.First;
  Result := qView.RecordCount > 0;

The parameter AFilename is a WideString and it is not compatible with my field type nvarchar(max).
What types should I use in my table for strings and still be ok with WideString / String etc in Delphi?

Thanks in advance
Who is Participating?
imitchieConnect With a Mentor Commented:
SELECT ID from tblPrejig WHERE cast(PrejigFileName as varchar(max))
= cast(:PrejigFileName as varchar(max))

nvarchar->varchar shouldn't hurt since you're comparing byte by byte
Marius0188Author Commented:
If I change the field type to:
nvarchar(50) it is working.

But 50 my be too short for my field length.
Any suggestions.

ThejakaConnect With a Mentor Commented:
Try nvarchar(200) or a greater length.
nvarchar(MAX) counts as a distinct type (not quite the same as nvarchar)
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Try this solution:

qView.SQL.Text := 'SELECT ID from tblPrejig WHERE rtrim(convert(char(200), PrejigFileName)) = :PrejigFileName';
bokistConnect With a Mentor Commented:
or this solution:

qView.SQL.Text := 'SELECT ID from tblPrejig WHERE convert(char(200), PrejigFileName) like :PrejigFileName';
qView.Parameters.ParamByName('PrejigFileName').Value := AFileName '%';
oops, I forget plus sign : AFileName + '%';

Marius0188Author Commented:
Ok, I have actually see where  I went wrong.
My field type should be varchar() and not nvarchar().

Let's split the points....
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.