Finding stuff in Database

[Petrus Vorster]

Hi All.

You have helped me make great progress.
Ok, now i have 3 simple dbf files and it works just perfectly Tdblookupcombo and displys correctly in the grid and i can use the navigator.

The next step is to allow the user to find and display a record.
I have a table with a few columns: Tracking_numbers, date in_, surname etc.
I wan the user either enter the tracking number in the Tedit and then search the database for that and display it in a simple grid. (or choose surname, Dates or any of the columns)

I assume this is where the Sql commands come in? OR can one still use the Tbdlookupcombo with say Autocomplete or what would you suggest?

Great thanks for everything.

-Peter

[Nicole]

I am afraid, you will need to write this step by step: Define edit-fields, read their content and generate SQL from them.
Do not forget a validity check of the text.

There is a very smart way to make tables sortable on click as Excel.
Do not use DBGrid but IBDynamicGrid.

Does not allow searching as you want. But is added within minutes.

[TRon]

I assume this is where the Sql commands come in? OR can one still use the Tbdlookupcombo with say Autocomplete or what would you suggest?

That sounds like the use of SQL queries with DBase files is self-evident but unfortunately it is not. The (standard) DBF support in FPC/Lazarus does not have SQL support implemented (DBase never did as well).

If you wish to use SQL with DBase files you would either have to choose a database engine that supports both SQL and DBF files or rely on a 3th party implementation that adds SQL query support for DBase file (I am not aware of either of them).

Therefor it will all be manual labour if sticking with DBase DBF files or better yet switch to something more modern such as SQLite. DBF support is rudimentary and was only added to be able to upgrade to a more modern implementation and/or better supported eco-system.

[Zvoni]

What's wrong with the Locate-Method of TDataSet?

[Thaddy]

@TRon

Although I gave a similar answer in another thread opened by the same OP, To be fair, dBase supports SQL (and the powerfull, lost in time,  QBE) since dBaseIV.
That is since 1988...

But it is better to not use the format, since its use is limited. That part of our arguments still stands.
https://en.wikipedia.org/wiki/DBase

But, although FPC has pretty good and completely native dBase support, including its index formats, I think the SQL part is not implemented, I may be wrong here, but I don't think so.
For SQL support you need an ODBC driver which comes standard with any Windows in a 32 bit only version.

It would be great if I am proven wrong, because that would mean there would be a native SQL query engine somewhere lurking in the standard code distribution. (do not refer to the jv one, which is pretty limited, but can be installed and has its own format)

[silvercoder70]

@Thaddy, did you like QBE? Or were you being sarcastic?

To the OP... here is another link to consider - https://wiki.freepascal.org/Lazarus_Tdbf_Tutorial

While I was around when Paradox used QBE, I cannot remember capabilities of dbase and querying the DB.

[Petrus Vorster]

Greetings everyone.

Please keep in mind i am an absolute beginner in Pascal.
I used to work with SQLITE over in Freebasic, but I am learning Pascal at a steep curve.

At the moment i am dealing with visual data controls which is new to me.

Explain to me, like you would explain it to a kid on his first week on a pc.
I know you are all very skilled here and currently i dont know anything.
I am reading through the Help and Wiki, and your assistance is of great help.

I will work my way up again to Sqlite, but for now let me just grasp the DBF and how to retrieve info from it.

I do not see a control called Tdataset. There are a few others available for selection.

Please go slow, I am picking up at great speed, but it will be a considerable time before I  will be up to speed like I used to be in Freebasic.

-Peter
 

[Zvoni]

*snip*
I do not see a control called Tdataset. There are a few others available for selection.

Please go slow, I am picking up at great speed, but it will be a considerable time before I  will be up to speed like I used to be in Freebasic.

-Peter

Code: Pascal  [Select][+][-]

1. MyBoolean:=MyDBLookupComboBox.DataSource.Dataset.Locate('SomeField', 'whatever',[loCaseInsensitive]);
2. If MyBoolean Then Writeln('Record found');

[TRon]

@Petrus:
I do not know how up to date it is but here you can download the original documentation of TDBF component. It contains some important remarks.

Other than that TDBF itself is a TDataset descendant so most events and methods also apply for/to TDBF.

Also note that you can find examples of its basic use, f.i. here.

Here is a step-by-step explained visual designed DBF database example video.

[Petrus Vorster]

Greetings all

This code :

Code: Pascal  [Select][+][-]

1. MyBoolean:=MyDBLookupComboBox.DataSource.Dataset.Locate('SomeField', 'whatever',[loCaseInsensitive]);
2. If MyBoolean Then Writeln('Record found');

Gives me an error.

But i can see what the process should be. First we determine if the record exist.

Let me try this: I want the user to enter a Tracking Number in a normal TEdit box.
Then i need to search the database and populate a normal Tlistview. (none data aware.)

Now that one can find the record and you know its there, the next step is to read all the fields and populate the listview.

Once I get that working, i should be able to do the write and delete easily.

Regards,

Peter

[TRon]

Gives me an error.

No code, no code-snippet (other than those 2 lines), no exact error message.

No idea how that is able to help us help you

[Petrus Vorster]

Apologies mate

I am trying to use the text in the Tdbflookupcombobox or any Tedit and check if the entry exist in the database.
This is done under clicking a button.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   Mytrack:string;
4.   Mybool:boolean = False;
5.  
6. begin
7.   mytrack:=dblookupcombobox3.Text;
8.   mybool:=dblookupcombobox3.DataSource.DataSet.Locate('TRACKING_N', mytrack,[loCaseInsensitive]);
9.  
10. end;                            

This returns an error of ACCESS VIOLATION.
I have attached a screen clip.

[Zvoni]

Is "TRACKING_N" even an available Field in your Dataset/DataSource?
What are your Keyfield and ListField for the LookupCombo?

[Petrus Vorster]

Hi

I attached a clip of the properties.
It looks correct to me.

-Peter

[Petrus Vorster]

I found it.

I never selected the datasource in the DBlookupcombobox.
I am an idiot.

Ok, now that i can verify the entry exists, how do I show all the fields for that tracking number into a grid?

Many thanks

Peter