TDBLookupComboBox and stuff
lazarus / 16-Databases
----------------------------------------------------------------------
guest48180 · 2012-11-28 08:11
Linux Mint 13
Lazarus 1.0.2
Firebird Super Server 2.5
I've read several posts before writing this, hoping to at least find
something I could use to weave together an answer before asking for
help.
I have a Firebird database. One of the Tables 'Customers', has its
first field 'Name', set to primary key. After the User name and
password are entered and the 'Connect' button pressed to connect to
the .fdb, the DBLookupComboBox loads the Name(s) from the Customer
Table.
procedure TForm1.ConnectExecute(Sender: TObject);
begin
try
if IBConnection1.Connected = False then
begin
SQLQuery1.Close;
IBConnection1.UserName:= Edit1.Text;
IBConnection1.Password:= Edit2.Text;
SQLQuery1.SQL.SetText:= ('select * from customers
where name = ' + DBLookupComboBox1.KeyField);
IBConnection1.Connected:= True;
SQLTransaction1.Active:= True;
SQLQuery1.Active:= True;
{ I realize I may not need both of the following
statements, but there is so little
documentation on working with databases to
reference to know for sure. }
DBLookupComboBox1.DataField:= 'Name';
DBLookupComboBox1.KeyField:= 'Name';
SQLQuery1.Open;
Button1.Visible:= False;
Button2.Visible:= True;
Edit1.ReadOnly:= True;
Edit2.Readonly:= True;
end;
except
on E: EDatabaseError do ...etc.
Now that loads DBLookupComboBox up with the customer names and my
DBGrid with all the customers and the info. But my problem is that:
1) I want the first name shown in the DBLookupComboBox to load ONLY
that customer in my DBGrid.
I have tried:
SQLQuery1.SQL.SetText:= ('select * from customers where name = ' +
DBLookupComboBox1.KeyField);
which raises an error...
SQLQuery1.SQL.Text:= 'select * from customers where name = ' +
DBLookupComboBox1.Text;
which also raises an error...
and I have no idea what to do here.
And then I have this code for when the DBLookupComboBox has a
different customer selection made:
procedure TForm1.DBLookupComboBox1Select(Sender: TObject);
begin
SQLQuery1.Close;
SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Text:= 'select * from customers where name = ' +
DBLookupComboBox1.Text;
SQLQuery1.Active:= True;
IBConnection1.Connected:= True;
SQLTransaction1.Active:= True;
SQLQuery1.ApplyUpdates;
SQLQuery1.Open;
end;
...and it throws a fit too. Maybe I just haven't found it, but if
anyone knows where I can go to study how to use Firebird with Lazarus,
some in-depth tutorials or something, then I'd appreciate a URL or
two.
I've studied the SQL Tutorials here, 1, 2, and 3, but they tend to
skim the surface and don't go into the deeper explaining. I'm the
simple mind that needs more than just a 'glance' to 'get it'. Right
now I'm so confused that I don't know what to do or where to do it.
I've always pride myself on being able to figure things out. But with
this I can't even get a sense of direction. And it's frustrating. Even
more so to have to come here and ask for help. But I do appreciate
anything that will get me on track with this.
----------------------------------------------------------------------
BigChimp · 2012-11-28 13:02
AFAIU, DBLookupCombobox is meant to get values from a lookup table and
store the foreign key into the main table.
You're using DBLookupCombobox to look up values in the same table,
which is probably the reason why it doesn't work.
(See an example for dblookupcombobox:
lazarus\examples/database/dblookup/, but it's not too clear IMO).
Perhaps a regular dbcombobox is needed instead? Looking at the help:
http://lazarus-ccr.sourceforge.net/docs/lcl/dbctrls/tdbcombobox.html:
no, that doesn't help: dbcombobox also wants to store its value in a
db table, which you don't want as you only want to display and filter
based on the user's selection.
(Perhaps there's a way to not bind the control to the database in
which case it could be used, have no idea about that)
Have a look here:
http://www.lazarus.freepascal.org/index.php/topic,18012.msg100952.html
#msg100952
... and adapting ludob's suggestion there to fill a regular combobox
with customer names seems to be the way to go. You can use the method
in sqldbtutorial3 to get the customer names and add them one by one to
the combobox.
Other db controls have links to the help pages:
http://wiki.lazarus.freepascal.org/Databases
----------------------------------------------------------------------
BigChimp · 2012-11-28 13:18
You could adapt the code in this post:
http://www.lazarus.freepascal.org/index.php/topic,9928.msg49038.html#m
sg49038
to add customer names to a combobox.
----------------------------------------------------------------------
guest48180 · 2012-11-29 02:44
I appreciate you, BigChimp. Although I haven't tried your suggestions
yet, through the links you provided I found some really in-depth SQLdb
info. I'm sure that with your suggestion and this other material, I'll
get to the end of the tunnel as many of the unasked questions have
been answered for me...all thanks to you One of the links:
http://wiki.lazarus.freepascal.org/Databases#Data_Controls actually
told me that one of the things I was trying to do (have a customer
table for customer names and another table for customer info) was spot
on with the DBLookupComboBox --> "DBLookupListBox control and
DBLookupComboBox control Allow the user to insert values into a
database field by displaying the contents of a lookup field in another
table. Though these controls store their results in a single field,
you need another field for the lookup values. " So I'm going back to
using the Tables set up the way I first had them. It makes more sense
to me doing it this way. It may be unorthodox, but I truly need a
customer table all to itself.
And another link I'll share with those who read this is:
http://translate.google.com/translate?hl=en&sl=auto&tl=en&u=http%3A%2F
%2Fwiki.lazarus.freepascal.org%2FSqlDBHowto%2Fnl
That's Google's English translation of a Dutch-written tutorial that
goes into quite a bit of depth, taking the reader along slowly and
methodically. Good stuff, that.
Thanks again, BigChimp.
----------------------------------------------------------------------
BigChimp · 2012-11-29 10:31
Quote from: Landslyde on November 29, 2012, 03:44:13 amI appreciate
you, BigChimp.
Thanks
Based on a recent post: you may also try using your existing
DBLookupCombobox without binding it:
http://lazarus.freepascal.org/index.php/topic,18941.msg108052.html#msg
108052
... as that may be quicker than filling comboboxes with data.
(I'm hoping other people step in because I'm really not that
experienced in the (Laz or any) GUI department)
Quote from: Landslyde on November 29, 2012, 03:44:13 am One of the
links: http://wiki.lazarus.freepascal.org/Databases#Data_Controls
actually told me that one of the things I was trying to do (have a
customer table for customer names and another table for customer info)
was spot on with the DBLookupComboBox -->
...
So I'm going back to using the Tables set up the way I first had them.
It makes more sense to me doing it this way. It may be unorthodox, but
I truly need a customer table all to itself.
I don't know what your tables contain exactly and how they are laid
out, so I can only guess. Having a 1:1 relation between Customers and
CustomerInfo tables is weird (can be done if you need to tweak
physical database performance etc but at a logical level it's weird).
I suspect you have multiple "customer info" details in your
CustomerInfo table, e.g. orders or something.
Splitting that out into another table would make more sense.
If you're willing to post your database/table structure (e.g.
FlameRobin: select database, properties, DDL) in another post, I'd be
happy to have a look and give some advice. If you're not, no problem,
but be aware that db design decisions may make your code much more
complicated than it needs to be.
Quote from: Landslyde on November 29, 2012, 03:44:13 amAnd another
link I'll share with those who read this is:
http://translate.google.com/translate?hl=en&sl=auto&tl=en&u=http%3A%2F
%2Fwiki.lazarus.freepascal.org%2FSqlDBHowto%2Fnl
Have you read the English version of that wiki article:
http://wiki.lazarus.freepascal.org/SqlDBHowto? If there's anything
missing in the English version, please feel free to add it.
As I've said before, there's quite a lot of wiki documentation, see
e.g. http://wiki.lazarus.freepascal.org/Category:Databases but we can
definitely use more!
Good luck,
BigChimp