Constructing SQL Queries for ComboBox V3

Note: This article applies to JSP, PHP, and Classic ASP users. ASP.NET users should read this article instead. ComboBox V3 boasts 6 different search modes. Each have different requirements for the way data is being selected from the master dataset.

Querystring Parameters

Web 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

These parameters help you build your SQL select statement. These parameters are defined as follows:

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

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.

Constructing SQL Queries for Combo V3 (ASP.NET)

View Printable Version

Comments:

Poster: David (Sunday, July 29, 2007)

I have noticed that, when back-spacing and then retyping within the ComboBox, the LIKE clause in my SELECT statement are inaccurate. For example, I have a table of locations with IDs that range from 1001 to 3500. When typing "LOCATION ID 129" the combo box will display values of "LOCATION ID 129", "LOCATION ID 1290", "LOCATION ID 1291", etc. HOWEVER, when I backspace and remove the trailing 9 ("LOCATION ID 12") and retype "9" ("LOCATION ID 129") the results are "LOCATION ID 129", "LOCATION ID 1200", "LOCATION ID 1201", etc.

Has anyone else experienced this or have a solutions to fix the issue, perhaps requerying the whole box? Any help on this would be greatly appreciated. Thanks.

Dave

Nitobi Product Knowledgebase