Solved

Database grid

Posted on 1998-05-30
17
225 Views
Last Modified: 2010-04-06
Im trying to filter a database on one screen based on the information taken from an edit and combobox on another screen.
In other words from form1 I choose the field using the combobox1.text then I choose what I want to filter using the edit1.text. Then I press a button taking me to form2 which is a grid based form and this should list only the items I have asked for on form2 can anyone help?
0
Comment
Question by:mykyl
  • 8
  • 8
17 Comments
 
LVL 3

Accepted Solution

by:
Matvey earned 140 total points
Comment Utility
You set these things in the underlying table:

Table1.Filter := combobox1.text + ' = ' + edit1.text;
Table1.Filtered := true;

--Matvey
bosism@netvision.net.il
0
 

Author Comment

by:mykyl
Comment Utility
I have tried what you suggested and each time I get the message:-
'Filter Expresion Incorrectly Terminated'.

I cant see what is wrong. Also when I filter the table I need it to be able to do the following.
I choose the field 'names' from the combobox1.text and choose 'andrew' from the edit1.text. So It should only show all the records with andrew in the name field. My problem is the name andrew might be the third word in the field. eg. 'Mike and Andrew' so it needs to check anywhere in the field If you can get me up and running with this problem I will increase the points to you.
Thanks.
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
OK,

Incorrectly terminated:

You have to add some quotes because it's a string:

       Table1.Filter := combobox1.text + ' = ''' + edit1.text + '''';

Second:
 If you don't want partial matches to be displayed:

        Table1.FilterOptions := [foNoPartialCompare];

Anything else wrong, or any other stuff stuck? :-)

Well, good luck!

Regards,
Matvey
0
 

Author Comment

by:mykyl
Comment Utility
Same error Im afraid.

Here is the code for the form.

unit Findsall;

interface

uses
  SysUtils, Windows, Messages, Classes, Graphics, Controls,
  StdCtrls, Forms, DBCtrls, DB, DBGrids, DBTables, Grids, ExtCtrls, MKTable,
  DBFilter;

type
  TFindall = class(TForm)
    Table1DescriptionofFind: TStringField;
    Table1FindersName: TStringField;
    Table1DateFindMade: TDateField;
    Table1FindComposition: TStringField;
    Table1AgeCirca: TStringField;
    Table1Information: TStringField;
    Table1Depth: TStringField;
    Table1MapGridReference: TStringField;
    Table1WeatherConditions: TStringField;
    Table1SoilType: TStringField;
    Table1SoilCondition: TStringField;
    Table1Recoveredby: TStringField;
    Table1SiteName: TStringField;
    Table1FindCondition: TStringField;
    Table1EstimatedValue: TCurrencyField;
    Table1FindGraphic: TGraphicField;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Panel2: TPanel;
    Table1: TTable;
    Edit1: TEdit;
    ComboBox1: TComboBox;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Findall: TFindall;

implementation

uses Finds;

{$R *.DFM}

procedure TFindall.FormCreate(Sender: TObject);
begin
  Table1.Open;
end;

procedure TFindall.Button1Click(Sender: TObject);
begin
Table1.Filter := combobox1.text + ' = ''' + edit1.text + '''';
table1.filtered := true;
end;

end.


And Im Not sure you understand what I was Meaning. I do want partial matches but I want it anywhere within the field not just from the beggining of the field.

I hope you can help.

Thank you.
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
I'm sorry, but it seems that at least my Delphi 3 has a bug with partial matches:

Basicly, you can use a wildcard, like * for example:
   Table1.Filter := 'address1 =''P.O. Box*''';

But somehow it doesn't work for the * charecter when in front of the value, like *Andrew won't produce 'Mike and Andrew'. But you can try that still if you have other Delphi version - maybe it's a bug of D3??

But anyway, you can replace this filter with an SQL query:

Put  a TQuery component instead of TTable, and in it's SQL property write something like this:

Select * from vendors where Address1 LIKE '%Drive%' ;

And to build this property at run time you write something like:

With Query1 Do
Begin
  Close;
  SQL.Clear;
  SQL.Add('Select * from <TableName> where ' +
                 ComboBox1.Text + ' LIKE ' + '''%' + edit1.text + '%'';');
  Open;
  end;
end;

In the place of <TableName> you put your table name, like Vendors.

Also I can see you have a DateFields and a CurrencyField. If you search within these once, you don't enclose in quotes the searched value - Edit1.Text.

Well, I hope this solves. -SQL must be the key here.
0
 

Author Comment

by:mykyl
Comment Utility
Now you have lost me. I have no idea about using the sql components. The combobox1.text holds the field names and the edit1.text holds whatever the user wants to find.

In other words I want to find 'ford' in the 'car' field so I use the combobox1.text to choose the 'car' field then I enter ford in the edit1.text. It should then after pressing a button list all the ford in the field car throughout the database regardless of of whether ford is within the field. (The field might say 'I own a ford falcon').
How would I do this? Any Ideas. If you can answer this You can have al my points that are left.
Thank You.
Mike
0
 

Author Comment

by:mykyl
Comment Utility
For the button I have inserted the code :=

With Query1 Do
Begin
  Close;
  SQL.Clear;
  SQL.Add('Select * from <TableName> where ' +
                 ComboBox1.Text + ' LIKE ' + '''%' + edit1.text + '%'';');
  Open;
  end;
end;

All this does is give me the error := 'Invalid use of keyword.
Token:

What does this mean?

In the sql property Ive put := 'SELECT * FROM Finds'(Finds is the name of the table.

Hopefully this will help you to help me .
Cheers

Mike Ross
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
Hmmm, so do you think you can't use SQL here?

Do you know how to use TQuery component?

Let's say you want to find 'ford' in the field 'Car' of your table 'Finds.db'. The SQL will look like this:

SELECT * FROM Finds WHERE Car LIKE '%ford%';

-If you type this exactly and run it, you get the results you want.
Now, if we want to build such a statement at Run time, depandnding on the info from the combo and the edit, we should do something like:

procedure TFindall.Button1Click(Sender: TObject);
begin
  With Query1 Do  
  Begin
    Close;
    SQL.Clear;
    SQL.Add('Select * from Finds where ' +
                    ComboBox1.Text + ' LIKE ' + '''%' + edit1.text + '%'';');
    Open;
  end;
end;

So if you chose 'Car' From the combo and write 'ford' in the edit, this procedure builds the SQL statement exactly like the original above this code.

If the error you say occures, it means that there is a small error in the SQL statement. If you get this error, watch the SQL property in debug and look what's wrong with the statement.

Well, I still think that SQL is the key here, because this is the thing for giving filtered lists, finding values etc.

Regards,
Matvey
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mykyl
Comment Utility
Im afraid I still get the same error. After stepping through the error occurs at the 'open;' section (or at least looks like it any ideas. By the way I have 145 points for whoever can solve this.
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
This is very strange - I try it here (with D3), and I do everything like you have - an edit a combo, just a different table, and it works well! I really don't understand what can cause this error, so the best thing will be is to send me this form and the tables, or, if it's not too big, the whole project. I'm sure I can fix this error.

Or at least try to watch the value of the Query1.SQL property at runtime - that can discover this error to you. As I said, it works fine here.

So send me that please and I'll fix the error... (I also have D2 if you're useing it)

Regards,
bosism@netvision.net.il
0
 

Author Comment

by:mykyl
Comment Utility
I am using delphi3. I seem to have fixed the problem now. I found (eventually) in the help file that the combobox1.text field names have to be in the folowing format to work on local tables:= <tablename>."Car"      This was the only way tomake it work. Allthough it still seems to be case sensitive which is not what I want and the combobox1.text looks untidy with that.
Any ideas .

Cheers

Mike
0
 
LVL 1

Expert Comment

by:rene_moeller
Comment Utility
Have a look at the table property "FilterOptions". "foCaseInsensitive" should be set to "True"
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
Phew! I though I'm never gonna find it!

There is no case insensitivity option in SQL, BUT you can do it in the following way:

SQL.Add('SELECT * FROM Vendors WHERE UPPER(' + ComboBox1.Text + ') LIKE ' + 'UPPER(''%' + edit1.text + '%'');');

You see what I mean? This checks both values in uppercase so there is no case sensitivity problem.

FeilterOptions and any other Filter properties don't effect the SQL property at all. In the TQuery component the Filter property is meant to filter the results of the SQL - funny isn't it? :)

Hope this helps at last!
0
 

Author Comment

by:mykyl
Comment Utility
Excellent. Thank you very much. Now all I have to figure out is how to print the information out. Anyway here are the prommised points and when my points have increased maybe I can ask you for help on printing the grid.

Thanks

Mike Ross
cheers@viprocess.co.uk

0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
Ohh, reeally, you didn't have to give me all your points, this question isn't so hard... If you want to ask me something else I'll be glad to help you even if I don't receive points - what for do I need them anyway? :)

Just say what you need and I'll see if I can help you. You can email me...

Cheers,
Matvey
0
 

Author Comment

by:mykyl
Comment Utility
I did say I would give you all the points and I keep my word.
Can you maybe point me in the right direction somewhere I can find out about printing the dbgrid or at least how to send the information from it to qreport. (Although using a normal form the graphic image does not show up on the report).

Cheers
Mike Ross
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
OK, I'll try to find out.

I've made reports myself not a long time ago, but I didn't use any components for that - just draw them on Printer.Canvas. I suggest you try to do the same, though it's a pice of work, but it's nice when it's done.

I'll see how can I do it through components...
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
APK file name 7 63
How to convert wav to mp3 in delphi 9 137
Machine not responding during CopyFile() 3 79
SUM 2 INTEGER ARRAYS INTO 1 10 56
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now