Solved

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

Posted on 2007-11-16
7
1,305 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
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

786 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