Recent

Author Topic: Find record from DBCombobox selection  (Read 351 times)

Bert_Plitt

  • New Member
  • *
  • Posts: 31
Find record from DBCombobox selection
« on: November 11, 2019, 03:12:18 am »
Please, I need help.  I have a SQLite3 database with one table, named TABLE1.  TABLE1 has 5 fields of which the first is named NAME and is designated as the primary key.  I also have a form on which are the following components: SQLite3Connection1, SQLTransaction1, DataSource1, SQLQuery1, 4 TDBEdit components, 1 TDBLookupComboBox, and a TDBNavigator.  The 4 TDBEdit components are each connected to one of the non-key data fields of TABLE1 and TDBComboBox is connected to the NAME field as both the data field and list field. 

With SQLQuery and SQLTransaction both set to Active=true via the ObjectInspector, when I run the project, I can step through the table using the navigation keys in the TDBNavigator.  I think this indicates that most things are setup correctly.

Can anyone provide some code example that will allow me to select the NAME record by selecting the NAME from the drop down list of TDBLookupComboBox?

The following OnSelect handler of TDBLookupComboBox does not work:

  var
    s: String;
  begin
    s:='SELECT * from TABLE1 where TABLE1.NAME = :' + DBLookupComboBox1.Text;
    SQLite3Connection1.ExecuteDirect(s);
  end;

Any suggestions greatly appreciated.
Windows 10, Lazarus 2.0.6, FPC 3.0.4

valdir.marcos

  • Hero Member
  • *****
  • Posts: 849
Re: Find record from DBCombobox selection
« Reply #1 on: November 11, 2019, 03:24:54 am »
Please, I need help.  I have a SQLite3 database with one table, named TABLE1.  TABLE1 has 5 fields of which the first is named NAME and is designated as the primary key.  I also have a form on which are the following components: SQLite3Connection1, SQLTransaction1, DataSource1, SQLQuery1, 4 TDBEdit components, 1 TDBLookupComboBox, and a TDBNavigator.  The 4 TDBEdit components are each connected to one of the non-key data fields of TABLE1 and TDBComboBox is connected to the NAME field as both the data field and list field. 

With SQLQuery and SQLTransaction both set to Active=true via the ObjectInspector, when I run the project, I can step through the table using the navigation keys in the TDBNavigator.  I think this indicates that most things are setup correctly.

Can anyone provide some code example that will allow me to select the NAME record by selecting the NAME from the drop down list of TDBLookupComboBox?

The following OnSelect handler of TDBLookupComboBox does not work:

  var
    s: String;
  begin
    s:='SELECT * from TABLE1 where TABLE1.NAME = :' + DBLookupComboBox1.Text;
    SQLite3Connection1.ExecuteDirect(s);
  end;

Any suggestions greatly appreciated.
Code: Pascal  [Select]
  1.   var
  2.     SelectText, TableName: String;
  3.   begin
  4.     SelectText:='SELECT * from ' + TableName + ' where  ' + TableName + ' .NAME = ' + DBLookupComboBox1.Text;
  5.     SQLQuery.Close;
  6.     SQLQuery.Text := SelectText;
  7.     SQLQuery.Open;
  8.     SQLQuery.ShowMessage('Name: ' + SQLQuery.FieldByName('Name').ASString);
  9.     SQLQuery.Close;
  10.   end;
Beyond that, please, attach a small sample project.
« Last Edit: November 11, 2019, 04:32:46 am by valdir.marcos »

Bert_Plitt

  • New Member
  • *
  • Posts: 31
Re: Find record from DBCombobox selection
« Reply #2 on: November 11, 2019, 05:53:37 am »
valdir.marcos -- I tried two versions of your code.  Neither works.  The first gives a runtime error and the second does not find any record.  I've attached the two test versions.  Any other thoughts?
Windows 10, Lazarus 2.0.6, FPC 3.0.4

bytebites

  • Full Member
  • ***
  • Posts: 220
Re: Find record from DBCombobox selection
« Reply #3 on: November 11, 2019, 08:49:49 am »
lpr-file is missing from the attachments.

Zvoni

  • Sr. Member
  • ****
  • Posts: 299
Re: Find record from DBCombobox selection
« Reply #4 on: November 11, 2019, 11:25:34 am »
*snipp*

The following OnSelect handler of TDBLookupComboBox does not work:

  var
    s: String;
  begin
    s:='SELECT * from TABLE1 where TABLE1.NAME = :' + DBLookupComboBox1.Text;
    SQLite3Connection1.ExecuteDirect(s);
  end;

Any suggestions greatly appreciated.
I'm trying to wrap my mind around the fact that you:
a) build a SQL-String to include a Parameter (anyone noticed the colon? ':'
b) In that Statement you ignore needed single quotes identifiying a String
C) That you ExecuteDirect that Statement on your Connection instead of your TSQLQuery.

As i said: i'm trying (and failing) to wrap my mind around those facts....
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

valdir.marcos

  • Hero Member
  • *****
  • Posts: 849
Re: Find record from DBCombobox selection
« Reply #5 on: November 11, 2019, 12:27:43 pm »
valdir.marcos -- I tried two versions of your code.  Neither works.  The first gives a runtime error and the second does not find any record.  I've attached the two test versions.  Any other thoughts?
I have managed to open and compile without success both of your samples.
I could not open your LookupTest.db3 as a SQLite file.

My suggestion is that your learn to walk before run:
https://wiki.freepascal.org/Portal:Databases
https://wiki.lazarus.freepascal.org/SQLite
https://www.sqlite.org/index.html

1. That said, give us a compilable small sample project where you open and show information about two tables (master and detail).
2. Then, request information about TDBLookupComboBox.

Zvoni

  • Sr. Member
  • ****
  • Posts: 299
Re: Find record from DBCombobox selection
« Reply #6 on: November 11, 2019, 12:48:07 pm »
Untested Aircode!
Code: Pascal  [Select]
  1.  var
  2.     s: String;
  3.   begin
  4.     s:='SELECT * from TABLE1 where TABLE1.NAME=:ParamName;';
  5.     SQLQuery1.Close;
  6.     SQLQuery1.SQL.text:=s;
  7.     SQLQuery1.ParamByName('ParamName').AsString:=DBLookupComboBox1.Text;
  8.     SQLQuery1.Open;
  9.     //SQLite3Connection1.ExecuteDirect(s);
  10.    //No Idea if you have to refresh your GUI-Controls to show the new Data
  11.   end;
  12.  
  13.  
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts