Find record from DBCombobox selection

[Bert_Plitt]

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]

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.

[Bert_Plitt]

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]

lpr-file is missing from the attachments.

[Zvoni]

*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]

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]

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.