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

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

Database Navigation

hi !
i would like to make it possible for my users to search in the database
for special names or directly jump to a customer no. i use the normal
delphi 6.0 enterprise data access components from the "data control"
tab. can i do this with dbnavigator ? is it possible to show only special
result sets instead of the whole database ?`

thanks
fuluppi

any code is appreciated
0
fuluppi
Asked:
fuluppi
  • 4
  • 3
  • 2
  • +1
1 Solution
 
rbarlowCommented:
There are MANY different ways for going about this. A commonly accepted way of doing this would be with 2 forms (but you could combine the functionality if you like).

Form 1 is a search form. On this form you can specify criteria by which to search for a customer. From the values entered in the criteria fields, you can run a query against the database, with parameters in the query. The query can be built either dynamically based in what fields were entered, or you can use a generic query that includes all the criteria parameters in such a way that if the user does not enter a criteria value for that field, it will not be filtered out. eg.

SELECT *
  FROM Customer
 WHERE
    Surname LIKE :Surname AND
    CustomerNo LIKE :CustNo

When setting these fields, set them to '%' if the user did not specify critera. From the results of this query, populate a grid so the user can then select one of the many results that were returned. When the user selects one of the results, then show the details of that customer on a different form.

On this second form, the user can insert new, update existing or delete existing customers.

Anyway, this is just a common way of doing this sort of thing. Best to break forms down into their areas of functionality instead of trying to bung as much as you can on one form.

Hope this helps.

Regards,
Raymond Barlow
0
 
fuluppiAuthor Commented:
hi !
i know how sql querys work but i have no idea how to use them with my dbNavigator.
this stupid thing seems to access the whole database. i use aMySQL Database
with MySQLDAC, a MySQL Table and a TDataSource where my fields are connected
to. Where can i enter a criteria ?

thanks
fuluppi
0
 
esoftbgCommented:
Delphi syntax: function Locate(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions): Boolean; virtual;

example for directly jump to a customer no:
TableCustomers.Locate('Cust_Num', Edit1.Text, []);

>is it possible to show only special result sets instead of the whole database ?
yes it is possible using properties:

TableCustomers.Filter := '........';
and
TableCustomers.Filtered := True;

emil
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!

 
kretzschmarCommented:
>Where can i enter a criteria ?
you should provide an editbox and a button

in the buttonevent you could use a filter like

mysqltable.filter := 'yourfieldname = '+quotedstr(edit1.text);  //for stringfields
mysqltable.filtered := true;

there are also other possibilities

meikl ;-)
0
 
kretzschmarCommented:
hi emil, a bit faster :-))
0
 
esoftbgCommented:
hi meikl, i am few seconds faster again ;-))
0
 
fuluppiAuthor Commented:
i love you all  ;-) now i just have to find out how to make a kind of full text search through
all fields. i don't want to name each field. isn't there something like
*='*test*' ? mySQLDac wants the explicit field name for the filter :-(
0
 
kretzschmarCommented:
>all fields. i don't want to name each field. isn't there something like
>*='*test*' ? mySQLDac wants the explicit field name for the filter :-(

you must name all fields (this could be automated)

the result of your filter should shown like

mysqltable.filter := 'yourfieldname1 like '+quotedstr('%'+edit1.text'%')+
                       ' or yourfieldname2 like '+quotedstr('%'+edit1.text'%')+
                       ' or yourfieldname3 like '+quotedstr('%'+edit1.text'%')+
                       ' or yourfieldname4 like '+quotedstr('%'+edit1.text'%')+
                       ' or yourfieldname5 like '+quotedstr('%'+edit1.text'%')+
                       ... to be continued

meikl ;-)
0
 
kretzschmarCommented:
btw. missed some + above :-))
0
 
fuluppiAuthor Commented:
bahhhh. i have a table with 40 fields and i'm soo lazy ;-) but if theres no other solution i have to do it

thanks a lot ;-)
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now