Print Page - [SOLVED- Thank you] DBListbox

Programming => Databases => Topic started by: Derek2020 on March 26, 2020, 02:34:42 pm

Title: [SOLVED- Thank you] DBListbox
Post by: Derek2020 on March 26, 2020, 02:34:42 pm

Hello

I'm afraid I'm quite new to programming but I've learned the basics from online tutorials plus some existing programmers I know. However, I am trying now to modify these tutorials, more or less reverse engineering them and I've hit my first bug and I can't see why.

I've got data appearing in TDBGrid, no problem. But I'm trying to test things like the list and combo box. However, I can select the datasource ("testdata1.DB") in the object inspector but when I try to select Datafield it tells me "unable to retrieve fields definition from dataset." I have the opening of the DB set to clicking a button, but no matter what I try it keeps populating the original grid.

I'm sorry I'm probably not asking the question very well. If anyone could point to an online tutorial that might help, I would be most appreciative.

Thanks
Derek

Title: Re: DBListbox
Post by: Handoko on March 26, 2020, 03:34:57 pm

Hello Derek2020,
Welcome to the forum.

When asking issue related with the code, it usually better if you can provide the source code. So we can easily and quickly help you to solve the bug. If you are not willing to publicize the code, you can write an example that can show the problem.

Create a new folder, copy and paste all the necessary files except: the binary (exe file), *.bak, lib and backup folders. Compress the folder and send the zip file here.

Title: Re: DBListbox
Post by: JanRoza on March 26, 2020, 03:45:15 pm

Mostly if you get error messages in the Object Inspector when you try to select fields for a DBGrid it is because the database is not opened yet or the database connection and/or query are not set to active yet.
But without any coding or screenshot it remains a guessing game.

Title: Re: DBListbox
Post by: devEric69 on March 26, 2020, 04:36:38 pm

Hello @Derek2020,

Quote

"unable to retrieve fields definition from dataset."

Globally, the TDBList (and the TDBCombobox) must be plugged on a TDatasource, TDatasource which must be plugged on a TDataSet (TTable\TQuery, ...), TDataSet which contains TDatatFields.
Once the TDatasource property is plugged, you can\have to choose in the TDBList's Datafield property (or the TDBCombobox(s property), the proposed datafield-name (from the plugged TDataSet) that you want to *update*.

nb1: you have then tell your TDBList (or the TDBCombobox) which fieldname must be updated. That's good. But now, with which data can you update? Well, with the data entered in their Items property. Two ways:
a) you enter them manually, via their Items property editor.
b) you code at run-time the loading of their Items properties. For examples, specious values and\or values of another TQuery "Open" result. This nb1 leads to a nb2.
nb2: all database-aware components that have "lookup" in the name of their Class (TDBLookupCombobox, etc), have two other properties that point fistly to a lookup TDatset (let's say a TQuery named qryFoo), and secondly to one of the qryFoo's lookup-data-fields: that automates the loading of data in the Items property.

Regards

Title: Re: DBListbox
Post by: Derek2020 on March 26, 2020, 05:26:09 pm

Code: [Select]

Hello and thank you for your responses.

The code isn't very exciting I'm afraid

[nobbc][code]
unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqlite3conn, sqldb, db, FileUtil, Forms, Controls,
  Graphics, Dialogs, DbCtrls, DBGrids, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    dsMembers: TDataSource;
    Edit1: TEdit;
    gridMembers: TDBGrid;
    DBNavigator1: TDBNavigator;
    SQLite3Connection1: TSQLite3Connection;
    queryMembers: TSQLQuery;
    SQLTransaction1: TSQLTransaction;

procedure Button1Click(Sender: TObject);

  private

  public

  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }



procedure TForm1.Button1Click(Sender: TObject);
begin
    SQLite3Connection1.Open;
  {Comment out
  querymembers.SQL.text:= 'SELECT * from Members where Lastname = :Lastname';
  querymembers.Params.ParamByName ('Lastname').AsString := Edit1.text;
  Comment out end    }
  SQLTransaction1.Active := True;
  queryMembers.Open;
end;

End.
 [/nobbc]

The above is a modified code from festra.com with the data being loaded when clicking a button rather than opening the form. But it proves the link to the database works.

I have the TDBlistbox set with a data source to the TDataSource, and as that same TDataSource is what "powered" the TDBGrid, I know it must work from that point backwards.

I'm guessing it is not too far from being right and I was surprised this didn't work. As I'm sure you will appreciate, however, that knowing how to articulate the problem is just as hard as the problem being solved.

Many thanks for your kind replies.

Derek

Title: Re: DBListbox
Post by: madref on March 26, 2020, 05:54:19 pm

Hi Derek,

Next time use the right syntax [_code=pascal_] your code [_/code_] (but without the underscores).

Code: Pascal [Select][+]

unit Unit1;
 
{$mode objfpc}{$H+}
 
interface
 
uses
  Classes, SysUtils, sqlite3conn, sqldb, db, FileUtil, Forms, Controls,
  Graphics, Dialogs, DbCtrls, DBGrids, StdCtrls;
 
type
 
  { TForm1 }
 
  TForm1 = class(TForm)
    Button1: TButton;
    dsMembers: TDataSource;
    Edit1: TEdit;
    gridMembers: TDBGrid;
    DBNavigator1: TDBNavigator;
    SQLite3Connection1: TSQLite3Connection;
    queryMembers: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
 
procedure Button1Click(Sender: TObject);
 
  private
 
  public
 
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.lfm}
 
{ TForm1 }
 
procedure TForm1.Button1Click(Sender: TObject);
begin
    SQLite3Connection1.Open;
  {Comment out
  querymembers.SQL.text:= 'SELECT * from Members where Lastname = :Lastname';
  querymembers.Params.ParamByName ('Lastname').AsString := Edit1.text;
  Comment out end    }
  SQLTransaction1.Active := True;
  queryMembers.Open;
end;
 
End.

Title: Re: DBListbox
Post by: Derek2020 on March 26, 2020, 06:02:36 pm

Hello Madref

I've just copied data from a tutorial site and aside from the alterations above, it is verbatim. I will work on the niceties of clean code, but I wouldn't know where to put the statement you just suggested.

Thanks
Derek

Title: Re: DBListbox
Post by: lucamar on March 26, 2020, 06:06:38 pm

Quote from: Derek2020 on March 26, 2020, 06:02:36 pm

[...] I wouldn't know where to put the statement you just suggested.

He means for the forum, about how to post code snippets. Read this wiki page: Forum (http://wiki.lazarus.freepascal.org/Forum) for more info.

BTW, MadRef, to post tags verbatim it's better to use the [nobbc] tag, as in:
[nobbc][code][/code][/nobbc],
which results in:
[code][/code]

Less confusing for newbies :)

Title: Re: DBListbox
Post by: Derek2020 on March 26, 2020, 06:35:06 pm

Hello Lucamar

Thanks for that. I dare say I'm not the first person to dive in without reading forum rules properly.

I hope you won't hold my inadvertent lack of manners against me.

I shall see if I can edit my post to add those tags.

Many thanks and again, my apologies
Derek

Title: Re: DBListbox
Post by: lucamar on March 26, 2020, 07:31:50 pm

Quote from: Derek2020 on March 26, 2020, 06:35:06 pm

Thanks for that. I dare say I'm not the first person to dive in without reading forum rules properly.

And will probably not be the last, I guess :)

Quote

I hope you won't hold my inadvertent lack of manners against me.

Nah, don't worry. All of us have been new sometime and it's quite common to dive into the forum and ask some question without getting to know first how the forum works; after all, you're more worried about your software. ;)

And having the forum related advice in the wiki doesn't help either :D

Title: Re: DBListbox
Post by: Derek2020 on March 27, 2020, 12:54:16 pm

EDIT
Somehow I've solved it. A mix of DevEric69's advice, together with a Delphi manual and randomly changing things and I've got this stage of what I wanted to do sorted.

Thanks
Derek

END EDIT

OK, so this is more strange than I thought.

Taking the info from replies here (DevEric69) and by looking closely at the example that I was trying to reverse engineer, I set up a project from scratch, but using the existing database. Now I can select my DataField.

However, no matter what I do I still can't populate it with any data, despite the TDBGrid being populated just as I expected. NB this applies whether I set the database to open when the form opens or on a click of a button.

I'm obviously missing a setting somewhere. Any help would be appreciated.
Thanks
Derek

Title: Re: [SOLVED- Thank you] DBListbox
Post by: wp on March 27, 2020, 02:19:01 pm

Derek2020, which tutorial are you talking of? Sometimes tutorial writers specify the path to their database in the obect inspector or as absolute path at runtime. Then, when you copy the code into your project you have these paths in your code which may not be valid for you.

This is my recommendation:
When you are a beginner you may want to do much work at designtime. You seem to be using an SQLite3 database. Specify the ABSOLUTE path to your sqlite file in the DatabaseName property of the SQLite3Connection. It is important to use the absolute path here because otherwise the IDE would consider the path to be relative to Lazarus.exe - and this is not where your data are. Set up your db-aware application etc.

When you are done delete the path to the database from the connection and add code to establish it at runtime in a general way. Maybe as relative path to the directory of your exe, or to a configurable folder. This avoids that your program searches for a folder which does not exist on the user's computer. For this purpose, you can add code to the OnCreate event of the form:

Code: Pascal [Select][+]

procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLite3Connection.DatabaseName := Application.Location + 'database/test.sqlite';
end;

Here it is assumed that a file test.sqlite resided in a subfolder "database" of your exe directory.

Another option would be to read the databasename from an ini file which the user must configure when the program is installed or runs for the first time:

Code: Pascal [Select][+]

uses
  inifiles;
 
procedure TForm1.FormCreate(Sender: TObject);
var
  ini: TIniFile;
  dbDir: String;
  fname: String;
begin
  ini := TIniFile.Create(GetAppConfigFile(false));
  try
    dbDir := ini.ReadString('Database', 'Directory', '');
    fname := ini.ReadString('Database', 'FileName', '');
    if (dbDir = '') or (fname = '') then  begin
      MessageDlg('Database not properly configured.', mtError, [mbOk], 0);
      exit;
    end;
    SQLite3Connection.DatabaseName := AppendPathDelim(dbdir) + fname;
  finally
    ini.Free;
  end;
end;

Title: Re: [SOLVED- Thank you] DBListbox
Post by: Derek2020 on March 27, 2020, 03:19:53 pm

Hello WP

Thanks for the message. The tutorials are at festra.com/fp/sqlite01.htm through to 05.htm

What was causing the problem is that the TDBGrid was finding the data, but when I put a combo or list box on the same form it couldn't find the data, yet the TBGrid carried on displaying it fine. I then started an entirely new project, copying what I thought I saw (plus advice here) and it works now.

If it was a case of not putting the absolute path in initially then surely it wouldn't work for the TDBGrid either. One of the problems for a beginner is diagnosing the problem in order to ask the experts what to do about it...

What you've written below in code is presumably the same thing that I've entered into Object Inspector; I know experts seem to prefer to write it in code whereas at the moment I'm finding it easier to automate as much as possible. It's addictive, this programming lark!

Many thanks
Derek

Title: Re: [SOLVED- Thank you] DBListbox
Post by: JanRoza on March 27, 2020, 03:36:06 pm

Quote

I know experts seem to prefer to write it in code whereas at the moment I'm finding it easier to automate as much as possible.

Keep in my that if you distribute your program to another user, he might install it in a different place than you did and then it is very handy if you've set your path to the data in code (using common places as users folder or the ProgramData folder on your C drive). Not every user has the same name as you do, so his folder name for the data may differ. ;)
Path defined in the Object Inspector are fixed for everyone and may not exist with the users of your program.
Just a practical point as tip.

Title: Re: [SOLVED- Thank you] DBListbox
Post by: devEric69 on March 27, 2020, 04:40:03 pm

Quote

Taking the info from replies here (DevEric69) and by looking closely at the example that I was trying to reverse engineer, I set up a project from scratch, but using the existing database. Now I can select my DataField.

However, no matter what I do I still can't populate it with any data, despite the TDBGrid being populated just as I expected. NB this applies whether I set the database to open when the form opens or on a click of a button.

Hello @Derek2020,

I don't know the tutorial you're talking about.

But, the important things to understand are:
1°) that the Datafield property (of the TDBList) indicates which field you want to UPDATE.
2°) to see values in the TDBList, to see the values to choose from (for the UPDATE of the current record), there are 2 ways:
a) the code must (!) load - at runtime - the TDBList's Items property (probably through a loop, that fetches values from a probably second opened dataset, ie TQuery\TTable), and injects them into the Items property;
b) or, the TDBList's Items property must (!) be filled - at design time - with manually entered values.

In other words, the TDBList does not fill itself with data like the TDBGrid.

Regards

ps: the TDBLookupXXX can do the same, with more automation (self-fulfilment) at design time.

Title: Re: [SOLVED- Thank you] DBListbox
Post by: Derek2020 on March 27, 2020, 04:53:44 pm

Thanks both.

DevEric69, the tutorials are festra.com/fp/sqlite01.htm through to 05.htm. I am experimenting now- adding bits, taking bits off to see what changes they make. Now I've got to this point, a lot of what I'm reading in the manuals (Delphi, but still close it seems) is making sense.

I know there are wikis for Lazarus, but I find a lot of that goes straight from the absolute basics to very complex, missing out the bit in the middle.

JanRoza, I understand the point you're making, thank you. I don't think I'm going to need to worry about distributing it just yet. Is it normal to leave the DB in the same project folder, or in a sub folder?

Again, my thanks for your kind help. I hope one day I can repay that with other new starters.

Derek

Title: Re: [SOLVED- Thank you] DBListbox
Post by: JanRoza on March 27, 2020, 06:47:47 pm

I mostly use a subfolder in the ProgramData folder as it is a reasonable constant location that is available on every Windows system but a good alternative would be a subfolder in the users folder.