Help with Query at run time

[Columbo 1]

Thanks Gary,  I have fixed the problem of getting the images to display but I am still having a problem with the TDBEdit, (dbeRecno), getting the proper record number.

 

In the database, the first column is 'record' and it is and Integer value.  If I use the prior or next buttons on the DBNavigator, it works fine and the record numbers display properly in the dbeRecno.  However, if using the DBNavigator I advance to the 6th record and then enter a name in the search box, (example Climatius), it will jump to record number 9 which is correct.  If I then press the Prior button on the DBNavigator it will jump to record 5 instead of 8.  Again, because the search box is using my procedure instead of the DBNavigator procedure.  I have played with this the whole day today and I can't figure out a solution.  I'm going to take a break before torturing myself further.

 

[Gary 17]

Couple of things to check. Is the dbEdit field set properly? Are you still using the parameterized query? If so there will only be one record in your dataset, so no records for the navigator to move to, in fact if you are using the parameterized query the navigator is pointless it only works on a set of records.

 

[Columbo 1]

Ok, Thanks Gary.

[Columbo 1]

Hi Gary,  I have it working now.  I created my own buttons for First, Previous, Next and Last.  They are working well and no problems using either the buttons or the search box.  They both work with each other.  A few more touch ups and I'll be ready to try to compile it.  I need to look up the docs on compiling though.

 

Cheers!

 

 

 

[Stano 140]

9 hours ago, Columbo said:

Thanks Gary,  I have fixed the problem of getting the images to display but I am still having a problem with the TDBEdit, (dbeRecno), getting the proper record number.

 

In the database, the first column is 'record' and it is and Integer value.  If I use the prior or next buttons on the DBNavigator, it works fine and the record numbers display properly in the dbeRecno.  However, if using the DBNavigator I advance to the 6th record and then enter a name in the search box, (example Climatius), it will jump to record number 9 which is correct.  If I then press the Prior button on the DBNavigator it will jump to record 5 instead of 8.  Again, because the search box is using my procedure instead of the DBNavigator procedure.  I have played with this the whole day today and I can't figure out a solution.  I'm going to take a break before torturing myself further.

 

Nobody knows what you're doing there. If you type a name in the search box, you also have to make sure that the record is set up correctly in the DataSet. Preferably using MyDataSet.Locate(PKeyName, PKeyValue, [loCaseInsensitive]); I'm assuming there's a bug there.

[Columbo 1]

Thanks Stano, I have it working now since I created my own buttons.  The buttons and the search box are working fine.  I also disabled the btnStart and btnPrevious buttons if it is on the first record and the btnLast  is disabled if it is on the last record.  All working fine now.  I am going to look at how to compile it now.

 

Edited December 19, 2023 by Columbo

[Gary 17]

How is all working fine different than compiled?

[Stano 140]

1 hour ago, Columbo said:

Thanks Stano, I have it working now since I created my own buttons.  The buttons and the search box are working fine.  I also disabled the btnStart and btnPrevious buttons if it is on the first record and the btnLast  is disabled if it is on the last record.  All working fine now.  I am going to look at how to compile it now.

 

I personally wouldn't add buttons to a standard component. You need to use what is there. DBNavigator takes care of the button control automatically.
You're supposed to use the DataSet.Locate() function in SearchBox.OnChange(?). Or another appropriate event - we don't know what you do where. I don't use SearchBox. I prefer ComboBox. That way you won't have to worry about anything. No additional buttons. Don't forget that you'll be using DBNavigator in multiple places. So don't break it.

[Columbo 1]

23 hours ago, Gary said:

How is all working fine different than compiled?

Guess your right Gary.  I had been under the impression that after the coding of an application you had to compile it into a distributable package that used all of those other files that are created in the project but I see now that I can just take the .exe file along with my DB and image files and it woks.   I was going to send you a copy of the app but it is 10,600KB.  I couldzip it and upload it to one of my web sites and send you a link to download it?

 

Cheers!

 

 

[Columbo 1]

Thanks Stano. 

 

Quote

You're supposed to use the DataSet.Locate() function in SearchBox.OnChange(?). Or another appropriate event

I was not aware of the DataLocate function.  I'll look at it and see how it works.  Maybe I can do a version of the app using that.

 

[Stano 140]

1 hour ago, Columbo said:

I was not aware of the DataLocate function. 

In that case, I recommend you to go to "help" and always check all the properties and methods of what you are interested in. You want to use. In this case, it's DataSet. After that, T(FD)Table and T(FD)Query are also included. They are its descendants. They inherit from it.
This is a very best and recommended practice!

[Columbo 1]

I was just looking at the Locate function.  I will check that out DataSet, along with  T(FD)Table and T(FD)Query.

 

Thanks again Stano. 

[Gary 17]

Yes, I would like to see the data. As Stano said, there is another way to accomplish your objective, always is in Delphi.

Using the Query is always better in a Client/Server environment. In your use case your data is local and I don't think with the small amount of data the performance would suffer. It is muck easier in this case to use DB controls and a Table. No SQL at all. You use the Locate method of the DataSet to search for specific items, and that allows you to hook up the navigator and just scroll through the table.

[Columbo 1]

I am going to try to see if I can make the changes to the application.  I will wait just a bit longer to get you the link so that you can get the DBNavigator version if I am successful in implementing the changes.

 

 

[Stano 140]

Hm, I've opined that you should get rid of your DBNavigator altogether. You're supposed to make do with a standard one. Without the added buttons!

Everything is linked to the record search in SearchBox. You chose the wrong methodology.

Edited December 21, 2023 by Stano

[Columbo 1]

Hi Stano, 

Quote

I've opined that you should get rid of your DBNavigator altogether.

I thought you said to use DBNavigator?

Quote

You need to use what is there. DBNavigator takes care of the button control automatically.

Maybe I am misunderstanding what you are recommending?

 

Edited December 21, 2023 by Columbo

[JonRobertson 39]

He meant don't use a modified TDBNavigator. You said at some point something about adding your own buttons.

 

If the buttons provided by TDBNavigator provide the functionality you are wanting but you weren't able to get it to work as intended, that can be corrected. If you want additional buttons to provide functionality that is not built into TDBNavigator, that is a different story.

 

For what you are doing, you should get the project working using single dataset component and a single datasource component. Based on your posts, it seems as though you may be using multiple datasets.

 

I would use a separate dataset to load the picture. But that is easier to implement once the project works using a single dataset.

 

 

[Stano 140]

9 minutes ago, Columbo said:

I thought you said to use DBNavigator?

Yes. But you are using a modified DBNavigator. See also the reply from JonRobertson.

[Columbo 1]

I suppose there has been some kind of confusion. I am probably not making myself clear in my posts. I had created my own buttons and they worked well as did my search box.  Then you said that I shouldn't make my own buttons and that I should use the DBNavigator.  Gary recommended that I should take your advice and so I agreed.

My response to Gary was:

Quote

I am going to try to see if I can make the changes to the application.  I will wait just a bit longer to get you the link so that you can get the DBNavigator version if I am successful in implementing the changes. 

So I am now working on making changes to the application to use the DBNavigator.  It is working as far as moving back and forth through the records but it is not loading my images. The images are loaded using the contents of the TDBEdit (dbeName) after the record has been retrieved but I am not sure what DBNavigator uses to retrieve the record. I am assuming that it is incrementing or decrementing the record number.  I am currently trying to figure out how to resolve this image issue.  I will work on that today.

 

Edited December 21, 2023 by Columbo

[JonRobertson 39]

Take a look at the AfterScroll event of the dataset. If the code that loads an image is not already a separate function/procedure, move it to one. Then call that from the AfterScroll event.

 

https://docwiki.embarcadero.com/Libraries/Sydney/en/Data.DB.TDataSet.AfterScroll

[Gary 17]

I think JonRobertson has identified the problem. In his post he recommends you use one dataset and one datasource. Do you have multiple dataset's? If you are still using the FDQuery with parameters, as well as a table that could be the problem. They have different use cases and mixing them will cause you frustration!

 

The Parameterized query will only ever return one record. You cannot use a DBNavigator since there is only one record, there is no prior or next record, the one record is the first as well as the last. This is fine for searching a table. When your user presses the search button you can update your query from the edit and load the data as well as your images. However, with an interface like this how will your users see any other entries unless they know, and as you mentioned can spell the name they are looking for? You cannot navigate your database table with a parameterized query. If you managed to get this working with the query you have much more going on.

 

For what you want you should use a Table or "Select *" in your query. Now your dataset will have all the records from the table and you can hook up a DBNavigator. As you use the buttons to move from one record to another the Dataset's (Table or Query since they both descend from TDataset) AfterScroll event will be fired, you can load your images or any other custom data display you have here.

 

To search for a particular record, you use the Locate method of the dataset passing the value from your search edit.

if not
  DataSetName.Locate('name', Edit.Text, [loCaseInsensitive]);
then
  ShowMessage(Edit.Text + ' not found.');

Locate returns true or false so you can check it. If successful the dataset will move to the record, your data aware controls will display the data and the Dataset's AfterScroll event will be called and the images will be loaded.

You should post your code with your questions, at least the procedure you are working on. 

[Stano 140]

Put a picture of the form here at the time of design. Let us have some idea of what you are doing and probably how you are doing it.
That way we're just guessing from the coffee.

[Columbo 1]

All done!   Seems to be working as it should.  I have attached a link so you can see what I was doing.  Given that it is my first application in Delphi there is probably room for a lot of improvement but it was intended as a learning project.  Also, I don't know what screen resolution you are using but this app was for 1280 x 720.

 

I removed the link for now.

Edited December 22, 2023 by Columbo

[Stano 140]

I tried the link. Just a blip and nothing more.
Put it here as an attachment.

[Gary 17]

I was able to get it. However it will throws an error when trying to open database. The database opens in DB Browser so it's a valid database.

You should include the project files.