Cannot change SelectCommand contents dynamically

Question

I am having an issue trying to change SelectCommand contents dynamically.

There is this Telerik searchbox control that uses SQLDataSource to constantly bang the DB with a select query and show up a list of words filtered by your typing. Then if you pick an entry from the list it will become a "token" and then you can start typing again.

In my case, the query should return a list of car makes and the initial query is:

SELECT DISTINCT mfrname 
FROM Manufacturers 
WHERE mfrname IS NOT NULL 
ORDER BY mfrname

So if I type "Che" it will show up "Checker" and "Chevrolet". So if I click "Chevrolet" it will become a token and my typing is reset so I can start typing again.

For tests purposes after the token is generated I slighted changed my query to:

SELECT DISTINCT mfrname 
FROM manufacturers 
WHERE mfrname IS NOT NULL and mfrname like 'm%' 
ORDER BY mfrname

Notice that now it should only select the car makes started with an "M" like "Mercedes", "Maseratti", etc, so if I type anything else that doesn't starts with an "M" it shouldn't show anything.

However if I type "Che" there it is "Checker" and "Chevrolet" again, making it clear that it's still using the initial query and not the new one.

OKay, I know that the event IS being triggered and the SelectCommand value IS being changed (I know this because I added a label that changes and shows up the new value). What I don't know is why the control insists on keep using the old query and not the new one! Any idea?

My code front:

<form id="form1" runat="server">
    <telerik:RadScriptManager runat="server" ID="RadScriptManager1" />

    <telerik:RadAutoCompleteBox RenderMode="Lightweight" runat="server" ID="RadAutoCompleteBox1" autopostback="true" EmptyMessage="Type in car make..."
        DataSourceID="SqlDataSource1" DataTextField="mfrname" InputType="Token" Width="450" DropDownWidth="150px" OnEntryAdded="RadAutoCompleteBox1_EntryAdded" >
    </telerik:RadAutoCompleteBox>

    <asp:SqlDataSource runat="server" ID="SqlDataSource1" CancelSelectOnNullParameter="false" ConnectionString="<%$ ConnectionStrings:MyConn %>" SelectCommand="SELECT DISTINCT mfrname FROM Manufacturers WHERE mfrname IS NOT NULL ORDER BY mfrname">
    </asp:SqlDataSource>

    <div>
        <br />
        <asp:label id="label1" runat="server">Waiting for update...</asp:label>
    </div>

    </form>

My code behind:

Protected Sub RadAutoCompleteBox1_EntryAdded(sender As Object, e As Telerik.Web.UI.AutoCompleteEntryEventArgs)
    SqlDataSource1.SelectCommand = "SELECT DISTINCT mfrname FROM manufacturers WHERE mfrname IS NOT NULL and mfrname like 'm%' ORDER BY mfrname"
    RadAutoCompleteBox1.DataBind()
    label1.Text = e.Entry.Text + " was added. (" + SqlDataSource1.SelectCommand + ")"
End Sub

Try cleaning and rebuilding your project. – preciousbetine Jan 13 '20 at 18:56 — preciousbetine, Jan 13 '20 at 18:56

score 1 · Accepted Answer · edited Jan 13 '20 at 19:49

I found the solution. Well, more like a work around but it works.

In my case I didn't need to change entirely the query, but actually only the WHERE clause. So I added a dynamic parameter to the query text and tied it to another control (an invisible label) forcing the AutoCompleteBox to always look into the contents of the label before to run the query.

This way, changing the contents of the label will change the parameter used in my WHERE clause and so I can control the query. It may be expanded by simply adding more parameters to the SQLDataSource and adding additional invisible controls to the page.

So here it goes the implemented solution since it may be of help for someone else...

Code front:

<form id="form1" runat="server">
    <telerik:RadScriptManager runat="server" ID="RadScriptManager1" />
    <telerik:RadAutoCompleteBox RenderMode="Lightweight" runat="server" ID="RadAutoCompleteBox1" autopostback="true" EmptyMessage="Type in car make..." DataSourceID="SqlDataSource1" DataTextField="mfrname" InputType="Token" Width="450" DropDownWidth="150px" OnEntryAdded="RadAutoCompleteBox1_EntryAdded" >
    </telerik:RadAutoCompleteBox>

    <asp:SqlDataSource runat="server" ID="SqlDataSource1" CancelSelectOnNullParameter="false" ConnectionString="<%$ ConnectionStrings:MyConn %>" SelectCommand="SELECT DISTINCT mfrname FROM Manufacturers WHERE mfrname like @mypar and mfrname IS NOT NULL ORDER BY mfrname">
        <SelectParameters>
            <asp:ControlParameter ControlID="lblSQLpar" Name="mypar" PropertyName="Text" />
        </SelectParameters>
    </asp:SqlDataSource>

    <div>
        <!-- initial value -->
        <asp:label id="lblSQLpar" runat="server" Visible="false">%</asp:label>
    </div>

</form>

code behind:

Protected Sub RadAutoCompleteBox1_EntryAdded(sender As Object, e As Telerik.Web.UI.AutoCompleteEntryEventArgs)
    'Use CASE structure to change the parameter accordingly to your needs
    lblSQLpar.Text = "m%"
End Sub

A hidden form field instead of a label would probably be better, but this is a fine solution. — Joel Coehoorn, Jan 13 '20 at 19:54

Cannot change SelectCommand contents dynamically

1 Answers1