ASPxComboBox question relating to Code Sample E1426

I have a particularly nasty table ("Species") with 78 fields, of which 17 have Foreign Keys that each require an ASPxComboBox. What's more, there are 96568 records in the table. One of the FK fields is a drop-down on a table ("Genus") with 11260 records.

If I leave out all the FK fields, ASPxGridview takes 6 seconds to load the entire table, which I think is pretty amazing. Adding in all the LEFT JOIN statements and displaying the FK description instead of the numerical value adds only 2 seconds to the load time. Again, this is pretty impressive. ASPxGridView is amazing.

The problam comes when I click on the "Edit" button. The edit screen has been broken down into 5 tabbed pages, but if I have data-bound ASPxComboBoxes, then the edit form takes around 10 *minutes* to load, even with callbacks enabled and only 10 items being loaded. If I leave out the data binding, as per E1426, the load time is an impressive 12 *seconds*. I need some help in creating the code to bind the Combo Box back to its data source when the user clicks on the drop-down box in order to do some editing.

Here is a VB.NET code snippet using Northwind's SalesOrder table as an example:

        <Templates>
             <EditForm>

<dxe:ASPxLabel ID="ASPxLabelShipperId" runat="server" Text="Ship Via" ForeColor="navy"></dxe:ASPxLabel>

<dxe:ASPxComboBox ID="ASPxComboBoxSalesOrder__ShipperId" runat="server" visible="True" Value='<%# Eval("Shipper__ShipperName") %>' Width="400px" ValueField="SalesOrder__ShipperId" ValueType="System.Int32" EnableCallbackMode="True" EnableIncrementalFiltering="True" CallbackPageSize="10" OnCallback="OnCallbackSalesOrder__ShipperId" ></dxe:ASPxComboBox><Columns><dxe:ListBoxColumn Caption="Shipper" FieldName="Shipper__ShipperName" Width="400px" /></Columns></dxe:ASPxComboBox>

</EditForm>
etc. The data source definition looks like this:

 <% '-- FK Data Source: Shipper -- %>
 <asp:SqlDataSource ID="SqlDataSourceShipperId" runat="server"
   ConnectionString = "<%$ ConnectionStrings:SampleConnectionString %>"
   SelectCommand="SELECT [ShipperId] AS SalesOrder__ShipperId, [ShipperName] AS Shipper__ShipperName FROM [Shipper] ORDER BY [ShipperName]">
   <UpdateParameters>
   <asp:Parameter Name="SalesOrder__ShipperId" Type="Int32" />
   <asp:Parameter Name="Shipper__ShipperName" Type="String" />
   </UpdateParameters>
 </asp:SqlDataSource>

and I have created a VB.NET callback event as follows:

    Protected Sub OnCallbackSalesOrder__ShipperId(ByVal source As Object, ByVal e As CallbackEventArgsBase)

    End Sub ' OnCallbackSalesOrder__ShipperId

What code do I put in this subroutine to get it to tell the control to use "SqlDataSourceShipperId" as the data source?

I have struggled to figure this out, and I'm getting nowhere. Please help! VB.NET is still a strange language to me after years of using VBA and VB6. I have searched through the code samples but this one is eluding me.

Thanks in advance
Donn

Hi Donn,

We're researching your issue and will answer you soon.
Thank you for your patience.

Thanks,
Serj

Hello Donn,

I hope that the following sample will help you:

===========================
ASPX:

<form id="form1" runat="server">
        <dxwgv:ASPxGridView ID="ASPxGridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" KeyFieldName="OrderID">
            <Templates>
                <EditForm>
            
                    <dxe:ASPxComboBox ID="ASPxComboBox1" runat="server"
                        Value='<%# Eval("CustomerID") %>' ValueType="System.String" ValueField="CustomerID" TextField="CustomerID"
                        OnCallback="ASPxComboBox1_Callback"
                        EnableCallbackMode="true" CallbackPageSize="10" EnableIncrementalFiltering="true">
                        <Items>
                            <dxe:ListEditItem Text=" "/>
                        </Items>
                        <ClientSideEvents DropDown="function(s){
                            if(s.GetItemCount() == 1)
                                s.PerformCallback();
                            }" />
                    </dxe:ASPxComboBox>
                    
                    <div style="text-align:right; padding:2px 2px 2px 2px">
                         <dxwgv:ASPxGridViewTemplateReplacement ID="UpdateButton" ReplacementType="EditFormUpdateButton" runat="server"></dxwgv:ASPxGridViewTemplateReplacement>
                         <dxwgv:ASPxGridViewTemplateReplacement ID="CancelButton" ReplacementType="EditFormCancelButton" runat="server"></dxwgv:ASPxGridViewTemplateReplacement>
                    </div>

                </EditForm>
            </Templates>
            <Columns>
                <dxwgv:GridViewCommandColumn VisibleIndex="0">
                    <EditButton Visible="True">
                    </EditButton>
                    <NewButton Visible="True">
                    </NewButton>
                </dxwgv:GridViewCommandColumn>
                <dxwgv:GridViewDataTextColumn FieldName="OrderID" ReadOnly="True" VisibleIndex="1">
                    <EditFormSettings Visible="False" />
                </dxwgv:GridViewDataTextColumn>
                <dxwgv:GridViewDataTextColumn FieldName="CustomerID" VisibleIndex="2">
                </dxwgv:GridViewDataTextColumn>
            </Columns>
        </dxwgv:ASPxGridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [CustomerID] FROM [Customers]"></asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [OrderID], [CustomerID] FROM [Orders]"></asp:SqlDataSource>
    </form>

============================
VB:

Public Partial Class Default4
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
       
    End Sub
    Protected Sub ASPxComboBox1_Callback(ByVal source As Object, ByVal e As DevExpress.Web.ASPxClasses.CallbackEventArgsBase)
        Dim cb As ASPxComboBox = DirectCast(source, ASPxComboBox)
        cb.DataSource = SqlDataSource1
        cb.DataBindItems()
    End Sub
End Class

=============================
C#:

public partial class Default4 : System.Web.UI.Page {
    protected void Page_Load(object sender, EventArgs e) {

    }
    protected void ASPxComboBox1_Callback(object source, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e) {
        ASPxComboBox cb = (ASPxComboBox)source;
        cb.DataSource = SqlDataSource1;
        cb.DataBindItems();
    }
}

Thanks,
Andrew

Attachment: SalesOrder.GIF (46059 bytes)

Thanks for the prompt reply. I have only been able to return to this project today. You guys get full marks for *amazing* technical support. No wonder you win so many awards.;

The solution provided works perfectly when the Combo box has a single field, but not so well for multiple fields. I have done a work-around for multiple fields, but it isn't ideal:

ASPX:

<dxe:ASPxComboBox ID="ASPxComboBoxSalesOrder__CustomerId" runat="server"
    Value='<%# eval("SalesOrder__CustomerID") & "; " & eval("Customer__CompanyName") %>' ValueType="System.String" ValueField="SalesOrder__CustomerID" TextField="SalesOrder__CustomerID"
    OnCallback="ASPxComboBoxSalesOrder__CustomerId_Callback"
    EnableCallbackMode="true" CallbackPageSize="10" EnableIncrementalFiltering="true">
     <Columns>
         <dxe:ListBoxColumn FieldName="SalesOrder__CustomerID" Width="10%" Caption="CustomerId" />
         <dxe:ListBoxColumn FieldName="Customer__CompanyName" Width="100%" Caption="Customer Name" />
     </Columns>
    <Items>
        <dxe:ListEditItem Value=" " Text=" " />
    </Items>
    <ClientSideEvents DropDown="function(s){
        if(s.GetItemCount() == 1)
            s.PerformCallback();
        }" />
</dxe:ASPxComboBox>

The CustomerId is shown, followed by a semicolon and a space, and then the description. When I run the code to do the updating, it looks for the "; " string and truncate the data at that point, but the displayed page looks a bit messy.

Is there a simple way I can hide the FK values and just show the descriptions? That would turn a working solution into an excellent solution! The speed of the method provided is great, and I am constantly amazed at how well ASPxGridView works.

Attachment: SalesOrder.GIF (26684 bytes)

I have tweaked the page a bit by putting the FK field after the description, which makes it easier to work with (see screen shot) but I'd prefer to do it properly if at all possible.

Any ideas?

Hello Donn,

I think that you can play with the ASPxComboBox's TextFormatString property to solve you problem.
Please, learn more at:

ASPxComboBox.TextFormatString Property
ComboBoxProperties.TextFormatString Property
Demo: ASPxComboBox (Multiple Columns)

Thanks,
Andrew

The use of the TextformatString property has helped a little, but I still have a problem with unbound comboboxes.

In the case of a bound Combo box, such as example E422, I can have the foreign key hidden, and the description visible, and the drop-down box displays the description only. i.e on E422 the value of RegionID is modified when the user selects a new value from the ComboBox, even though the user never sees RegionId, only RegionDescription

In the case of an unbound combo box, where do I store RegionId since I am displaying RegionDescription on the page, and the ComboBox doesn't have any data in it yet? It seems that in the case of unbound controls I can't have a hidden foreign key. Or am I missing something obvious?

Thanks once again for excellent support and a great product!
Donn

Hello Donn,

For now, the ASPxComboBox doesn't support unbound mode in the multi-column mode. If you want to use a multi-column ASPxComboBox without any database, a simple way to implement this is to emulate data binding by populating its Items collection using a DataTable:

    public DataTable GetData() {
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("Value"));
        dt.Columns.Add(new DataColumn("Text"));
        dt.Columns.Add(new DataColumn("Description"));
        dt.Rows.Add(new object[] { "0", "T0", "D0" });
        dt.Rows.Add(new object[] { "1", "T1", "D1" });
        dt.Rows.Add(new object[] { "2", "T2", "D2" });
        return dt;
    }

    public void DataBindComboBox(ASPxComboBox cb) {
        cb.ValueField = "Value";
        cb.Columns.Add(new ListBoxColumn("Text"));
        cb.Columns.Add(new ListBoxColumn("Description"));

        cb.DataSource = GetData();
        cb.DataBind();
    }

If the multi-column mode is not used, the ASPxComboBox allows its items to be created declaratively in the aspx markup:

<dxe:ASPxComboBox ID="ASPxComboBox1" runat="server" ValueType="System.String">
            <Items>
                <dxe:ListEditItem Text="0" Value="0" />
                <dxe:ListEditItem Text="1" Value="1" />
                <dxe:ListEditItem Text="2" Value="2" />
            </Items>
        </dxe:ASPxComboBox>

In a single-column mode, items could also be added into the ASPxComboBox Items collection via client-side or server-side API.

Thanks,
Andrew

Excellent! Thanks!