[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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.
0
cwtang
Asked:
cwtang
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
cwtangAuthor Commented:
Thank You. Got it fixed :)
0
 
RickJCommented:
Thanks.
Glad I could help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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