• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 896
  • Last Modified:

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>
0
theclassic
Asked:
theclassic
  • 11
  • 7
1 Solution
 
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.
0
 
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
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
theclassicAuthor Commented:
It is a listbox, with multiple selections, sorry - thanks for your help...
0
 
theclassicAuthor Commented:
oh - and the listbox's are populated with data from a  sql table
0
 
theclassicAuthor Commented:
ANd with a button - I actually got as far as you have shown me using the automatic functuionality of VS
0
 
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>
0
 
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
0
 
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.
0
 
theclassicAuthor Commented:
Yeah...Imma need some more help on this one...it is burning me out
0
 
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
0
 
kdtreshCommented:
is your sqldatasource called "SearchResults" ?
0
 
theclassicAuthor Commented:
Yes
0
 
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
0
 
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.
0
 
theclassicAuthor Commented:
Syntax?
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now