?
Solved

Using gridview to return query based on listbox selection

Posted on 2008-11-12
18
Medium Priority
?
869 Views
Last Modified: 2012-05-05
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
Comment
Question by:theclassic
  • 11
  • 7
18 Comments
 
LVL 6

Expert Comment

by:kdtresh
ID: 22941551
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
 

Author Comment

by:theclassic
ID: 22941660
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
 
LVL 6

Expert Comment

by:kdtresh
ID: 22942205
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:theclassic
ID: 22942439
It is a listbox, with multiple selections, sorry - thanks for your help...
0
 

Author Comment

by:theclassic
ID: 22942461
oh - and the listbox's are populated with data from a  sql table
0
 

Author Comment

by:theclassic
ID: 22942479
ANd with a button - I actually got as far as you have shown me using the automatic functuionality of VS
0
 
LVL 6

Expert Comment

by:kdtresh
ID: 22942557
0
 

Author Comment

by:theclassic
ID: 22942752
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
 

Author Comment

by:theclassic
ID: 22942873
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
 
LVL 6

Expert Comment

by:kdtresh
ID: 22943288
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
 

Author Comment

by:theclassic
ID: 22954587
Yeah...Imma need some more help on this one...it is burning me out
0
 

Author Comment

by:theclassic
ID: 22960175
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
 
LVL 6

Expert Comment

by:kdtresh
ID: 22960906
is your sqldatasource called "SearchResults" ?
0
 

Author Comment

by:theclassic
ID: 22961261
Yes
0
 

Author Comment

by:theclassic
ID: 22961280
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
 
LVL 6

Expert Comment

by:kdtresh
ID: 22963182
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
 

Author Comment

by:theclassic
ID: 22977330
Syntax?
0
 
LVL 6

Accepted Solution

by:
kdtresh earned 2000 total points
ID: 22977752
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question