Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1355
  • Last Modified:

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
0
Marius0188
Asked:
Marius0188
3 Solutions
 
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
 
ThejakaCommented:
Try nvarchar(200) or a greater length.
nvarchar(MAX) counts as a distinct type (not quite the same as nvarchar)
0
 
bokistCommented:
Try this solution:

qView.SQL.Text := 'SELECT ID from tblPrejig WHERE rtrim(convert(char(200), PrejigFileName)) = :PrejigFileName';
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bokistCommented:
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
 
imitchieCommented:
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:
Ok, I have actually see where  I went wrong.
My field type should be varchar() and not nvarchar().

Let's split the points....
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now