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;
begin
  qView.SQL.Text := 'SELECT ID from tblPrejig WHERE PrejigFileName = :PrejigFileName';
  qView.Parameters.ParamByName('PrejigFileName').Value := AFileName;
  qView.Open;
  qView.Last; qView.First;
  Result := qView.RecordCount > 0;
end;

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
Marius0188Asked:
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
0
 
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.

Thanks!
0
 
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)
0
Get your problem seen by more experts

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

 
bokistCommented:
Try this solution:

qView.SQL.Text := 'SELECT ID from tblPrejig WHERE rtrim(convert(char(200), PrejigFileName)) = :PrejigFileName';
0
 
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 '%';
0
 
bokistCommented:
oops, I forget plus sign : AFileName + '%';

0
 
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....
0
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.