Lazarus

Programming => Databases => Topic started by: AKCarlow on August 18, 2014, 02:36:59 pm

Title: DBLookupComboBox not working?
Post by: AKCarlow on August 18, 2014, 02:36:59 pm
The form has two queries and data sets, both based on the same MySQL table. Data from one query is displayed in a DBGrid, and from the other (as ListSource and ListField) in a DBLookupComboBox. The ComboBox's DataSource is set to the data source of the grid, and the DataField and KeyField are set to the same field (ShareNo). ScrollListData is set to true.

What I expected to happen was that selecting an entry in the combo box would scroll the grid to the corresponding row. What actually happens is that the key field value corresponding to the selection in the combo box is put into the ShareNo column of the current row in the grid, overwriting the value that is there.

In the screen shot, Brzezinski has been selected, but instead of scrolling to his row (341), his ShareNo has overwritten Conway's (1).

Am I misunderstanding how it should work, not doing it properly, or is it broken?




Title: Re: DBLookupComboBox not working?
Post by: rvk on August 18, 2014, 03:43:13 pm
What I expected to happen was that selecting an entry in the combo box would scroll the grid to the corresponding row.
Why are you then working with two queries and datasets?

Because your working with ListSource and ListField (in TDBLookupComboBox), you're providing the DataSource (of TDBLookupComboBox) with a new value from the ListSource (when in edit-mode).

That's not what you wanted (as you described). If you want the TDBLookupComboBox to "follow" the DBGrid you can just assign it the ListSource the same DataSource as your DBGrid and leave DataSource of your TDBLookupComboBox empty. That way the scrolling from TDBLookupComboBox and DBGrid should be "in sync".

You can remove the second query and datasource completely.

Title: Re: DBLookupComboBox not working?
Post by: AKCarlow on August 18, 2014, 06:19:18 pm
Thank you. I want the DBGrid to follow the Combo Box. Using just one query and ds, as you suggest, achieves this. However, the reason for trying with two queries/ds was that I want the combo box to show its contents (surnames) alphabetically, which it did with the separate queries.
Title: Re: DBLookupComboBox not working?
Post by: rvk on August 18, 2014, 06:47:57 pm
Just off the top of my head (not at my pc right now, typing mobile) doesn't TDBLookupComboBox have a property Sorted for this? Just set it to true and you're good to go.

Edit: Yep... there is a Sorted property for this.

B.T.W. Just as a thought about your screenshot...
(It's off-topic for your question but i thought i'd share it anyway)

You could also use a simple TEdit and Filter the DBGrid on all the surnames containing that string. That way you wouldn't have to scroll to just one name but could have all the names together which contain your text.
Code: [Select]
procedure TForm1.Edit1Change(Sender: TObject);
begin
  SQLQuery1.Filtered := false;
  if TEdit(Sender).Text <> '' then
  begin
    SQLQuery1.Filter :=
      'UPPER(SURNAME)=' + QuotedStr('*'+uppercase(TEdit(Sender).Text) + '*');
    SQLQuery1.Filtered := true;
  end;
end;
or even check first name and surname together:
Code: [Select]
procedure TForm1.Edit1Change(Sender: TObject);
begin
  SQLQuery1.Filtered := false;
  if TEdit(Sender).Text <> '' then
  begin
    SQLQuery1.Filter :=
    'UPPER(relatienaam)=' + QuotedStr('*'+uppercase(TEdit(Sender).Text) + '*') +
    ' OR ' +
    'UPPER(betreft)=' + QuotedStr('*'+uppercase(TEdit(Sender).Text) + '*');
    SQLQuery1.Filtered := true;
  end;
end;

(just a thought... :))
Title: Re: DBLookupComboBox not working?
Post by: AKCarlow on August 18, 2014, 07:38:03 pm
It has a property sorted, but it doesn't sort - it tells the control that the list field is already sorted. "When True, new entries are added in sort order, not to the end of the list."
Title: Re: DBLookupComboBox not working?
Post by: rvk on August 18, 2014, 07:43:57 pm
It has a property sorted, but it doesn't sort - it tells the control that the list field is already sorted. "When True, new entries are added in sort order, not to the end of the list."
That's strange. For me it is sorted while the query itself has a different order.

Did you try this already for yourself?

I just tested this and "DBLookupComboBox1.Sorted := true;" (in a Button) does sort the combobox even when it was initially not sorted. Also setting it in the Object Inspector it just works correctly. (Lazarus 1.2.4)

(b.t.w. i types another suggestion about a tedit in my previous post)
Title: Re: DBLookupComboBox not working?
Post by: AKCarlow on August 19, 2014, 12:07:38 am
Hmm. I was setting Sorted in the Object Inspector, without success. The form in question is initially hidden, and is made visible by a button on the main form. To the OnClick for that button I added
Code: [Select]
  fShareReg.qShares.Close;
  fShareReg.SurnameSelector.Sorted :=true;
  fShareReg.qShares.Open;
and it is sorted - but with only one occurrence of each surname   >:(

I am working towards getting something similar to what you were suggesting with your TEdit and filtering. If I can work out how to install it, I believe the RXNew package has a similar component but allows the display of multiple fields, similar to the MSAccess component.
Title: Re: DBLookupComboBox not working?
Post by: rvk on August 19, 2014, 12:30:40 am
and it is sorted - but with only one occurrence of each surname   >:(
Yeah, that would be the downside of Sorted. Duplicates are removed. Even if you would have an AllowDuplicates setting it wouldn't make much sense because you wouldn't know which one you're choosing.

I am working towards getting something similar to what you were suggesting with your TEdit and filtering. If I can work out how to install it
You don't need to "install it". Just make an Edit1 (of TEdit) and create an event for OnChange and paste the code in. The second example (with OR) has the benefit that you can just type part of a first name or last name and the DBGrid will only show you results with those names.

I believe the RXNew package has a similar component but allows the display of multiple fields, similar to the MSAccess component.
Yes. According to this (http://wiki.lazarus.freepascal.org/dblookupcombobox) (at the bottom) you can set multiple fields for ListField like "Name;Surname" with the RxDBLookupCombobox. B.T.W. you could accomplish this yourself by adding a calculated field in your select statement (like "name || ' ' || surname as Fullname") and use Fullname to display in the combobox.

Downside (of using RxDBLookupCombobox) would be that you can't begin typing the name to get to a name fast. And choosing a name like that in a combobox with hundreds of entries.... seems difficult. That's why i suggested the TEdit (free typing) where the user could just type "Car" and all Carlow, Carlos, Carxxx in first and surname become visible (which would be much more intuitive).
Title: Re: DBLookupComboBox not working?
Post by: taazz on August 19, 2014, 01:14:48 am
please post a small sample for us to see the problem, TDBLookupcombobox was created to allow you to select any record from a master table to be linked on a detail table.

If you want to show the row's listfield then try to use a lookup field in the dataset instead (it requires persistent fields).

with out a sample application it would be impossible for us to see the problem.
Title: Re: DBLookupComboBox not working?
Post by: AKCarlow on August 19, 2014, 06:07:55 pm
It is now doing what I need, thanks. I can quickly pick a desired record by name from the several hundred.

I'm just using one query for both the grid and the lookup. As previously mentioned, by changing the lookup's Sorted to true when the form is made visible the surnames are in the desired order. As I wanted to show the first name in the lookup anyway, I'm making a calculated field. This almost got around the problem of duplicates not showing when sorted, but there were still a few, so I've also included the unique share number in the calculated field.

Using || or + to concatenate the fields just produced 0, but concat() achieves the desired result.

I didn't install RXNew.

Thanks again, rvk & taazz.
TinyPortal © 2005-2018