Help with Query at run time

[Lajos Juhász 291]

In order to work with jpg image format you must add the  jpeg unit to your uses.

 

IF you add a TImage named imgName then you can write:

 

imgName.Picture.LoadFromFile('D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\'+ sName + '.jpg');

[Columbo 1]

29 minutes ago, Lajos Juhász said:

In order to work with jpg image format you must add the  jpeg unit to your uses.

 

IF you add a TImage named imgName then you can write:

 

imgName.Picture.LoadFromFile('D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\'+ sName + '.jpg');

Thank you Lajos.  That's great!   I'll give that a try.  I had already added jpeg to the uses but I was unsure as to how to use the LoadFromFile.  Thank you so much.

 

[Columbo 1]

Hi Lajos,  I'm still getting a problem loading the image.  I have a TImage named dinoImage so using your suggestion I added:

 

dinoImage.Picture.LoadFromFile('D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\'+ sName + '.jpg');

 

When I run it I get am error saying 'Cannot load file. Image cannot be found '.jpg'

 

The path is correct but it seems as if it is not adding the sName but only the ',jpg'.

 

Suggestion?

 

 

[JonRobertson 67]

11 minutes ago, Columbo said:

The path is correct but it seems as if it is not adding the sName but only the ',jpg'.

 

That code would add sName. But sName may be an empty string ('') at that point of code execution.

 

Have you confirmed that sName contains a value when LoadFromFile is called?

 

Have you set a breakpoint to examine sName before LoadFromFile is called? Or added a ShowMessage(sName) before calling LoadFromFile?

[Gary 18]

Should work

Edited December 15, 2023 by Gary

[weirdo12 19]

3 hours ago, Columbo said:

dinoImage.Picture.LoadFromFile('D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\'+ sName + '.jpg');

 

You can build your file name like this as well:

 

var  file_name : String;

 

file_name := TPath.Combine('D:\Delphi_11_Community\MyProjects\Dinobase\dino_images', sName);

file_name := TPath.ChangeExtension(file_name, "jpg");

 

dinoImage.Picture.LoadFromFile(file_name);

 

 

[Columbo 1]

Hi Jon,  I have 10 TEdits that are populated with info from the DB.  I have another TEdit that I use as a search box.  When I type a name into the search box and press ENTER the contents of the search box is placed into the variable sName.  If I comment out the LoadFromFile line and run the program all of the TEdits are populated.  That would not happen if sName was empty.  After the 10 TEdits are populated, I do the LoadFromFile using the same sName.  I do not clear the variable sName before calling the LoadFromFile.  The image that it looks for has the same name as the name stored in the sName variable.  In other words, If I am searching for the name 'Anomalocaris', the image file that it should load is 'Anomalocaris.jpg'.

 

here is my query code:

Query.SQL.Text := 'SELECT Name, OtherFieldsAsNeeded from dino where Name = :PName';Query.ParamByName('PName').AsString := sName;Query.Open;edtName.Text := Query.FieldByName('Name').AsString;
	

 

[Columbo 1]

Hi weirdo12,

Actually I tried something similar to that before.  I assigned the path to a variable like so:

imagePath := 'D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\'+ sName + '.jpg';

 

but...  had the same problem.  I have the TImage in a TabbedSheet but that shoudn't have anything to do with finding the image file.

 

[Remy Lebeau 1375]

7 minutes ago, Columbo said:

I have 10 TEdits that are populated with info from the DB.  I have another TEdit that I use as a search box.  When I type a name into the search box and press ENTER the contents of the search box is placed into the variable sName.  If I comment out the LoadFromFile line and run the program all of the TEdits are populated.  That would not happen if sName was empty.  After the 10 TEdits are populated, I do the LoadFromFile using the same sName.  I do not clear the variable sName before calling the LoadFromFile.  The image that it looks for has the same name as the name stored in the sName variable.  In other words, If I am searching for the name 'Anomalocaris', the image file that it should load is 'Anomalocaris.jpg'.

The only LoadFromFile() code you have shown us so far is in the form's OnCreate event before the SQL query is performed.  Please show your updated code that calls it after the SQL query is performed.

7 minutes ago, Columbo said:

here is my query code:

 

Query.SQL.Text := 'SELECT Name, OtherFieldsAsNeeded from dino where Name = :PName';Query.ParamByName('PName').AsString := sName;Query.Open;edtName.Text := Query.FieldByName('Name').AsString;
	

 

That is just a copy/paste of the example code I gave you originally.  What does your REAL code look like?

Just now, Columbo said:

Actually I tried something similar to that before.  I assigned the path to a variable like so:

imagePath := 'D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\'+ sName + '.jpg';

but...  had the same problem.

Did you put a breakpoint on imagePath, or show it in a ShowMessage(), or anything to verify it is holding the correct path before you then pass it to LoadFromFile()?

Just now, Columbo said:

I have the TImage in a TabbedSheet but that shoudn't have anything to do with finding the image file.

No, it doesn't.

Edited December 16, 2023 by Remy Lebeau

[Columbo 1]

Hi Jon,

Quote

That code would add sName. But sName may be an empty string ('') at that point of code execution.

 

Have you confirmed that sName contains a value when LoadFromFile is called? 

I have a 10 TEdits that I populate with info from the DB. I also have a TEdit that I use as a search box.  When I type a name into the search box and press ENTER, it places the text, (the name to search for), into sName.   sName is then used in the query to find the appropriate record and populate the other 10 TEdits.  The image for that record has the same name as the name in sName.  If I comment out the LoadFromFile line all of the other TEdits get populated and that would not happen if sName was empty.

 

Edited December 16, 2023 by Columbo

[Columbo 1]

Here is the code.

 

unit Dinobase_u;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.Imaging.jpeg, Vcl.ExtCtrls,
  Vcl.StdCtrls, Vcl.ComCtrls, System.ImageList, Vcl.ImgList, FireDAC.Stan.Intf,
  FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys,
  FireDAC.Phys.SQLite, FireDAC.Phys.SQLiteDef, FireDAC.Stan.ExprFuncs,
  FireDAC.Phys.SQLiteWrapper.Stat, FireDAC.VCLUI.Wait, FireDAC.Stan.Param,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client, Vcl.DBCtrls, Vcl.Buttons;

type
  TfrmDino = class(TForm)
    pnlTitlebar: TPanel;
    Image1: TImage;
    pnlHeader: TPanel;
    Image2: TImage;
    lblName: TLabel;
    edtName: TEdit;
    lblMeaning: TLabel;
    lblPronounce: TLabel;
    edtPronounciation: TEdit;
    edtPeriod: TEdit;
    lblPeriod: TLabel;
    edtMeaning: TEdit;
    edtMainGroup: TEdit;
    lblMainGroup: TLabel;
    pnlFooter: TPanel;
    Footer: TImage;
    edtSize: TEdit;
    lblSize: TLabel;
    edtLived: TEdit;
    lblLived: TLabel;
    edtDiet: TEdit;
    lblDiet: TLabel;
    edtFossils: TEdit;
    lblFossils: TLabel;
    memFactfile: TMemo;
    PLTabSheet: TPageControl;
    TabSheet1: TTabSheet;
    SizeComp: TTabSheet;
    dinoImage: TImage;
    dinoSize: TImage;
    conPLife: TFDConnection;
    tblPLife: TFDTable;
    DBNavigator1: TDBNavigator;
    Datasource1: TDataSource;
    edtRecno: TEdit;
    lblRecno: TLabel;
    BitBtn1: TBitBtn;
    FDQuery1: TFDQuery;
    edtSearch: TEdit;
    btnSearch: TButton;
    lblSearch: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure btnSearchClick(Sender: TObject);
   // procedure displayimages;
    private
    { Private declarations }
  public
    { Public declarations }
     procedure getdbrecord;
  end;

var
  frmDino: TfrmDino;

 implementation


{$R *.dfm}

procedure TfrmDino.BitBtn1Click(Sender: TObject);
begin
Application.Terminate;
end;

procedure TfrmDino.btnSearchClick(Sender: TObject);
begin
getdbrecord;
end;

procedure TfrmDino.FormCreate(Sender: TObject);
var
img1: String;
img2: String;
begin
WindowState := wsMaximized;
getdbrecord;
end;

procedure TfrmDino.getdbrecord;  //Get record procedure
var
sName: String;   //holds name of dino record to retrieve
img1: STRING;
img2:String;
begin
img1 := sName + '.jpg';
img2 := sName + '_size.jpg';
sName := edtSearch.Text;
FDQuery1.SQL.Text := 'SELECT record,name,meaning,pronounce,period,maingroup,size,lived,diet,factfile FROM dino Where name = :PName';
FDQuery1.ParamByName('PName').AsString := sName;
FDQuery1.Open;
 //place data into edtName
edtRecno.Text := FDQuery1.FieldByName('record').AsString;
edtName.Text := FDQuery1.FieldByName('name').AsString;
edtMeaning.Text := FDQuery1.FieldByName('meaning').AsString;
edtPronounciation.Text := FDQuery1.FieldByName('pronounce').AsString;
edtPeriod.Text := FDQuery1.FieldByName('period').AsString;
edtMainGroup.Text := FDQuery1.FieldByName('maingroup').AsString;
edtSize.Text := FDQuery1.FieldByName('size').AsString;
edtLived.Text := FDQuery1.FieldByName('lived').AsString;
edtDiet.Text := FDQuery1.FieldByName('diet').AsString;
memFactfile.Text := FDQuery1.FieldByName('factfile').AsString;
//displayimages
dinoImage.Picture.LoadFromFile('D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\' + sName + '.jpg');
//edtSearch.clear;
end;

end.

[Columbo 1]

Ok,   I think that I might have it working now.  I'll do a little more testing to confirm it.

 

 

[Remy Lebeau 1375]

OK, first problem...  what do you see in the following snippet? 

46 minutes ago, Columbo said:

 

procedure TfrmDino.FormCreate(Sender: TObject);
var
img1: String;
img2: String;
begin
WindowState := wsMaximized;
getdbrecord;
end;

 

(ignoring the unused img variables here)

You are calling getdbrecord() in the Form's OnCreate event! What value does edtSearch hold when the Form is first created, BEFORE the user has a chance to type anything into it?  Is it blank?  Does it have a default value?  We don't know, but I'm guessing it is not a valid record name.

 

OK, next problem... see anything wrong with the following snippet?

46 minutes ago, Columbo said:

 

procedure TfrmDino.getdbrecord;  //Get record procedure
var
sName: String;   //holds name of dino record to retrieve
img1: STRING;
img2:String;
begin
img1 := sName + '.jpg';
img2 := sName + '_size.jpg';
sName := edtSearch.Text;

 

The sName variable is BLANK when you use its value to assign the img1 and img2 variables!

Not that it really matters here, since you are not actually using those img variables for anything useful to begin with, but it is still something to be aware of so you don't make this same mistake again in the future.

 

Lastly, just on a side note, in the following code snippet...

46 minutes ago, Columbo said:

 

...
FDQuery1.SQL.Text := 'SELECT record,name,meaning,pronounce,period,maingroup,size,lived,diet,factfile FROM dino Where name = :PName';
FDQuery1.ParamByName('PName').AsString := sName;
FDQuery1.Open;
//place data into edtName
edtRecno.Text := FDQuery1.FieldByName('record').AsString;
...

 

You should be checking that TFDQuery.Open() actually found a matching record before you access the Field values.  If TFDQuery.Open() does not find a match, the TFDQuery.Bof and TFDQuery.Eof properties will both be true, eg:

...
FDQuery1.SQL.Text := '...';
...
FDQuery1.Open;
if FDQuery1.Bof and FDQuery1.Eof then
begin
  // no matching record found, do something...
end else
begin
  // place field data into tedit controls...
end;

Now, let's put all of that together...

procedure TfrmDino.BitBtn1Click(Sender: TObject);
begin
  Close;
end;

procedure TfrmDino.btnSearchClick(Sender: TObject);
begin
  getdbrecord;
end;

procedure TfrmDino.FormCreate(Sender: TObject);
begin
  WindowState := wsMaximized;
  //getdbrecord;
end;

procedure TfrmDino.getdbrecord;
var
  sName: String;
  imgPath: String;
  img1: String;
  //img2: String;
begin
  sName := edtSearch.Text;
  FDQuery1.SQL.Text := 'SELECT record,name,meaning,pronounce,period,maingroup,size,lived,diet,factfile FROM dino Where name = :PName';
  FDQuery1.ParamByName('PName').AsString := sName;
  FDQuery1.Open;
  if FDQuery1.Bof and FDQuery1.Eof then
  begin
    ShowMessage('Name not found');
    Exit;
  end;
  //place data into edtName
  edtRecno.Text := FDQuery1.FieldByName('record').AsString;
  edtName.Text := FDQuery1.FieldByName('name').AsString;
  edtMeaning.Text := FDQuery1.FieldByName('meaning').AsString;
  edtPronounciation.Text := FDQuery1.FieldByName('pronounce').AsString;
  edtPeriod.Text := FDQuery1.FieldByName('period').AsString;
  edtMainGroup.Text := FDQuery1.FieldByName('maingroup').AsString;
  edtSize.Text := FDQuery1.FieldByName('size').AsString;
  edtLived.Text := FDQuery1.FieldByName('lived').AsString;
  edtDiet.Text := FDQuery1.FieldByName('diet').AsString;
  memFactfile.Text := FDQuery1.FieldByName('factfile').AsString;
  //displayimages
  imgPath := 'D:\Delphi_11_Community\MyProjects\Dinobase\dino_images\';
  img1 := sName + '.jpg';
  //img2 := sName + '_size.jpg';
  dinoImage.Picture.LoadFromFile(imgPath + img1);
  //edtSearch.Clear;
end;

 

Edited December 16, 2023 by Remy Lebeau

[Columbo 1]

Ok, It seems to be working now however I do have another question about the images.  I'm not sure as to how to word this is a manner that is clear but,... as i mentioned earlier, I have the TImage in a TabbedSheet.  I load the dino image into Tab1 (Dinosaur), and the second image into Tab2 (Size Comparison).  If I search a record, Tab1 is the selected Tab and the dino is displayed.  If I click on Tab2 the size comparison is diaplayed,  which is how it should be however, if I then search a new record, Tab2, the size Comparison is displayed first since that was the Tab being displayed just before searching a new record. The proper picture is displayed but Tab2 shows first  Is there a way that whenever you search a record Tab1 is always displayed first?

[Columbo 1]

Thanks Remy,  I was going to add checks for EOF and BOF as well as 'If found...' but I wanted to first be sure that the program was finding the DB, able to query the DB and load the images as well.  Once I was sure I could do that I was going to add the checks.  You have been extremely helpful and I appreciate your time and patience.

 

 

[Remy Lebeau 1375]

17 minutes ago, Columbo said:

Is there a way that whenever you search a record Tab1 is always displayed first?

Simply set the TPageControl's ActivePage property back to Tab1, eg:

procedure TfrmDino.getdbrecord;
var
  ...
begin
  ...
  PLTabSheet.ActivePage := TabSheet1;
end;

 

Edited December 16, 2023 by Remy Lebeau

[Columbo 1]

Thanks yet again Remy.  That is perfect!

And BTW you had asked:

Quote

Next problem... you are calling getdbrecord() in the Form's OnCreate event. What value does edtSearch hold when the Form is first created, BEFORE the user has a chance to type anything into it?  Is it blank?  Does it have a default value? 

The answer to that is that it is empty.  I am just trying to figure that one out now because when the program is first run it pops up a dialog box saying that sName is empty. I want to retrieve record 1 and populate the fields when the program is first run.  With all of the other stuff like loading the images and stuff I haven't had much time to think about it. 

Edited December 16, 2023 by Columbo

[haentschman 92]

Hi...

procedure TfrmDino.BitBtn1Click(Sender: TObject);
begin
  Application.Terminate;
end;

Terminate is the last option to end the program.
With Terminate NO more events are executed. Better to terminate is self.close or close

 

[Columbo 1]

Thank you haentshman,  I change that.

 

 

[Columbo 1]

I was able to figure out how to display the first record on start up and it works well.  My next problem is the DBNavigator.  I placed a DBNavigator on my form and I am using only the BOF, Previous, Next and EOF buttons.  My understanding is that when you place a DBNavigator on your form you set it's Datasource property to your Datasource.  In my case my Datasource is Datasource1 and, of course,  that is the only Datasource that is listed in the drop down menu for the Datasource property.  I did that and ran the program but when I press the next button nothing happens.  Actually, nothing happens when I press any of the buttons.  Does it need something else set?

I guess I could create my own nav buttons but using the DBNavigator seemed like the easiest way to do it.

[Stano 143]

No, no. Things happen in the back that you ask him to do. But you have to make sure you update the data you see. That's what I was warning you about. You'd see that when using DB components.
At least for learning purposes, put a DBGrid in there. You'll immediately see what's going on there.

[Remy Lebeau 1375]

16 hours ago, Columbo said:

The answer to that is that [edtSearch] is empty [in the OnCreate event].

Then there is no point in calling getdbrecord() in the OnCreate event, since it will always fail to find a matching record.

16 hours ago, Columbo said:

I am just trying to figure that one out now because when the program is first run it pops up a dialog box saying that sName is empty.

As it should be, since the user hasn't been allowed to type in a name yet!

16 hours ago, Columbo said:

I want to retrieve record 1 and populate the fields when the program is first run.

Then you need to perform a SELECT query without a WHERE clause.  And then after you have called FDQuery1.Open(), you can call FQuery1.First() before reading the field values.

type
  TfrmDino = class(TForm)
    ...
    private
     procedure displaydbrecord;
    public
     procedure get1stdbrecord;
     procedure searchdbrecord(const AName: string);
  end;

procedure TfrmDino.FormCreate(Sender: TObject);
begin
  WindowState := wsMaximized;
  get1stdbrecord;
end;

procedure TfrmDino.btnSearchClick(Sender: TObject);
begin
  searchdbrecord(edtSearch.Text);
end;

procedure TfrmDino.displaydbrecord;
var
  sName: String;
  img1: string;
  ...
begin
  sName := FDQuery1.FieldByName('name').AsString;

  edtRecno.Text := FDQuery1.FieldByName('record').AsString;
  edtName.Text := sName;
  ...
  img1 := sName + '.jpg';
  ...
end;

procedure TfrmDino.get1stdbrecord;
begin
  FDQuery1.SQL.Text := 'SELECT record,name,meaning,pronounce,period,maingroup,size,lived,diet,factfile FROM dino';
  FDQuery1.Open;
  FDQuery1.First;
  displaydbrecord;
end;

procedure TfrmDino.searchdbrecord(const AName: string);
begin
  FDQuery1.SQL.Text := 'SELECT record,name,meaning,pronounce,period,maingroup,size,lived,diet,factfile FROM dino Where name = :PName';
  FDQuery1.ParamByName('PName').AsString := AName;
  FDQuery1.Open;
  displaydbrecord;
  //edtSearch.Clear;
end;

 

2 hours ago, Columbo said:

My next problem is the DBNavigator.

Did you read the documentation yet?

https://docwiki.embarcadero.com/RADStudio/en/Navigating_and_Manipulating_Records

 

Even if you hook up the TDBNavigator to your DataSource, you don't have your UI hooked up to the Navigator, so of course nothing happens when you click the buttons.  You would have to either use the TDBNavigator's events to re-query your records every time a button is clicked, or you should change your UI to use TDBEdit components and let them update themselfes automatically when the DataSource is navigated.

Edited December 16, 2023 by Remy Lebeau

[Columbo 1]

Thanks Remy,  Sorry for the late response, I was out of town for the weekend.  Prior to leaving for the weekend I had figured out how to have the first record displayed when the program is started.  I was also able to set up my search box so that it didn't matter if the user entered a name in upper case or in lower case or a combination of both it will still find the record.  I need to look for some info on a "LIKE' statement or something similar.  Many people don't know how to spell the names of some of the dinosaurs in the database so if they do misspell a name it should  get the record with the name closest to that entered into the search box. 

 

Stano also pointed out that I should be using DBEdits instead of TEdits.  I'll try doing that tomorrow now that I am back home.

 

Everyone on this forum have been very helpful and I do appreciate the help and the patience shown while I try to get a grip on Delphi.  My thanks to all.

 

 

Edited December 18, 2023 by Columbo

[Columbo 1]

I changed all of the TDEdits to TDBEdits, set the DataSource and DataField and the DBNavigator is working now as it should however, that has created another problem.  As mentioned in one of my previous posts, the variable sName holds the name of the dino to search for in the database and I have images in 2 folders called dino_images and dino_size. The images have the same name as the name in the variable sName.  The image is loaded using LoadFromFile with the path to the image folder and whatever name is in sName.  The problem now is,  if I type a name into the search box, the record and the images are displayed as they should but,  if I use the DBNavigator to move to the next or previous record, the all of the TDBEdits are updated but the images are not. That is because the DBNavigator is not using my procedure to get the record along with the images.  I'm not sure how or where to edit the DBNavigator code to fix this.

 

I have a TDBEdit that displays the record number however,  I just noticed that if I type a name into the search box the record is found and displayed.  If I then press next or previous in the DBNavigator, the record number is incorrect.  When the program first starts it pulls and displays record 1.  If I then type a name into the search box it gets that record and displays the proper record number, example record 9. If I then press next in the TDBNavigator it displays the data for record 2.  The same reason that the images do not change,... not using my procedure.

 

Edited December 18, 2023 by Columbo

[Gary 18]

Use the DataSource DataChange event, (Object Inspector Event tab) put your image loading procedure there. It will be called every time the record changes. Not sure how you get the record number into the edit as it's not a field? You can add code to update a label  in the DataChange event also, Label.Caption :='Record Number: " + Query.RecNo.ToString.

 

If you want to get fancy with the names you can try iterating through the records and filling a comboBox with the names. Then use it's on change event to trigger your lookup. Your users will just select from a list (Have fun researching that 🙂 ).