How can I pass variable from jQuery to SelectCommand in SqlDataSource?

Question

I'm trying to pass variable from jQuery to SqlDataSource in dropdownlist.

First, the jQuery's variable get the value from asp:dropdownlist_1 then keep it in the variable in jquery.

Next step, I would like to send the jquery's variable to SelectCommand in dropdownlist_2. Is it possible ?

If you have a good idea, it will be a great honor if you share it to me.

Thank you in advance

This is my ASPX markup:

<asp:DropDownList ID="DropDown_1" runat="server" CssClass="form-control" 
     DataSourceID="SqlDataSource_1" 
     DataTextField="field_1" DataValueField="field_2"></asp:DropDownList>                               
<asp:SqlDataSource ID="SqlDataSource_1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:asrsDBConnectionString %>" 
     SelectCommand="SELECT * FROM [table_1]"></asp:SqlDataSource>

<asp:DropDownList ID="DropDown_2" runat="server" CssClass="form-control" 
     DataSourceID="SqlDataSource_2" DataTextField="field_1" 
     DataValueField="field_2"></asp:DropDownList>                               
<asp:SqlDataSource ID="SqlDataSource_2"unat="server" 
     ConnectionString="<%$ ConnectionStrings:asrsDBConnectionString %>" 
     SelectCommand="SELECT * FROM [table_2] WHERE [variable_from_jquery] = "value_JQ"></asp:SqlDataSource>

This is my Javascript code:

$('#<%:DropDown_1.ClientID %>').change(function () {
  var value_JQ = $('#<%:DropDown_1.ClientID%>').val();
  return value_JQ;
});

You can reference the drop down directly (without jquery) by using control parameters. see this. If you must use a javascript variable then set the value on a hidden field and then use that as the control parameter per the example linked to. — Crowcoder
– Crowcoder, Commented Sep 30, 2017 at 10:47

Alex Kudryashev · Accepted Answer · 2017-10-01 01:19:33Z

1

It is potentially possible to set some (hidden) input via jQuery and then use it.
But there is no need in such sophisticated approach. Apply KISS principle (Keep It Simple Stupid). Everything can be done in .aspx declaratively. Something like this.

<%--AutoPostBack="true" is important here --%>
<asp:DropDownList runat="server" ID="lstCountry" DataSourceID="sqlCountry" 
    DataTextField="CountryName" DataValueField="CountryId" AutoPostBack="true">
</asp:DropDownList>
<asp:SqlDataSource runat="server" ConnectionString='<%$ConnectionStrings:MyCNN %>' ID="sqlCountry"
    SelectCommand="select CountryId,CountryName from dbo.Countries" />

<asp:DropDownList runat="server" ID="lstState" DataSourceID="sqlState" 
    DataTextField="StateName" DataValueField="StateId">
</asp:DropDownList>
<asp:SqlDataSource runat="server" ConnectionString='<%$ConnectionStrings:MyCNN %>' ID="sqlState"
    SelectCommand="select StateId,StateName from dbo.States where CountryId=@CountryId" >
    <SelectParameters>
        <%--Take parameter value from 1st dropdown --%>
        <asp:ControlParameter Name="CountryId" ControlID="lstCountry" 
            PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

No more code required.

edited Oct 1, 2017 at 1:19

answered Oct 1, 2017 at 1:13

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

K.Olagon Over a year ago

Your method is worked, but can we avoid page's reloading ?

Alex Kudryashev Over a year ago

Sure you can. Wrap the code provided by asp:UpdatePanel.

K.Olagon Over a year ago

Thank you so much Alex. You saved my day !!

Dalokey · Accepted Answer · 2017-09-30 19:00:19Z

This is what I have gathered from your question, but I am not too sure if it is correct.

You have 2 drop down menus, the first selects the value, and the second uses that value in a select statment to get the results you want... If this is the case then I suggest that you don't use jquery, and just use C# and asp.net. Also there is a really useful DropDorwnList event called OnSelectedIndexChanged which can be used to do this really easily.

So this is how it would work, first the aspx file:

<!-- For the first drop down menu, as you have done above, creating a SqlDataSource with a SelectCommand that will get the data from your table (I am using a table called products as an example) -->
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:asrsDBConnectionString %>" 
    ProviderName="<%$ ConnectionStrings:asrsDBConnectionString.ProviderName %>"
    SelectCommand = "Select * from products">
</asp:SqlDataSource>

<!-- For the first drop DropDownList make sure you create a DataValueField of the column you want to show as the values form your DropDownList. Also pay attention to the name of the OnSelectedIndexChanged which will be very important for the C# part. And remember to set AutoPostBack to true... like the code block bellow -->
<asp:DropDownList ID="DropDownList1" runat="server"
    DataSourceID="SqlDataSource1"
    DataValueField="ProductID" 
    OnSelectedIndexChanged ="DropDownList1_SelectedIndexChanged"
    AutoPostBack="true">
</asp:DropDownList>


<!-- Now for the second DropDownList create another SqlDataSource, however this time don't create a SelectCommand because it will be created in the C# file depending on what you selected on your first DropDownList-->
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:asrsDBConnectionString %>" 
    ProviderName="<%$ ConnectionStrings:asrsDBConnectionString.ProviderName %>">
</asp:SqlDataSource>

<!-- when creating the second DropDownList make sure the DataValueField is the column that you want as values -->
<asp:DropDownList ID="DropDownList2" runat="server"
    DataSourceID="SqlDataSource2"
    DataValueField="UnitPrice"
    AutoPostBack="true">
</asp:DropDownList>

Now for the c#, very easy and quick:

//create a new method inside your page class, right under the Page_Load if possible, so inside the page class but outside the Page_Load method. The name of the method is what you set the OnSelectedIndexChanged event to from the first dropdown list. Hence, DropDownList1_SelectedIndexChanged
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    //get the selected value like so:
    string item1 = this.DropDownList1.SelectedValue.ToString();

    //and now set the SelectCommand for SqlDataSource which is used for the 2nd dropdownlist and use the value to filter your table.
    SqlDataSource2.SelectCommand = "Select * from products where ProductID='" + item1 + "'";
    //DataBind is important!
    SqlDataSource2.DataBind();
}

Hope this helps, copy and past it into a new file to see if it works, just make sure to change select statements as I just used the ones above as an example.

Collectives™ on Stack Overflow

How can I pass variable from jQuery to SelectCommand in SqlDataSource?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related