List search results in a combobox.

Question

I want to change our search as it is a bit hacky now. Currently it works like this:

1. User enter a text in a textbox, for example Volvo and start searching.
2. This search with SQL all posts containing Volvo.
3. The result is a list with 2 columns, BoldID and DisplayValue.
4. BoldID is just an unique number to identify the object in database.
5. DisplayValue is what the user see in the list of results.
6. Each row in the resultset is identified with a row number in users list. The first with 1, the second with 2 and so on.
7. There is also a limitation to only show max 99 hits by intention by looping the query in code. It is not good to have a query that returns many hits as it no meaning to have more in the users hit list. The search should be more specific in that case.

The result list is displayed in a separate global window called rightpane. The hacky part is that if searching from a modal dialog then rightpane must be undocked from the main window to be able to pick a value with the mouse. We suspect this could be a reason that the application sometimes lock itself with the modal dialog behind the main window.

After change it should work like this:

The user enter text in a Combobox and start searching with SQL on demand like before. But the resultset is displayed in the combobox. So I only want to change the display if possible as there are a lot of old SQL queries that is in use and works fine now.

We use Delphi 2007, Interbase 2009 and components from DevExpress. Yesterday I tried to connect a customdatasource to a combobox, but I realize this was not implemented yet. See How to use a TcxCustomDataSource in a TcxExtLookupComboBox. I want to use a custom datasource to implement row number and max number of hits. Se 6 and 7 in the list above.

So now I see 2 options:

• Use a DB-aware combobox
• Use a textbox and attach a grid to it to display the resultset.

The combobox is simpler because it is a whole component, but I don't know how to implement feature 6 and 7 from the list above. The textbox + grid give more freedom but require more work.

I prefer the first option. The current problems is:

• I think Interbase SQL cannot restrict the amount of hits in the resultset. It must be done with code. How is this done with a DB-aware combobox ?
• How can I display row numbers in the resultset ?

Update 1: Requirement 7 is solved thanks to Marjan, thanks. As for 6 I think it is harder. Ideally I want it in SQL so I could extract an own column like Adding row no. But Interbase don't support that.

Answer 1

Interbase has a ROWS clause, apparently used for what you want.

Answer 2

Requirement 6:

Assuming that the db's id value is an integer, you can use the tag of a control or the Object of the strings in a combobox/listbox to store the id value.

[pseudo code]

while not eof do
  ComboBox.Items.AddObject(<DisplayText From Db>, TObject(<IntegerID from Db>));

and use it like so

id := Integer(ComboBox.Items.Objects[ComboBox.ItemIndex]);

Requirement 7:

Using a db aware combobox/listbox looping through the query til eof or 99 rows is out. So you would need to rely on SQL. Even if you do not want to change any of the current SQL statements, you could use SQL's "TOP" feature to limit the number of rows. For example

SELECT TOP 99 FROM (<your original SQL>)

In Interbase SQL you can use the phrase:

ROWS 1 TO 99 

just after the order clause (see example at http://blogs.teamb.com/craigstuntz/dtpostname/ibsqlintro/ - almost the bottom of the page)