Unable To Display Data On DBGrid using SQL

Hi,
 I would like to display a set of data using SQL query in DBGrid. However, I keep getting error when I try to run the code using Delphi 2005. I am not sure what is causing it. I have pasted the code for reference. Thanks.


<<<<<<<<<<<<<
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, Grids, DBGrids, StdCtrls, Mask, DBCtrls;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    ADOQuery1: TADOQuery;
    ADOConnection1: TADOConnection;
    DBEdit1: TDBEdit;
    procedure FormShow(Sender: TObject);
  private
  sSQLSTR : String;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormShow(Sender: TObject);
begin
with ADOQuery1 do
   begin
     Close;  SQL.Clear;

     sSQLSTR := 'Select * From TaskMaster Where Not Exists '+
                '(Select * From UserTaskMaster Where UserName =:UserName) '+
                 'Order By FormName, Funct';


      SQL.Add(sSQLSTR);
     Parameters.ParamByName('UserName').Value := DBEdit1.Text;
     Open;
   end;
end;

end.



>>>>>>>>>>>


The Error I keep getting is "First chance exception at $7C59BC3F. Exception class EOleException with message 'The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator'. Process Project1.exe (1932)"

I have run the SQL Statements in SQL Server using query analyser and it is able to run without any problems.
Any help is appreciated. Thanks.
LVL 1
cwtangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
i would guess the problem is as the exception tells
-> do not sort on an "Blob-Field"

meikl ;-)
0
cwtangAuthor Commented:
Hi,
Thanks For the reply. I have tried removing the 'Order By FormName, Funct' statements in the SQL Query and it still return the same error message. I am not sure what is the cause of the error...

Any help is appreciated.

Thanks.
0
mikelittlewoodCommented:
Which field is your blob field as if it is in your ORDER BY clause you cannot have it here

Also Im guessing there must be a common index field between the 2 tables, so shouldnt your query be something like (bear in mind Ive assumed the main common index field is called iIdx though you can change this)

     sSQLSTR := 'Select * From TaskMaster Where iIdx not in '+
                '(Select iIdx From UserTaskMaster Where UserName =:UserName) '+
                 'Order By FormName, Funct';

You will still have to remove your blob field from the order by though if it is listed here
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

RickJCommented:
I think you also have to be careful comparing Text types.
Is the UserName field of type Text or VarChar ?
If Text change to VarChar and see if it works.
0
cwtangAuthor Commented:
I have edited the SQL statement to the following below, but I still get the same error message.

     sSQLSTR := 'Select * From TaskMaster Where Taskid not in '+
                '(Select Taskid From UserTaskMaster Where UserName =:UserName) '+
                 'Order By FormName, Funct';


      SQL.Add(sSQLSTR);
     Parameters.ParamByName('UserName').Value := DBEdit1.Text;
     Open;
 
The username field has been changed from nvarchar to varchar, still I get the same error. As for the taskid field it is an integer field. I have no problem running the program if I break up the SQL statements and run them indiviually, but I just cant run it when I have them nested together. I am not sure if is an limitation of delphi 2005? Anyone has any ideas of the cause?

This is really fustrating; no problem running in SQL analyser querry, but I cant just run it from Delphi :(
0
RickJCommented:
OK...
I should have said nvarchar before, sorry.
The parameter that you are passing, does it have a value? i.e What is the value of DBEdit1.Text?
If it is null or empty string, try actually passing a "real" value.  Does it make any difference?
0
RickJCommented:
Sorry if I do not reply again, I am going to bed....
Good Luck.
0
cwtangAuthor Commented:
The program manage to run when I pass real value instead of null. However, I need the DBEdit1.text as it is used to pass a value from another DBGrid when the form is opened.  Any idea on how it might work with the DBEdit1 ?

Thanks.
0
RickJCommented:
I have seen problems with null parameters in the past, I would put the value directly into the SQL string i.e

sSQLSTR := 'Select * From TaskMaster Where Taskid not in '+
                '(Select Taskid From UserTaskMaster Where UserName = ' + '''' + DBEdit1.Text + '''' + ') '+
                 'Order By FormName, Funct';


SQL.Add(sSQLSTR);
Open;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cwtangAuthor Commented:
Thank You. Got it fixed :)
0
RickJCommented:
Thanks.
Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

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.