Change SQLDataSource.SQLCommand content on OnEntryAdded event?

[Yan Moura]

I am starting to work on a new project using AutoCompleteBox and I need to change my searches after every entry added by the client.

I am using the tutorial code that uses SQLDataSource. I tried to update the value of the property SQLDataSource.SelectCommand with a new query into the OnEntryAdded event this way:

Protected Sub RadAutoCompleteBox1_EntryAdded(sender As Object, e As Telerik.Web.UI.AutoCompleteEntryEventArgs)
        SqlDataSource1.SelectCommand = "my new query here"
End Sub

The problem is that the event is being triggered and the content of SqlDataSource1.SelectCommand is being changed but AutoCompleteBox keep using the initial hard coded query of the client side script.

Any idea?

[Peter Milchev]

Hello Yan,

The SqlDataSource restores the SelectCommand on each request to the server, so the best workaround would be to use parameters as the SelectCommand cannot be changed permanently dynamically. Here are some threads on the topic:

• https://forums.asp.net/t/1535310.aspx?Changing+Select+Command+of+SQLDataSource+from+code+behind;
• https://forums.asp.net/t/1435639.aspx?Changing+SQL+DataSource+Query+at+Runtime

Regards,
Peter Milchev
Progress Telerik

[Yan Moura]

Unfortunately it didn't help. I checked the two suggested threads and tested what was proposed but the query still doesn't work. As I mentioned I manage to update the value of SelectCommand to a new query but the code persists using the initial query.

Here it is my code:

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

        <h2>iPMP (demo)</h2>
        <telerik:RadAutoCompleteBox RenderMode="Lightweight" runat="server" ID="RadAutoCompleteBox1" autopostback="true" EmptyMessage="" DataSourceID="SqlDataSource1" DataTextField="mfrname" InputType="Token" Width="450" DropDownWidth="150px" OnEntryAdded="RadAutoCompleteBox1_EntryAdded" >
        </telerik:RadAutoCompleteBox>

<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:MyConn %>" SelectCommand="SELECT DISTINCT [mfrname] FROM [Manufacturers] ORDER BY [mfrname]">
</asp:SqlDataSource>
</form>

 

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"
    SqlDataSource1.Select(DataSourceSelectArguments.Empty)
    SqlDataSource1.DataBind()
    RadAutoCompleteBox1.DataBind()
End Sub

[Yan Moura]

I found the solution. Well, not the ideal and 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 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 AsTelerik.Web.UI.AutoCompleteEntryEventArgs)
'Use CASE structure to change the parameter accordingly to your needs
lblSQLpar.Text = "m%"
End Sub