locked
SqlDataSource - filter by selection from listbox - WHERE ([Name] IN ('Alaska','alabama'))" RRS feed

  • Question

  • User-411148529 posted

    I have a listbox and SqlDataSource. 

    I am not sure how to take the multiple value that are selected in listbox and use it to generate a sqlquery.
    Below I have shown that if I hard code it in the Command statement it works but I not sure how to take it from a variable or the listbox.

    ---------------------------------------------------------- 

    <asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource1" DataTextField="Name"

    DataValueField="Name" SelectionMode="Multiple" Width="341px"></asp:ListBox>

     

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

    SelectCommand="SELECT classifieds_Ads.Id, classifieds_Ads.MemberId, classifieds_Ads.CategoryId, classifieds_Ads.Title, classifieds_Ads.Description, classifieds_Categories.Name, classifieds_Ads.Price, classifieds_Ads.Location, classifieds_Ads.ExpirationDate, classifieds_Ads.DateCreated FROM classifieds_Ads INNER JOIN classifieds_Categories ON classifieds_Ads.CategoryId = classifieds_Categories.Id WHERE ([Name] IN ('ALASKA','alabama'))">

    <SelectParameters>

    <asp:ControlParameter Name="Name" ControlID="listbox1" PropertyName="SelectedValue" />

    </SelectParameters>

    </asp:SqlDataSource>

    -------------------------------------------------------- 

     

    Saturday, November 4, 2006 8:25 PM

Answers

  • User-1573490007 posted

    Hmm, I am not 100% sure, but at that point I think you might have to make a custom query.

    Which means that on the Selecting event of the datasource, build your query dynamically, loop through the selected items of the list, as part of the loop insert parameters in the command, etc etc etc.

    I -think- its the only way, but i could be wrong.

    So in the Selecting event, e.Command.CommandText will be your SQL string, and SqlDataSource2.SelectParameters.add will allow you to add all your parameters.

    Not quite as nice as doing everything declaratively, but it works. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 5, 2006 1:37 PM

All replies

  • User-1573490007 posted

    In your WHERE clause, put a parameter, you don't have any. A parameter would be something something starting by @ instead of a flat string.

    So like:

    select * from mytable where whatever = @myparam

    then when you set the parameters in the wizard (like you did, as you bound your listbox's selectedvalue), it will use that value instead of the placeholder. 

    Sunday, November 5, 2006 12:31 AM
  • User-411148529 posted

    Thank You!

    Another related question:
    I am not sure how could I can get the query based on multiple values selected in listbox. Below, I have show the changed SQLDataSource

    I tried as you mentioned and it works but shows me only 1st item selected from listbox.

    But, I want to query using multiple values selected in listbox. eg: Alaska, Michigan, Texas

    Is there something in Parameter Properties that could allow me to accept multiple selected values (like I did for Listbox Control).

     -----------------------------------------------------------------

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

    SelectCommand="SELECT classifieds_Ads.Id, classifieds_Ads.MemberId, classifieds_Ads.CategoryId, classifieds_Ads.Title, classifieds_Ads.Description, classifieds_Categories.Name, classifieds_Ads.Price, classifieds_Ads.Location, classifieds_Ads.ExpirationDate, classifieds_Ads.DateCreated FROM classifieds_Ads INNER JOIN classifieds_Categories ON classifieds_Ads.CategoryId = classifieds_Categories.Id WHERE ([Name] = @newstring)">

    <SelectParameters>

    <asp:ControlParameter ControlID="ListBox1" Name="newstring" PropertyName="SelectedValue" />

    </SelectParameters>

    </asp:SqlDataSource>

    ----------------------------------------------------------------------------------

    Sunday, November 5, 2006 12:55 PM
  • User-1573490007 posted

    Hmm, I am not 100% sure, but at that point I think you might have to make a custom query.

    Which means that on the Selecting event of the datasource, build your query dynamically, loop through the selected items of the list, as part of the loop insert parameters in the command, etc etc etc.

    I -think- its the only way, but i could be wrong.

    So in the Selecting event, e.Command.CommandText will be your SQL string, and SqlDataSource2.SelectParameters.add will allow you to add all your parameters.

    Not quite as nice as doing everything declaratively, but it works. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 5, 2006 1:37 PM
  • User-2040673653 posted

    Now in my solution I had one listbox filter the next one. But you could have one listbox filtering itself if needed, by just changing 1 line of code.

    I have this in my asp.net front-end

          <%-- Make Panel --%>
                    
                                    <span runat="server" style="padding:8px; ;" >
                                        <asp:Label ID="label1" runat="server" Text="Make" Font-Size="Large" >          </asp:Label>    
                                        <asp:Listbox AutoPostback="true" ID="MakeList" runat="server" Width="166px" SelectionMode ="Multiple" DataTextField="MakeName" DataValueField="MakeID" OnSelectedIndexChanged="UpdateModels" DataSourceID="MakeSource">
                                        </asp:Listbox>
                                        <asp:SqlDataSource runat="server" ID="MakeSource" ConnectionString="<%$ ConnectionStrings:VCDBConnectionString %>"></asp:SqlDataSource>
                                   </span>
                                 



    I did it like this in my C# backend.

    //note this is triggered by a selected index changed on makelist.  
     public void UpdateModels(object sender, EventArgs e)
            {
    
                //build a string for a SQL query for the Models
                string baseQuery = "SELECT DISTINCT M.[ModelID], M.[ModelName] FROM Model M INNER JOIN BaseVehicle BV ON BV.ModelID = M.ModelID Where BV.MakeID= '";
                string newQuery = "";
    
                selectedMakes.Clear();
                //build a query into a list which will be compiled later into a single string
                List<string> queryBuilder = new List<string>();
                
                //add the base query
                queryBuilder.Add(baseQuery);
    
                //add the seleted items to items in the list
                    foreach (ListItem li in MakeList.Items)
                    {
                  
                        if (li.Selected)
                        {
                            queryBuilder.Add(li.Value);                 
                            queryBuilder.Add("' OR BV.MakeID = '");
                     
    
                            //build the list of selected makes for later use
                            selectedMakes.Add(li.Value);
                        }
                    }
                        try
                        {
                            //remove the last  ' AND BV.MakeID= '
                            queryBuilder.RemoveAt(queryBuilder.Count-1);
                            
                           //add back the ' and the orderby
                            queryBuilder.Add("'");
                            queryBuilder.Add(" ORDER BY [ModelName]");
    
                            //build the string
                            foreach(string s in queryBuilder){
                            
                                newQuery+= s;
                            
                            }
                      
    
                            //debug for visibilty 
                            TESTER.Text =newQuery;
    
                        }
                        catch (ArgumentNullException) { TESTER.Text = "Argument Null"; }
                        catch (IndexOutOfRangeException) { TESTER.Text = "Index out of range"; }
                        catch (UpdateException) { TESTER.Text = "Update Problems"; }
                        catch (Exception) { TESTER.Text = "Other Problems"; }
    
                        
                        ModelSource.SelectCommand = newQuery;
                        ModelList.DataTextField="ModelName";
                        ModelList.DataValueField = "ModelID";
    
    
              
                }



    Friday, June 28, 2013 2:26 PM