More than one customer posted a query—Q241076 and S32198—about using a multi-column filter in an ASPxComboBox in an ASPxGridView and searching on any of the columns. I checked and this isn’t currently supported in an ASPxComboBox. It just demonstrates that our customers can always come up with scenarios that we haven’t gotten around to supporting directly. It also demonstrates that sometimes one needs to get a little creative and JavaScript is useful in these circumstances. It takes a little bit of work as such things do, but here is a careful post that will walk you through the process. (It will be helpful if you have seen the video “ASP.NET Grid – How to use a Combo Box with Incremental Filtering” video at tv.devexpress.com. I have included those instructions too in this solution.)
For the example I am using the Northwind database tables Products and Categories. The Categories table’s CategoryID and CategoryName will used to look up the CategoryID for the Products table. To enable filtering follow these instructions:
Enable Incremental Filtering
1. Add an ASPxGridView on an ASPX form
2. Using the smart tags to configure the Products table as the grids primary data source
3. Select ProductID, ProductName, CategoryID, and UnitsInStock
4. On the Configure Select Statement step of the wizard click the Advanced button and check generate INSERT, UPDATE, and DELETE statements
5. In the ASPxGridView’s smart tags menu check Enable Editing. (It is when you are in editing mode that the ComboBox will be displayed.)
6. Drag and drop a second SqlDataSource from the Data tab of the Toolbox and configure it to point at the Categries table; configure it to return the CategoryID and CategoryName, which will be used for the lookup for the Products.CategoryID column
7. Click on the ASPxGridView’s smart tags menu and click Columns to display the columns editor
8. For the CategoryID column click the ChangeTo button and select ComboBox column (see Figure 1)
9. Change the CategoryID column’s Caption to Category and set the FieldName to CategoryID (see Figure 1)
10. In the same editor form expand the PropertiesComboBox property and set the DataSourceID to the second SqlDataSource (in my example I used the default name of SqlDataSource2)
11. In the PropertiesComboBox set the DropDownStyle to DropDownList and EnableIncrementalFiltering to True, set the TextField to CategoryName, the ValueField to CategoryID, and the ValueType to System.String
After this step you have basically enabled default filtering and the web form should look like Figure 2. The grid is not configured as described in the video and filtering will be based on the first value for the combobox, the CategoryID. We want to get to a multi-column combo and multi-column filtering, which is where I will be describing new information.
Figure 1: Change the CategoryID type to ASPxComboBox Column and configure the lookup information.
Figure 2: The ASPxGridView as it should appear in design time after step 11.
To add multi-column support in the ASPxGridView you need to perform some additional steps, which includes adding an ASPxComboBox to the edit template for the Category column and defining the columns to display and supporting filtering through those items. The next steps represent new material and provide a satisfactory solution for filtering based on any text in any of the columns of the ASPxComboBox.
Adding Multi-Column Filtering Support with JavaScript
1. Click the ASPxGridView’s smart tags menu and select Edit Template
2. Choose the EditItem Template and place and ASPxComboBox in the EditItem Template designer (see Figure 3).
3. For the ASPxComboBox set the DataSourceID to SQlDataSource2—the data source with the Categories table
4. For the ASPxComboBox set the TextField to CategoryName and the ValueField to CategoryID and the ValueType to System.Int32.
5. For the ASPxComboBox set the EnableIncrementalFiltering property to True and set the DropDowntStyle property to DropDownList.
6. For the ASPxComboBox set the ClientInstanceName to ‘Combo’.
7. For the ASPxComboBox click the Columns editor and add two columns—one for the CategoryID and the other for the CategoryName. (I set the Captions to ID and Name respectively.)
8. Here is the key to the solution: add a ClientSideEvents-KeyPress attribute to the ASPxComboBox and call a local function (we’ll add) named Validate, passing the two event arguments s and e.
9. Implement the Validate function to search through all columns in the ASPxComboBox. (The implementation of the Validate function is shown in Listing 1 and the complete ASPX is provided in Listing 2.)
The runtime appearance of the ASPX solution is shown in Figure 4. Simply type in the ID or first character of the name that you want to select and the ASPxComboBox will cycle through the items by name or match the ID actually entered. In an actual production implementation add some sanity checking around the if-conditional checks and indexing of the various client-side combobox elements.
Of course, our client-side controls—for each control like the ASPxComboBox there is a client-side equivalent, ASPxClientComboBox—and these controls also have a very rich feature set. Some times finding what you need takes a little experimentation but the evangelists and support team our here to offer assistance. Having recently written the Professional DevExpress ASP.NET Controls I encourage you post your queries to our support team, but you can also send them to me in conjunction with posting to our support system at paulk@devexpress.com. I enjoy a challenge. And, if enough people need a feature—the support team track the number of inquiries—then the developers work diligently to prioritize these features and add them to the built-in behavior.
[Caveats: This code only checks one character at a time and won’t work when EnableCallbackMode is True for the ASPxComboBox, and so is a partial solution. The solution seems to work best when the cursor and the input text is in the last position. I am tinkering with a broader solution.]
Figure 3: Add an ASPxComboBox to the EditItem template.
Figure 4: The run-time appearance of the solution.
Listing 1: The Validate function that searches all columns of the ASPxComboBox and increments the selected item based on user input.
function Validate(s, e) {
//debugger;
var ch = String.fromCharCode(e.htmlEvent.keyCode);
// get all items
for (var i = 0; i < Combo.GetItemCount(); i++) {
// check each column item
for (var j = 0; j < Combo.GetItem(i).texts.length; j++) {
var str = Combo.GetItem(i).texts[j];
if (ch.toUpperCase() == str.substr(0, 1) ||
ch.toLowerCase() == str.substr(0, 1)) {
if (Combo.GetSelectedIndex() == i) continue;
Combo.SetSelectedIndex(i);
Combo.SetCaretPosition(Combo.GetItem(i).text.length);
return;
}
}
}
}
Listing 2: The complete ASPX listing for the solution. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register assembly="DevExpress.Web.ASPxGridView.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxGridView" tagprefix="dxwgv" %>
<%@ Register assembly="DevExpress.Web.ASPxEditors.v9.2, Version=9.2.8.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" namespace="DevExpress.Web.ASPxEditors" tagprefix="dxe" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<script type="text/javascript">
function Validate(s, e) {
//debugger;
var ch = String.fromCharCode(e.htmlEvent.keyCode);
// get all items
for (var i = 0; i < Combo.GetItemCount(); i++) {
// check each column item
for (var j = 0; j < Combo.GetItem(i).texts.length; j++) {
var str = Combo.GetItem(i).texts[j];
if (ch.toUpperCase() == str.substr(0, 1) ||
ch.toLowerCase() == str.substr(0, 1)) {
if (Combo.GetSelectedIndex() == i) continue;
Combo.SetSelectedIndex(i);
Combo.SetCaretPosition(Combo.GetItem(i).text.length);
return;
}
}
}
}
</script>
<form id="form1" runat="server">
<div>
<dxwgv:ASPxGridView ID="ASPxGridView1" runat="server"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
KeyFieldName="ProductID">
<Columns>
<dxwgv:GridViewCommandColumn VisibleIndex="0">
<EditButton Visible="True">
</EditButton>
</dxwgv:GridViewCommandColumn>
<dxwgv:GridViewDataTextColumn FieldName="ProductID" ReadOnly="True"
VisibleIndex="1">
<EditFormSettings Visible="False" />
</dxwgv:GridViewDataTextColumn>
<dxwgv:GridViewDataTextColumn FieldName="ProductName" VisibleIndex="2">
</dxwgv:GridViewDataTextColumn>
<dxwgv:GridViewDataComboBoxColumn Caption="Category" FieldName="CategoryID"
VisibleIndex="3">
<PropertiesComboBox DataSourceID="SqlDataSource2"
EnableIncrementalFiltering="True" TextField="CategoryName"
ValueField="CategoryID" ValueType="System.String">
</PropertiesComboBox>
<EditItemTemplate>
<dxe:ASPxComboBox ID="ASPxComboBox1" runat="server"
DataSourceID="SqlDataSource2" EnableIncrementalFiltering="True"
TextField="CategoryName"
ValueField="CategoryID" ValueType="System.Int32" ClientInstanceName="Combo"
ClientSideEvents-KeyPress="function(s,e){Validate(s,e);}"
>
<Columns>
<dxe:ListBoxColumn Caption="ID" FieldName="CategoryID" />
<dxe:ListBoxColumn Caption="Name" FieldName="CategoryName" />
</Columns>
</dxe:ASPxComboBox>
</EditItemTemplate>
</dxwgv:GridViewDataComboBoxColumn>
<dxwgv:GridViewDataTextColumn FieldName="UnitsInStock" VisibleIndex="4">
</dxwgv:GridViewDataTextColumn>
</Columns>
<Settings ShowFilterRow="True" />
</dxwgv:ASPxGridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
InsertCommand="INSERT INTO [Products] ([ProductName], [CategoryID], [UnitsInStock]) VALUES (@ProductName, @CategoryID, @UnitsInStock)"
SelectCommand="SELECT [ProductID], [ProductName], [CategoryID], [UnitsInStock] FROM [Products]"
UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [CategoryID] = @CategoryID, [UnitsInStock] = @UnitsInStock WHERE [ProductID] = @ProductID">
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="CategoryID" Type="Int32" />
<asp:Parameter Name="UnitsInStock" Type="Int16" />
<asp:Parameter Name="ProductID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="CategoryID" Type="Int32" />
<asp:Parameter Name="UnitsInStock" Type="Int16" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories]">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>