Nitobi

The Combobox component boasts six different search modes. Each have different requirements for the way data is being selected from the master dataset.

Querystring Parameters

The Combo calls the datasource url (or getHandler) with several parameters in the querystring.

eg:

get.asp?StartingRecordIndex=12&PageSize=12 &SearchSubstring=a&ComboId=myCombo&LastString=Arlene

• StartingRecordIndex - When using paging, this is the ordinal record number to begin at. Ie: if Combo is requesting records 10 to 20 then StartingRecordIndex will be 10.
• PageSize - The number of records to return.
• SearchSubstring - The value entered by the user to search against (warning: will be empty if there is no typed value).
• ComboId - The ID of the combobox making the request.
• LastString - The value of the last string in the combo"s dataset. This is only used for databases without proper paging functionality (like MS Access).

The following querystrings are templates, or guidelines for constructing your own queries. Note that strings with highlighting are the querystring variables mentioned above.

Portions that are underlined indicate where the above mentioned querystring variables are used.

Classic Mode

In classic mode we make use of basic autocompletion as well as paging. The experienced developer will know that different databases have somewhat different methods of doing paging.

Microsoft Access

Notes: MS Access doesn"t support paging, but we have provided a workaround. Here is an example querystring.

SELECT TOP PageSize * FROM tblCustomers WHERE ContactName > "LastString" AND ContactName LIKE "SearchSubstring%" ORDER BY ContactName

SELECT *
FROM (SELECT TOP PageSize *
FROM (SELECT TOP PageSize+StartingRecordIndex *
FROM tblCustomers  WHERE ContactName LIKE "SearchSubstring%"
ORDER BY ContactName ASC) DERIVEDTBL
ORDER BY ContactName DESC) DERIVEDTBL2
ORDER BY ContactName

MySQL

SELECT ContactName FROM tblCustomers WHERE ContactName LIKE "SearchSubstring%" ORDER BY ContactName LIMIT StartingRecordIndex, PageSize

SQL Server

SELECT TOP PageSize ContactName FROM tblCustomers WHERE ContactName > "LastString" AND ContactName LIKE "SearchSubstring%" ORDER BY ContactName

or

SELECT *
FROM (SELECT TOP PageSize *
FROM (SELECT TOP PageSize+StartingRecordIndex *
FROM tblCustomers  WHERE ContactName LIKE "SearchSubstring%"
ORDER BY ContactName ASC) DERIVEDTBL
ORDER BY ContactName DESC) DERIVEDTBL2
ORDER BY ContactName

+++Oracle

SELECT ContactName FROM (SELECT ContactName FROM tblCustomers WHERE ContactName > "LastString" AND ContactName LIKE "SearchSubstring%" ORDER BY ContactName) WHERE ROWNUM <= PageSize

DB2

SELECT ContactName FROM tblCustomers WHERE ContactName > "LastString" AND ContactName LIKE "SearchSubstring%" ORDER BY ContactName FETCH FIRST PageSize ROWS ONLY

Filter Mode / Compact Modes

SQL for these modes is much simpler because paging isn"t required here.

Microsoft Access

SELECT TOP PageSize * FROM tblCustomers WHERE ContactName LIKE "SearchSubstring%"

MySQL

SELECT * FROM tblCustomers WHERE ContactName LIKE "SearchSubstring%" LIMIT PageSize

SQL Server

SELECT TOP PageSize * FROM tblCustomers WHERE ContactName LIKE "SearchSubstring%"

Oracle

SELECT * FROM tblCustomers WHERE ContactName LIKE "SearchSubstring%" AND ROWNUM <= PageSize

DB2

SELECT * FROM tblCustomers WHERE ContactName LIKE "SearchSubstring%"  FETCH FIRST PageSize ROWS ONLY

SmartSearch / SmartList Modes

Both of these modes use a more open ended "fuzzy search" method and we (of course) leave this up to you provide ordered records in the order that you want them. The idea here is that you can order your records based on priority, frequency, importance, etc.

Essentially this involves creating an open ended wildcard search for a substring in your table, and then ORDER BY your "importance" field (if you have one). In our included demos we use a table of eMail contacts and order them by the number of times we"ve emailed them. When a user types "vin" - for example - they might receive "Devin Jones, Evin Smith, Vinnie Sucloy" and those records would appear in different order each time depending on how they get sorted by our SQL statement.

In other words, it is up to you, the developer, to order the data on the server before it is sent to the combo.

Here are some example wildcard queries for each of our database types. In these examples we have made up a database field "CustomerImportance" that is being used to sort our dataset.

Microsoft Access

SELECT TOP PageSize * FROM tblCustomers WHERE ContactName LIKE "%SearchSubstring%" ORDER BY CustomerImportance DESC

MySQL

SELECT * FROM tblCustomers WHERE ContactName LIKE "%SearchSubstring%" ORDER BY CustomerImportance LIMIT PageSize

SQL Server

SELECT TOP PageSize * FROM tblCustomers WHERE ContactName LIKE "%SearchSubstring%" ORDER BY CustomerImportance DESC

Oracle

SELECT * FROM tblCustomers WHERE ContactName LIKE "%SearchSubstring%" AND ROWNUM <= PageSize ORDER BY CustomerImportance DESC

DB2

SELECT * FROM tblCustomers WHERE ContactName LIKE "%SearchSubstring%"  ORDER BY CustomerImportance FETCH FIRST PageSize ROWS ONLY

Unbound Mode

In this mode we don"t bind to a remote dataset. Instead we bind to a static local dataset contained inside the page. No SQL queries are required here.