How do I populate a listview based on 2 boolean table columns?

Question

Hi,

I have a database table containing scripts for plays/dramas. The scripts can be categorized under 'Theatre and Education', 'Plays for Young People' or both (by using bools to determine whether they belong to said categories);

• scriptID: int
• theatreAndEducation:bit
• playsForYoungPeople: bit
• scriptTitle: varchar
• scriptSample:text
• thumbImageUrl: varchar

In the main menu I have a drop down list which contains the following links;

• a runat="server" href="~/WebForm/scriptList">theatre & education
• a runat="server" href="~/WebForms/scriptList">plays 4 young people

When the user clicks one of these links, they are taken to a page which contains a listview. I would like this listview to be populated with the records that correspond to the link that was clicked.

I'm guessing that a querystring is needed but I can't think an appropriate one that can be used.

Below is the listview in its current state. Any ideas on a query string and how the sql datasource should be configured to display the appropriate script records based on the link which is clicked?

<asp:ListView ID="scriptsListView" runat="server" DataSourceID="SqlDataSource1">
    <LayoutTemplate>
        <table>
            <tr>
                <td>title</td>
                <td>summary</td>
                <td>image</td>
            </tr>
            <tr>
                <asp:PlaceHolder ID="itemPlaceholder" runat="server">
                </asp:PlaceHolder>
            </tr>
        </table>
    </LayoutTemplate>

    <ItemTemplate>
        <tr>
            <td><%# Eval ("scriptTitle") %></td>
            <td><%# Eval ("scriptSample") %></td>
            <td><asp:Image ID="image" runat="server" 
                     ImageUrl='<%# "~/Images/Scripts/" + Eval("thumbImageUrl") %>' /></td>
        </tr>
    </ItemTemplate>
</asp:ListView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
    SelectCommand="">
    <SelectParameters>
    </SelectParameters>
</asp:SqlDataSource>

Answer 1

You could pass a query string parameter that differentiates between the two views. Also, in the code behind, on the SqlDataSource's Select event , you can swap out the select command.

Answer 2

set the select statement for the sqldatasource on page_load

a runat="server" href="~/WebForm/scriptList?type=theatre">theatre & education

a runat="server" href="~/WebForms/scriptList?type=plays">plays 4 young people

protected void Page_Load(object sender, EventArgs e)

if (Page.IsPostBack == false) { string s;

if (Request.QueryString["type"]=="theatre")
    {
      s="select * from scripts where theatreAndEducation = 1";
    }
else
    if (Request.QueryString["type"]=="plays")
    {
      s = "select * scripts where playsForYoungPeople = 1";
    }
    SqlDataSource1.SelectCommand = s;

} }