Filling a drop down list in a grid view with a table in Sql

Question

I am trying to add a combo box to a field within a grid view, however, I do not know how to fill it with data from a table within Sql. I know how to do it normally but I have ben looking online for answer on how to do one stored within a grid view. I have been trying many methods and this is what I have only managed to come up with but it is not working.

        String Sql = @" select Staffid, staffforename, staffsurname, CONCAT(staffforename, ' ', staffSurname) as FullName from Staff where SupportTeamID = 'TEL'";
        SqlConnection conn = new SqlConnection(Properties.Resources.cString);
        SqlDataAdapter DA = new SqlDataAdapter(Sql, Properties.Resources.cString);
        DataSet DS = new DataSet();
        DA.Fill(DS, "STaff");
        DataTable DT = DS.Tables["Staff"];

        gv_Quals.FindControl("cmbStaffID").DataValueField = "StaffID";
        gv_Quals.FindControl("cmbStaffID").DataTextField = "FullName";
        gv_Quals.FindControl("cmbStaffID").DataSource = DT;
        gv_Quals.FindControl("cmbStaffID").DataBind();

Here is the markup code of my drop down list stored within the grid view

   <asp:TemplateField HeaderText="staffID">

                        <ItemTemplate>
                            <asp:DropDownList ID="cmbStaffID" runat="server"></asp:DropDownList>
                        </ItemTemplate>

Here is the design of my grid view Is there a specifc way of doing it or am I missing something? Thanks in advance! UPDATE: gv_Quals is the grid view!

Have you added the code in ```GridView1_RowDataBound``` function? — Sandesh Gupta, Apr 30 '18 at 09:42

score 0 · Answer 1 · answered Apr 30 '18 at 09:45

You should be adding the binding code for different row. Hence, define a RowDataBound method for the same.

 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)  
    {  
        if (e.Row.RowType == DataControlRowType.DataRow)  
        {  
            String Sql = @" select Staffid, staffforename, staffsurname, CONCAT(staffforename, ' ', staffSurname) as FullName from Staff where SupportTeamID = 'TEL'";
            SqlConnection conn = new SqlConnection(Properties.Resources.cString);
            SqlDataAdapter DA = new SqlDataAdapter(Sql, Properties.Resources.cString);
            DataSet DS = new DataSet();
            DA.Fill(DS, "STaff");
            DataTable DT = DS.Tables["Staff"];
            gv_Quals.FindControl("cmbStaffID").DataValueField = "StaffID";
            gv_Quals.FindControl("cmbStaffID").DataTextField = "FullName";
            gv_Quals.FindControl("cmbStaffID").DataSource = DT;
            gv_Quals.FindControl("cmbStaffID").DataBind();
        }   

   }

score 0 · Answer 2 · answered Apr 30 '18 at 09:48

0

Add your code to GridViewID_RowDataBound event.

answered Apr 30 '18 at 09:48

Gagan Deep

1,508
9
13

Filling a drop down list in a grid view with a table in Sql

2 Answers2