Solved

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

Posted on 2007-11-16
7
1,313 Views
Last Modified: 2013-11-23
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
Comment
Question by:Marius0188
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:Marius0188
ID: 20296985
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
 
LVL 8

Assisted Solution

by:Thejaka
Thejaka earned 150 total points
ID: 20297187
Try nvarchar(200) or a greater length.
nvarchar(MAX) counts as a distinct type (not quite the same as nvarchar)
0
 
LVL 6

Expert Comment

by:bokist
ID: 20297338
Try this solution:

qView.SQL.Text := 'SELECT ID from tblPrejig WHERE rtrim(convert(char(200), PrejigFileName)) = :PrejigFileName';
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Assisted Solution

by:bokist
bokist earned 150 total points
ID: 20297357
or this solution:

qView.SQL.Text := 'SELECT ID from tblPrejig WHERE convert(char(200), PrejigFileName) like :PrejigFileName';
qView.Parameters.ParamByName('PrejigFileName').Value := AFileName '%';
0
 
LVL 6

Expert Comment

by:bokist
ID: 20297362
oops, I forget plus sign : AFileName + '%';

0
 
LVL 25

Accepted Solution

by:
imitchie earned 200 total points
ID: 20297400
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
 

Author Comment

by:Marius0188
ID: 20348562
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question