Filtering DataSet Results


List the FAQs of a Particular FAQ Category
Application Object | Arrays | ASP.NET | Cookies | Databases, Errors | Databases, General | Databases, Queries | Dates and Times | Email | FileSystemObject | Forms | Functions and Subroutines | Getting Started | Math Functions | Networking | Regular Expressions | Security | Session Object | Strings | System.IO Namespace Classes | The Nature of Things | Uploading | XML |

View FAQFAQ CategoryFAQ Question
View FAQApplication ObjectHow can I list all of the Application variables on a Web page?
View FAQApplication ObjectCan I create an application-level Dictionary object?
View FAQApplication ObjectHow can I use application-level variables to cache information?


Source Code
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
      BindData(7)
    End If
    
    AddFilterButtons()
  End Sub


  Sub AddFilterButtons()
    'Get a list of the categories
    '2. Create the command object, passing in the SQL string
    Dim strSQL as String 
    strSQL = "SELECT FAQCategoryID, Name AS CategoryName " & _
             "FROM tblFAQCategory " & _
             "ORDER BY CategoryName"

    'Set the datagrid's datasource to the datareader and databind
    Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
    Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
    
    myConnection.Open()
    Dim myDataReader as SqlDataReader = myCommand.ExecuteReader()
    
    'Loop through the results, creating a LinkButton for each row
    Dim filterButton as LinkButton
    While myDataReader.Read()
      'Add the LinkButton
      filterButton = New LinkButton()
      filterButton.Text = myDataReader("CategoryName")
      filterButton.CommandArgument = myDataReader("FAQCategoryID")
      AddHandler filterButton.Command, AddressOf Me.FilterData      
      
      phFilterLinkButtons.Controls.Add(filterButton)
      
      'Add some white space
      phFilterLinkButtons.Controls.Add(New LiteralControl(" | "))
    End While
    
    myDataReader.Close()
    myConnection.Close()    
  End Sub

  
  Sub FilterData(sender as Object, e as CommandEventArgs)
	BindData(e.CommandArgument)
  End Sub

	
  Sub BindData(FilterOnFAQCategoryID as Integer)
    '2. Create the command object, passing in the SQL string
    Dim strSQL as String 
    strSQL = "SELECT FAQID, F.FAQCategoryID, F.Description, FC.Name AS CategoryName " & _
             "FROM tblFAQ F INNER JOIN tblFAQCategory FC ON F.FAQCategoryID = FC.FAQCategoryID " & _
             "WHERE F.FAQCategoryID = @FAQCatID " & _
             "ORDER BY FAQID"

    'Set the datagrid's datasource to the datareader and databind
    Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
    Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) 
    
    Dim FAQCatIDParam as New SqlParameter("@FAQCatID", SqlDbType.Int, 4)
    FAQCatIDParam.Value = FilterOnFAQCategoryID
    myCommand.Parameters.Add(FAQCatIDParam)
    
    myConnection.Open()
    dgFilteredFAQs.DataSource = myCommand.ExecuteReader()
    dgFilteredFAQs.DataBind()
    myConnection.Close()
  End Sub
</script>

  <form runat="server">
    <p align="center">
    <b>List the FAQs of a Particular FAQ Category</b><br />
    <asp:PlaceHolder id="phFilterLinkButtons" runat="server" />
    </p>
    <asp:datagrid id="dgFilteredFAQs" runat="server"
		AutoGenerateColumns="False" CellPadding="5"
		HeaderStyle-HorizontalAlign="Center"
		HeaderStyle-BackColor="Red"
		HeaderStyle-ForeColor="White"
		HeaderStyle-Font-Bold="True"
		HeaderStyle-Font-Name="Verdana"
		AlternatingItemStyle-BackColor="#dddddd"
		ItemStyle-Font-Name="Verdana">
	
	  <Columns>
	    <asp:HyperLinkColumn DataNavigateUrlField="FAQID" ItemStyle-Width="10%"
				ItemStyle-HorizontalAlign="Center" HeaderText="View FAQ"
				Text="View FAQ" 
				DataNavigateUrlFormatString="http://aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID={0}" />
	    <asp:BoundColumn DataField="CategoryName" HeaderText="FAQ Category"
	            ItemStyle-Wrap="False" />
	    <asp:BoundColumn DataField="Description" HeaderText="FAQ Question" />	  
	  </Columns>	
	</asp:datagrid>
  </form>
	


[Return to the article]