Find record from DBCombobox selection
lazarus / 16-Databases

----------------------------------------------------------------------
Bert_Plitt · 2019-11-11 02:12

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.

----------------------------------------------------------------------
valdir.marcos · 2019-11-11 02:24

Quote from: Bert_Plitt on November 11, 2019, 03:12:18 amPlease, 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   var   
SelectText, TableName: String;  begin    SelectText:='SELECT * from '
+ TableName + ' where  ' + TableName + ' .NAME = ' +
DBLookupComboBox1.Text;    SQLQuery.Close;    SQLQuery.Text :=
SelectText;    SQLQuery.Open;    SQLQuery.ShowMessage('Name: ' +
SQLQuery.FieldByName('Name').ASString);    SQLQuery.Close;  end;Beyond
that, please, attach a small sample project.

----------------------------------------------------------------------
Bert_Plitt · 2019-11-11 04:53

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?

----------------------------------------------------------------------
bytebites · 2019-11-11 07:49

lpr-file is missing from the attachments.

----------------------------------------------------------------------
Zvoni · 2019-11-11 10:25

Quote from: Bert_Plitt on November 11, 2019, 03:12:18 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....

----------------------------------------------------------------------
valdir.marcos · 2019-11-11 11:27

Quote from: Bert_Plitt on November 11, 2019, 05:53:37 amvaldir.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 · 2019-11-11 11:48

Untested Aircode!
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  var    s:
String;  begin    s:='SELECT * from TABLE1 where
TABLE1.NAME=:ParamName;';    SQLQuery1.Close;   
SQLQuery1.SQL.text:=s;   
SQLQuery1.ParamByName('ParamName').AsString:=DBLookupComboBox1.Text; 
 SQLQuery1.Open;    //SQLite3Connection1.ExecuteDirect(s);   //No Idea
if you have to refresh your GUI-Controls to show the new Data  end;