databind gridview edititemtemplate dropdownlist with sql statement

Question

I have a gridview with the following code:

<asp:TemplateField HeaderText="Column1">
    <EditItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Bind("Column1") %>'></asp:Label>
    </EditItemTemplate>
    <ItemTemplate>
        <asp:DropDownList ID="DropDownList1" runat="server" Width="125px">
    </asp:DropDownList>
    </ItemTemplate>
</asp:TemplateField>

I want to bind a sql statement to populate the dropdownlist...

select Column1 from Table1

would I do this through the code behind? any information is appreciated? Also based on the selection the user makes with this dropdownlist, I want to populate the next column (column2) with the corresponding data... Need assistance with that too...

I am unfamiliar with template fields, I can work with a gridview binding in code behind and through the html but templatefields is like another language... I appreciate the help!!

Answer 1

There are a few parts to the answer to your question and I'm making a few assumptions (you're using Visual Studio as your IDE, you can use VB as your code-behind language):

I want to bind a sql statement to populate the dropdownlist...

You can do this either in code-behind or through the Visual Studio GUI. While you may use a boundfield for the dropdownlist, the templatefield ultimately gives you more flexibility. I'd read up on the templatefield (here), as it will be your friend using the Gridview for anything beyond basic data display. Using the GUI, selecting the dropdownlist should give you a small arrow to the upper-right that will allow you to create a data connection and datasource to bind to your dropdownlist.

Also based on the selection the user makes with this dropdownlist, I want to populate the next column (column2) with the corresponding data

This is a little more complex, as you will need to trigger PostBack (using AutoPostBack) on the Dropdownlist, handle the Dropdownlist's SelectedIndexChanged event, find the control to be updated in your second column, and update that control based on the selectedindex/item/value from your Dropdownlist. There are several ways to do this but here's the quickest I've found (using asp.net winforms). I'm using the SQL Adventureworks database, populating a dropdown in a templatefield in column 1 with the employeeID and using the selected employeeID to populate a label in column 2 with that employeeID's managerID.

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" 
    EnableModelValidation="True" AutoGenerateColumns="False" 
    DataKeyNames="EmployeeID">
    <Columns>
        <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" 
            InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
        <asp:TemplateField>
            <ItemTemplate>
                Select EmployeeID
                <asp:DropDownList ID="ddEmpID" runat="server" OnSelectedIndexChanged="ddEmpID_SelectedIndexChanged" 
                    DataSourceID="SqlDataSource1" DataTextField="EmployeeID" 
                    DataValueField="EmployeeID" AutoPostBack="True">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="labManagerID" runat="server" Text="ManagerID"></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="Data Source=MyServer\Instance;Initial Catalog=AdventureWorks;Integrated Security=True" 
    ProviderName="System.Data.SqlClient" 
    SelectCommand="SELECT TOP(10) EmployeeID, ManagerID FROM HumanResources.Employee">
</asp:SqlDataSource>

And the SelectedIndexChanged event from the code-behind:

Protected Sub ddEmpID_SelectedIndexChanged(sender As Object, e As EventArgs)
    Dim myDDList As DropDownList = CType(sender, DropDownList)
    Dim gvRow As GridViewRow = CType(myDDList.NamingContainer, GridViewRow)
    Dim myLabel As Label = CType(gvRow.FindControl("labManagerID"), Label)

    ' Create your sql query here to populate a data object and assign values throughout your row
        Dim myConnection As SqlConnection = New SqlConnection("Data Source=MyServer\Instance;Initial Catalog=AdventureWorks;Integrated Security=True")
        Dim myCmd As SqlCommand = New SqlCommand("Select ManagerID FROM HumanResources.Employee WHERE EmployeeID='" + myDDList.SelectedValue + "'", myConnection)

        If myConnection.State = ConnectionState.Closed Then myConnection.Open()
        myLabel.Text = myCmd.ExecuteScalar
        If myConnection.State = ConnectionState.Open Then myConnection.Close()

End Sub

And since working with the Gridview is sometimes an exercise in self-flagellation, I'd suggest having some good walkthrough tutorials at hand:

-J