ASP.net DropDownList populates GridView

Question

I'm trying to set up the GridView to filter the Credentials that belong to a specific Employee. The dropdownlist provides the list of employees, once selected I want the gridview to only populate entries that belong to the specific employee.

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:DropDownList ID="DropDownListEmployee" runat="server"  
                      AutoPostBack="True" 
                      OnSelectedIndexChanged="SelectionHasChanged"
                      DataSourceID="SqlDataSource2" DataTextField="Fullname"
                      DataValueField="Employee_ID" AppendDataBoundItems="true"
                      Width="214px">
        <asp:ListItem>Select</asp:ListItem>
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                       ConnectionString="<%$ ConnectionStrings:DBConnectionString %>" 
                       SelectCommand="SELECT Employee.F_Name + ' ' + Employee.L_Name AS Fullname, Employee.Primary_Address, Employee.Primary_Phone, Employee.E_mail, Credentials.Degree, Credentials.Years_Experience, Credentials.Certifications, Credentials.Positions, Employee.Employee_ID FROM Employee INNER JOIN Credentials ON Employee.Employee_ID = Credentials.Employee_ID"></asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                  DataSourceID="SqlDataSource2" DataKeyNames="Employee_ID">
        <Columns> 
            <asp:BoundField DataField="Fullname" HeaderText="Fullname" 
                            ReadOnly="True" SortExpression="Fullname" />
            <asp:BoundField DataField="Primary_Address" HeaderText="Primary_Address" 
                            SortExpression="Primary_Address" />
            <asp:BoundField DataField="Primary_Phone" HeaderText="Primary_Phone" 
                            SortExpression="Primary_Phone" />
            <asp:BoundField DataField="E_mail" HeaderText="E_mail" 
                            SortExpression="E_mail" />
            <asp:BoundField DataField="Degree" HeaderText="Degree" 
                            SortExpression="Degree" />
            <asp:BoundField DataField="Years_Experience" HeaderText="Years_Experience" 
                            SortExpression="Years_Experience" />
            <asp:BoundField DataField="Certifications" HeaderText="Certifications" 
                            SortExpression="Certifications" />
            <asp:BoundField DataField="Positions" HeaderText="Positions" 
                            SortExpression="Positions" />
            <asp:BoundField DataField="Employee_ID" HeaderText="Employee_ID" 
                            InsertVisible="False" ReadOnly="True" 
                            SortExpression="Employee_ID" />
        </Columns>
    </asp:GridView>
</asp:Content>

Answer 1

You didn't say what happenes with your current code, but I'm betting that when you select an employee, the page reloads and you get a list of all the employees in your database, right?

If that's true, the reason is because the same SqlDataSource is bound to both your DropDownList and your GridView, and the Select command for the SqlDataSource retrieves all the employees - there is no WHERE criteria to select a desired employee.

I would use 2 SqlDataSources - one for the DropDownList, and one for the GridView. The second SqlDataSource would have a Select command to get the desired employee's information, based on the selection in the DropDownList.

You can modify your SqlDataSource2's SelectCommand to return only the fullname and EmployeeID fields, as you don't need the rest for your DropDownList:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:DropDownList ID="DropDownListEmployee" runat="server"  
                      AutoPostBack="True" 
                      OnSelectedIndexChanged="SelectionHasChanged"
                      DataSourceID="SqlDataSource2" DataTextField="Fullname"
                      DataValueField="Employee_ID" AppendDataBoundItems="true"
                      Width="214px">
        <asp:ListItem>Select</asp:ListItem>
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                       ConnectionString="<%$ ConnectionStrings:DBConnectionString %>" 
                       SelectCommand="SELECT F_Name + ' ' + L_Name AS Fullname, Employee_ID FROM Employee"></asp:SqlDataSource>

In your second SqlDataSource, you'll need to add a parameter (EmployeeID) to the SelectParameters collection as shown below, and update your SelectCommand to take the parameter.

    <asp:SqlDataSource ID="gvDataSource" runat="server"
                       ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
                       SelectCommand="SELECT Employee.F_Name + ' ' + Employee.L_Name AS Fullname, Employee.Primary_Address, Employee.Primary_Phone, Employee.E_mail, Credentials.Degree, Credentials.Years_Experience, Credentials.Certifications, Credentials.Positions, Employee.Employee_ID FROM Employee INNER JOIN Credentials ON Employee.Employee_ID = Credentials.Employee_ID WHERE Employee.EmployeeID = IsNull(@EmployeeID, EmployeeID)">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownListID" 
                 ConvertEmptyStringToNull="true" Name="EmployeeID"
                 PropertyName="SelectedValue" />
        </SelectParameters>

Then assign this SqlDataSource to the GridView instead of the first one.

Note that there is no validation by the SqlDataSource on the values submitted into the parameters - which can be a security threat.

SqlDataSource.Select Method

SqlDataSource.SelectParameters Property