Using gridview to return query based on listbox selection

I want to add a button that takes all the list values and querys the gridviews sqldatasource based on the user selection of list box items

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
       
       
        <asp:listbox id="ListBox1" runat="server" datasourceid="SqlDataSource1" datatextfield="searchtopic" datavaluefield="searchtopic"></asp:listbox><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:InternalConnection %>"
            selectcommand="SELECT [searchtopic] FROM [plaintalk].[search] WHERE [topicID] = '9' ORDER BY [ID]">
        </asp:sqldatasource>
       
       
       
       
        <asp:gridview id="GridView1" runat="server" autogeneratecolumns="False" datasourceid="InternalConnect">
            <columns>
                <asp:boundfield datafield="image" headertext="image" sortexpression="image" />
                <asp:boundfield datafield="expDate" headertext="expDate" sortexpression="expDate" />
                <asp:boundfield datafield="Photodim" headertext="Photodim" sortexpression="Photodim" />
                <asp:boundfield datafield="Photosize" headertext="Photosize" sortexpression="Photosize" />
                <asp:boundfield datafield="photoName" headertext="photoName" sortexpression="photoName" />
                <asp:boundfield datafield="Shoot" headertext="Shoot" sortexpression="Shoot" />
            </columns>
        </asp:gridview>
        <asp:sqldatasource id="InternalConnect" runat="server" connectionstring="<%$ ConnectionStrings:InternalConnection %>"
            selectcommand="SELECT [image],[expDate],[Photodim],[Photosize],[search],[photoName],[Shoot]FROM[Library]">
//the name of the column I want to test the listbox selections against is "[search]"......And I want it to happen with a button click!!!!!!!!!!!!
        </asp:sqldatasource>
     
               
    </div>
    </form>
</body>
</html>
theclassicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kdtreshCommented:
I've done it in C#, but I'll try to make it generic pseudocode. In the PageLoad function of the codebehind file:

if (ispostback) then sqldatasource.selectcommand = select blah from blah where somefield = listbox.selectedvalue

If you have a submit button, you can set the selectcommand in the submit button on_click function. Another way is to set the autopostback property of the listbox to true, then you don't even need a submit button, assuming you have the ispostback code in the pageload.
theclassicAuthor Commented:
I am a beginner and drowning.  I really need this spelled out but I cannot find a simple example anywhere - I can't believe it
kdtreshCommented:
I'll use a bit of my code. Again, it's in C#, but hopefully you can interpret it. The ASP should be similar. This is just a basic page with a gridview and two dropdownlists, one with a list of dates, and the other with a few numbers and an "all" for records per page.

I start with the data source declaration. Note that SelectParameters must be specified for you to be able to select using it and that the formfield must point to your dropdownlist:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:serverConnectionString %>"
            SelectCommand="SELECT * FROM [WORKERS] ORDER BY date DESC">
            <SelectParameters>
                <asp:FormParameter Name="date" DefaultValue="1/1/2007" FormField="dateDropDownList" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>


Then the actual dropdownlist, note the ID is the same as in the SelectParameters field and autopostback is true, which makes it postback whenever the selection is changed. I add the actual list items in the page_load function of the codebehind, but you can also make it databound:

<asp:DropDownList ID="dateDropDownList" runat="server" DataTextFormatString="{0:d}" AutoPostBack="True"></asp:DropDownList>


Now the gridview, note the DataSourceID is the sqldatasource from earlier:

       <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="65535"
            AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
            HorizontalAlign="Center" Width="900px" CellPadding="3" ShowFooter="true">


Now we move to the codebehind file. Assuming you're using Visual Studio, it should autocreate the file. The first function in the class is Page_Load. In that function, I add an if/else statement, which I'll crop a bit, since most of it won't really apply. On first load I populate the datedropdownlist with dates and then set the selectcommand. On postback, I check to see if the page size dropdown has been changed and set the select command and gridview pagesize accordingly:

        if (!Page.IsPostBack) // first load
        {
            SqlDataSource1.SelectCommand = "SELECT * FROM [workers] WHERE (date = @date)";
        }
        else
        {
            SqlDataSource1.SelectCommand = "SELECT * FROM [workers] WHERE (date = @date)";
            try
            {
                GridView1.PageSize = int.Parse(pageDropDownList.SelectedValue);
            }
            catch
            {
                GridView1.PageSize = 65535;
            }
        }


By making the dropdownlist autopostback=true, every time the user selects a different value, it will postback. In your codebehind page_load function, you need to specify the selectcommand for the sqldatasource. You may not need to differentiate between first load and subsequent postbacks. The page_load is automatically called every time the page postback is fired.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

theclassicAuthor Commented:
It is a listbox, with multiple selections, sorry - thanks for your help...
theclassicAuthor Commented:
oh - and the listbox's are populated with data from a  sql table
theclassicAuthor Commented:
ANd with a button - I actually got as far as you have shown me using the automatic functuionality of VS
theclassicAuthor Commented:
See - I knew you would say that - now, where the heck do I put it ( I have been looking at that,,,)  I marked where I put it - I do not know if I am on the right track...Im gonna award you the points anyway, but could you help me with where to put that?
 

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Copy of Copy of Copy of Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml" > 
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:listbox id="ListBox1" runat="server" datasourceid="SegmentandAge" datatextfield="searchtopic"
datavaluefield="searchtopic" autopostback="True">

</asp:listbox><asp:sqldatasource id="SegmentandAge" runat="server" connectionstring="<%$ ConnectionStrings:InternalConnection %>"
selectcommand="SELECT [searchtopic] from [plaintalk].[search] Where [topicID] = '9' ORDER by [ID]">
</asp:sqldatasource>
&nbsp;<br />
<br />
<br />
<asp:gridview id="GridView1" runat="server" ReadOnly="True" autogeneratecolumns="False" datasourceid="SqlDataSource2">
<columns>
<asp:boundfield datafield="image" headertext="image" sortexpression="image" />
<asp:boundfield datafield="expDate" headertext="expDate" sortexpression="expDate" />
<asp:boundfield datafield="Photodim" headertext="Photodim" sortexpression="Photodim" />
<asp:boundfield datafield="Photosize" headertext="Photosize" sortexpression="Photosize" />
<asp:boundfield datafield="photoName" headertext="photoName" sortexpression="photoName" />
<asp:boundfield datafield="Shoot" headertext="Shoot" sortexpression="Shoot" />
</columns>
</asp:gridview>
 
HERE IS WHERE I THOUGHT IT WENT _ BUT IT IS GIVING ME A SYNTAX ERROR
<asp:sqldatasource id="SqlDataSource2" runat="server" connectionstring="<%$ ConnectionStrings:InternalConnection %>"
selectcommand="<%For Each item As ListItem In ListBox1.Items
If item.Selected = True Then%>+(SELECT [image], [expDate], [Photodim], [Photosize], [photoName], [Shoot] FROM [plaintalk]. [Library] WHERE ([search] LIKE '%' + @search + '%')
+<% End If Next%>">
<selectparameters>
<asp:controlparameter controlid="ListBox1" name="search" propertyname="SelectedValue" />
</selectparameters>
</asp:sqldatasource>



</div>
</form>
</body>
</html>
theclassicAuthor Commented:
And dude - if you could show me how to do it on the click of a button INSTEAD of just when the user clicks on the list item - lets just say my first boy and girl will be named kdtresh
kdtreshCommented:
Here's how I would do it in pseudocode. Not a VB guy, so I'm not sure how the onclick function fires with VB. VS automatically creates the onclick function when you drag a button onto the design window and double-click on the button.

In the .aspx file:

<asp:Button ID="submitButton" runat="server" Text="Submit" onclick="submitButton_Click" />

In the codebehind file:

function submitButton_Click()
{
     String paramlist = "none";
     
     foreach (listbox.item)
     {
          if listbox.item.selected == true // item selected
          {
              if (paramlist == "none") // no parameters selected yet
                   paramlist = listbox.item.text;
              else
                   paramlist = paramlist + " OR " + listbox.item.text;
          }
          paramlist = paramlist + "none";  // can be any string that isn't one of the parameters
     }

     sqldatasource2.selectcommand = "select * from tableName where param = (" + paramlist + ")";
}

That should give you a button that when it is clicked, it checks all the items in the listbox and if they're selected, it adds it to the param string. Then it changes the selectcommand to "select * from tableName where param = (selected1 OR selected2 OR selected3 OR none). You may have to tweak it to make it (param = param1) OR (param = param2) OR ... which would require slight tweaking in the foreach loop.

Now for your aspx file, try changing your selectcommand for sqldatasource2 to something like "select * from tableName" to see if that eliminates your syntax error. You can refine the selectcommand in the codebehind for the button click, it should just be something simple in the initial declaration.

To make the list selection not autopostback, just set autopostback= false on the listbox.
theclassicAuthor Commented:
Yeah...Imma need some more help on this one...it is burning me out
theclassicAuthor Commented:
I got this, but it will only do a search on a button click once - whats the deeal?  IE - when I go to search a second tiome, no results are returened
 

Imports System.Data
Imports System.Web
 
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
SearchResults.SelectParameters.Clear()
Dim SelectCommand = "SELECT [image],[expDate],[Photodim],[Photosize],[photoName],[Shoot]FROM[plaintalk].[Library]"
Dim strWr As String = "WHERE [search] LIKE '%"
Dim i As Integer = 0
Do While i < lstSegment.Items.Count
If lstSegment.Items(i).Selected Then
strWr &= "" & lstSegment.Items(i).Value & ","
End If
i = i + 1
Loop
strWr = strWr.TrimEnd(",") & "%';"
SearchResults.SelectCommand = SelectCommand + strWr
 
End Sub

 

End Class
kdtreshCommented:
is your sqldatasource called "SearchResults" ?
theclassicAuthor Commented:
Yes
theclassicAuthor Commented:
ANd I got it to return everytime - now i just need to see how to loop through multiple listBOXES and each of there selections, and add them to the query...
 

Imports System.Data
Imports System.Web
 
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
SearchResults.SelectParameters.Clear()
GatherSelect()
 
End Sub
Sub GatherSelect()
Dim SelectCommand = "SELECT [image],[expDate],[Photodim],[Photosize],[photoName],[Shoot]FROM[plaintalk].[Library]"
Dim strWr As String = "WHERE [search] LIKE '%"
Dim i As Integer = 0
Do While i < lstSegment.Items.Count
If lstSegment.Items(i).Selected Then
strWr &= "" & lstSegment.Items(i).Value & ","
End If
i = i + 1
Loop

strWr = strWr.TrimEnd(",") & "%';"
SearchResults.SelectCommand = SelectCommand + strWr
End Sub


End Class
kdtreshCommented:
I would create a sub called GetParams that goes through all the listboxes and builds the strings. That way you can ignore the parameters that aren't specified. You can either build the selectcommand in GetParams or just have it make the strings for each listbox and build the query in your button_click sub.
theclassicAuthor Commented:
Syntax?
kdtreshCommented:
Here is a sample in VB

Sub GetSelection (Src As Object, Args As EventArgs)

  SelectedText.Text = ""
  SelectedValue.Text = ""

  Dim Item As ListItem
  For Each Item in MyListBox.Items
    If Item.Selected = True Then
      SelectedText.Text &= Item.Text & " "
      SelectedValue.Text &= Item.Value & " "
    End If
  Next

End Sub

from this site: http://www.maconstateit.net/tutorials/ASPNET1/ASPNET06/aspnet06-04.aspx

You should be able to have a loop for each listbox and build string1, string2, etc from each box. For example, if listBox1 is a list of dates, the built string1 could look like "(date = 11/10/2008) OR (date = 11/11/2008)". If no other boxes had anything selected, you could tack "WHERE " + string1 onto your base query of "SELECT* FROM tableName" to get "SELECT * FROM tableName WHERE ((date = 11/10/2008) OR (date = 11/11/2008))".

If you have multiple listboxes, you could tack them on in sequence. If SelectCommand is "SELECT * FROM tableName" you can do SelectCommand + " WHERE (" + string1 + ") AND (" + string2 + ")".

Info about VB string concatenation: http://msdn.microsoft.com/en-us/library/te2585xw(VS.80).aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.