This is a migrated thread and some comments may be shown as answers.

Change SQLDataSource.SQLCommand content on OnEntryAdded event?

3 Answers 429 Views
AutoCompleteBox
This is a migrated thread and some comments may be shown as answers.
Yan Moura
Top achievements
Rank 1
Veteran
Iron
Yan Moura asked on 07 Jan 2020, 06:29 PM

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?

3 Answers, 1 is accepted

Sort by
0
Peter Milchev
Telerik team
answered on 10 Jan 2020, 01:36 PM

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:

Regards,
Peter Milchev
Progress Telerik

Get quickly onboarded and successful with UI for ASP.NET AJAX with the Virtual Classroom technical trainings, available to all active customers. Learn More.
0
Yan Moura
Top achievements
Rank 1
Veteran
Iron
answered on 13 Jan 2020, 02:54 PM

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
0
Yan Moura
Top achievements
Rank 1
Veteran
Iron
answered on 13 Jan 2020, 07:44 PM

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

 

 

Tags
AutoCompleteBox
Asked by
Yan Moura
Top achievements
Rank 1
Veteran
Iron
Answers by
Peter Milchev
Telerik team
Yan Moura
Top achievements
Rank 1
Veteran
Iron
Share this question
or