Using dropdownlist selected item to search gridview?

I've got a gridview that populates from four tables, and the select command (shown below) is fully functional. I'd like to be able to search the gridview, and display only those rows whose "Status" column value is equivalent to the selected item on a dropdown list. I believe I have the select statement right for this (also listed below), but I only want the gridview to display searched results on button click. How do I make this select statement conditional? In other words, how do I make it so that the Search select statement is only used when the user clicks the search button?

regular select statement:

SelectCommand="SELECT Customer.SubId, Customer.CustName, Customer.CustCity, Customer.CustState, Broker.BroName, Broker.BroState, Broker.EntityType, Submission.Coverage, Status.Status FROM Submission INNER JOIN Broker ON Broker.SubId = Submission.SubmissionId INNER JOIN Customer ON Customer.SubId = Submission.SubmissionId INNER JOIN Status ON Status.StatusId = Submission.StatusId"

my search select statement:

`SelectCommand="SELECT Customer.SubId, Customer.CustName, Customer.CustCity, Customer.CustState, Broker.BroName, Broker.BroState, 

Broker.EntityType, Submission.Coverage, Status.Status FROM Submission WHERE Status = '" + Ddl.SelectedItem.Text + "' INNER JOIN Broker ON Broker.SubId = Submission.SubmissionId 

INNER JOIN Customer ON Customer.SubId = Submission.SubmissionId INNER JOIN Status ON Status.StatusId = Submission.StatusId">`

Full gridview/droplist code:

    <asp:DropDownList ID="DropDownList1" runat="server" 
            DataSourceID="SqlDataSource2" DataTextField="Status" DataValueField="Status">

        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
            SelectCommand="SELECT [Status] FROM [Status]"></asp:SqlDataSource>
        &nbsp;<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        &nbsp;
        <asp:Button ID="Button1" runat="server" Text="Search" />
</asp:Panel>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" 
        EmptyDataText="There are no data records to display." AllowPaging="True" 
        BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" 
        CellPadding="3" GridLines="Vertical" HorizontalAlign="Center" 
        AllowSorting="True" >
        <AlternatingRowStyle BackColor="#DCDCDC" />

        <Columns>

            <asp:BoundField DataField="SubId" HeaderText="Submission Id" 
                SortExpression="SubId" >
            </asp:BoundField>
            <asp:BoundField DataField="CustName" HeaderText="Customer" 
                SortExpression="CustName" />
            <asp:BoundField DataField="CustCity" HeaderText="Customer City" 
                SortExpression="CustCity" />
            <asp:BoundField DataField="CustState" HeaderText="Customer State" 
                SortExpression="CustState" />
            <asp:BoundField DataField="BroName" HeaderText="Broker" 
                SortExpression="BroName" />
            <asp:BoundField DataField="BroState" HeaderText="Broker State" 
                SortExpression="BroState" />
            <asp:BoundField DataField="EntityType" HeaderText="Entity Type" 
                SortExpression="EntityType" />
            <asp:BoundField DataField="Coverage" 
                HeaderText="Coverage" SortExpression="Coverage" />
            <asp:BoundField DataField="Status" HeaderText="Status" 
                SortExpression="Status" />

            <asp:HyperLinkField DataNavigateUrlFields="SubId" 
                DataNavigateUrlFormatString="View.aspx?SubId={0}" Text="View" />
            <asp:HyperLinkField DataNavigateUrlFields="SubId" 
                DataNavigateUrlFormatString="ViewEdit.aspx?SubId={0}" HeaderText="Edit" 
                Text="Edit" />

        </Columns>
        <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
        <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
        <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
        <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#F1F1F1" />
        <SortedAscendingHeaderStyle BackColor="#0000A9" />
        <SortedDescendingCellStyle BackColor="#CAC9C9" />
        <SortedDescendingHeaderStyle BackColor="#000065" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
        ProviderName="<%$ ConnectionStrings:ProductInstanceString.ProviderName %>" 



        SelectCommand="SELECT Customer.SubId, Customer.CustName, Customer.CustCity, Customer.CustState, Broker.BroName, Broker.BroState, Broker.EntityType, Submiss开发者_如何学JAVAion.Coverage, Status.Status FROM Submission INNER JOIN Broker ON Broker.SubId = Submission.SubmissionId INNER JOIN Customer ON Customer.SubId = Submission.SubmissionId INNER JOIN Status ON Status.StatusId = Submission.StatusId">

    </asp:SqlDataSource>

First off, you definitely want to use parameters with your SQL. The example you have posted would be pretty vulnerable to a SQL injection attack.

Secondly, you can achieve doing one type of query or the other by using a stored procedure (which also has plenty of other benefits). In that procedure you can check if the parameter for status which you will pass in is null or not. If it's null use the regular, if it's not use the search statement.

In your DB, you can create a stored procedure that looks something like this (it could be more elegant but this is a simple example):

CREATE PROCEDURE [dbo].[GetStuffByStatus]

@status varchar(255) = null

AS 
BEGIN

IF @status IS NOT NULL
BEGIN

    SELECT Customer.SubId, Customer.CustName, Customer.CustCity, Customer.CustState, Broker.BroName, Broker.BroState, 

    Broker.EntityType, Submission.Coverage, Status.Status 

    FROM Submission 

    WHERE Status = @status

    INNER JOIN Broker ON Broker.SubId = Submission.SubmissionId 

    INNER JOIN Customer ON Customer.SubId = Submission.SubmissionId INNER JOIN Status ON Status.StatusId = Submission.StatusId

END
ELSE
    SELECT Customer.SubId, Customer.CustName, Customer.CustCity, Customer.CustState, Broker.BroName, Broker.BroState, Broker.EntityType, Submission.Coverage, Status.Status 

    FROM Submission 

    INNER JOIN Broker ON Broker.SubId = Submission.SubmissionId 
    INNER JOIN Customer ON Customer.SubId = Submission.SubmissionId INNER JOIN Status ON Status.StatusId = Submission.StatusId

END

Then you can edit your SqlDataSource appropriately:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
        SelectCommand="GetStuffByStatus" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:ControlParameter Name="status" ControlID="DtopDownList1" PropertyName="SelectedValue" ConvertEmptyStringToNull="true" />
        </SelectParameters>
</asp:SqlDataSource>

Then if you don't want to filter by status, make sure nothing is passed with this parameter (DropDownList SelectedValue is empty). Add a default, empty, value to your DropDownList:

<asp:DropDownList ID="DtopDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="someidentifier" DataValueField="someidentifier" AppendDataBoundItems="true">
    <asp:ListItem Text="No filter" Value="" />
</asp:DropDownList>

I had a little trouble at first getting the SqlDataSource to handle the non filtering (empty) case at first and I had to add an OnSelecting handler to the SqlDataSource for the GridView. I added this property to the SqlDataSource:

OnSelecting="SqlDataSource2_Selecting"

And wrote a handler which checks for the empty case and sets the parameter to DBNull:

protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    if (DtopDownList1.SelectedValue == String.Empty)
    {
        ((SqlCommand)e.Command).Parameters["@status"].Value = DBNull.Value;
    }
}

I have tested this solution and it works as you described.

Of course, the best practice of doing this would be to write your own data layer that would handle this logic more appropriately, but that's probably outside of the scope of this question.

In the interest of showing what worked for me, here is my code. It functions properly. With respect to the answer from @ashelvey, while I haven't tested his method, he is right about parameterizing. The only reason I haven't is that I'm working on a training project and my handler specifically told me to avoid it for now. The site is not for deployment. Anyway, my original question was "how do I change the select command on button click?" Here is the answer:

protected void BtnStatusSearch_Click(object sender, EventArgs e)
{
    SqlDataSource1.SelectCommand = "SELECT Customer.SubId, Customer.CustName, Customer.CustCity, Customer.CustState, Broker.BroName, Broker.BroState, Broker.EntityType, Submission.Coverage, Status.Status FROM Submission INNER JOIN Broker ON Broker.SubId = Submission.SubmissionId INNER JOIN Customer ON Customer.SubId = Submission.SubmissionId INNER JOIN Status ON Status.StatusId = Submission.StatusId WHERE Status.Status = '" + DdlStatus.SelectedItem.Text + "'";
    SqlDataSource1.DataBind();
}