Stored Procedure
The stored procedure uses ROW_NUMBER() to return a specific 25-row window from the matching result set.
ALTER PROCEDURE dbo.fp_PTArchitect_SEL_top25
-- Lazy-loaded Architect lookup for Telerik RadComboBox.
-- @ArchitectName filters Architect Name as the user types.
-- @StartIndex is the number of rows already loaded by the dropdown.
-- Returns the next 25 matching Architect records for virtual scrolling.
@ArchitectName VARCHAR(100),
@StartIndex INT = 0
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @RowsToReturn INT = 25
SELECT @ArchitectName = '%' + RTRIM(@ArchitectName) + '%'
;WITH Architects AS
(
SELECT
arch.ArchitectID,
RTRIM(arch.Architect_Name) AS Architect_Name,
ROW_NUMBER() OVER (ORDER BY arch.Architect_Name, arch.ArchitectID) AS RowNumber
FROM dbo.PTArchitect arch
WHERE arch.INACTIVE = 0
AND arch.Architect_Name LIKE @ArchitectName
)
SELECT
ArchitectID,
Architect_Name
FROM Architects
WHERE RowNumber > @StartIndex
AND RowNumber <= @StartIndex + @RowsToReturn
ORDER BY RowNumber
GO
GRANT EXEC ON dbo.fp_PTArchitect_SEL_top25 TO public
GO
The important detail is that the query numbers the complete matching set first. It then returns only the requested range.
For example:
fp_PTArchitect_SEL_top25 'design', 0
Returns matches 1 through 25.
fp_PTArchitect_SEL_top25 'design', 25
Returns matches 26 through 50.
fp_PTArchitect_SEL_top25 'design', 50
Returns matches 51 through 75.
RadComboBox Markup
Replace a normal RadDropDownList with a RadComboBox configured for load-on-demand and virtual scrolling.
<telerik:RadComboBox runat="server" ID="ddlArchitect" Width="300px" MaxHeight="250px" EnableLoadOnDemand="true" ShowMoreResultsBox="true" EnableVirtualScrolling="true" AllowCustomText="false" Filter="Contains" AutoPostBack="true"></telerik:RadComboBox>
Important properties:
|
Property
|
Purpose
|
|
EnableLoadOnDemand="true"
|
Requests lookup data only when needed.
|
|
EnableVirtualScrolling="true"
|
Requests additional records when the user scrolls.
|
|
ShowMoreResultsBox="true"
|
Displays the loading/more-results UI.
|
|
MaxHeight="250px"
|
Gives the dropdown a scrollable result area.
|
|
Filter="Contains"
|
Supports searching within Architect names.
|
|
AllowCustomText="false"
|
Requires the user to select a valid lookup record.
|
|
AutoPostBack="true"
|
Runs the selected-index event after the user chooses an Architect.
|
Initial Binding
The initial page bind requests the first 25 active Architects.
ddlArchitect.DataSource = fp_PTArchitect_SEL_top25("", 0, oAppWeb.ConnectionString).getTable
ddlArchitect.DataTextField = "Architect_Name"
ddlArchitect.DataValueField = "ArchitectID"
ddlArchitect.DataBind()
The second parameter is the starting index. A value of 0 means “return the first page.”
Lazy-Loading Event
ItemsRequested fires when Telerik needs records. This happens when the user types a search term or scrolls to the bottom of the loaded results.
Private Sub ddlArchitect_ItemsRequested(sender As Object, e As RadComboBoxItemsRequestedEventArgs) Handles ddlArchitect.ItemsRequested
Dim strError As String = ""
Try
Dim strArchitectSearch As String = e.Text.Trim()
If strArchitectSearch = "" AndAlso e.NumberOfItems = 0 Then
strArchitectSearch = ddlArchitect.Text.Trim()
End If
Dim oDT As DataTable = fp_PTArchitect_SEL_top25(strArchitectSearch, e.NumberOfItems, oAppWeb.ConnectionString).getTable
ddlArchitect.DataSource = oDT
ddlArchitect.DataTextField = "Architect_Name"
ddlArchitect.DataValueField = "ArchitectID"
ddlArchitect.DataBind()
e.EndOfItems = oDT.Rows.Count < 25
Catch ex As Threading.ThreadAbortException
Catch ex As Exception
globalErrorHandler(ex, strError, oAppWeb.AppName, oAppWeb.UserName, False)
Me.lblError.Text = ex.Message
End Try
End Sub
e.NumberOfItems is Telerik’s count of lookup rows already loaded:
|
e.NumberOfItems
|
SQL behavior
|
|
0
|
Load rows 1–25
|
|
25
|
Load rows 26–50
|
|
50
|
Load rows 51–75
|
The following line tells Telerik when it has reached the final batch:
e.EndOfItems = oDT.Rows.Count < 25
When fewer than 25 rows come back, the control stops showing an endless loading state.
Selected Architect Event
Because the control is now a RadComboBox, the selected-index event must use Telerik’s ComboBox event type.
Private Sub ddlArchitect_SelectedIndexChanged(sender As Object, e As RadComboBoxSelectedIndexChangedEventArgs) Handles ddlArchitect.SelectedIndexChanged
A previous RadDropDownList event signature will no longer compile:
DropDownListEventArgs
When the selected Architect is used by procedures that expect an integer ID, guard against a blank value before running follow-up lookup logic:
If ddlArchitect.SelectedValue.Trim() = "" Then
ddlArchContact.Items.Clear()
Exit Sub
End If
Designer File
After replacing the ASPX control, confirm that the generated designer declaration also uses RadComboBox.
Protected WithEvents ddlArchitect As Telerik.Web.UI.RadComboBox
A mismatch between the ASPX control and designer declaration causes an ASP.NET parser error similar to:
The base class includes the field 'ddlArchitect', but its type
(Telerik.Web.UI.RadComboBox) is not compatible with the type of control
(Telerik.Web.UI.RadDropDownList).
Summary
This pattern is useful for Architect, Owner, Contractor, Bidder, Item, Project, and other large lookup lists.
The core implementation is:
- SQL returns the next 25 matching rows.
- Telerik passes the count already loaded through
e.NumberOfItems.
- The page sets
e.EndOfItems when fewer than 25 records are returned.
This avoids loading hundreds or thousands of records into every page while still giving users a fast searchable lookup experience.