ASP.NET Gridview with dropdownlist. select the value in the database

Question

I have gridview with dropdownlist. I

successfully added the dropdownlist values from sql server table1. I have

another table contains the dropdownlist selected values previously, how can I show that saved values in the dropdownlost indeviually.(dropdownlist1.selectedindex = what I have in the database)?

The question in short: I can save the value from the dropdownlist, but I don't know how to show that value again on page load?

Gridview code

<asp:GridView ID="Grid_View1" runat="server" Font-Size="Large" CellPadding="3" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" OnRowCommand="Subjects_RowCommand" OnRowDataBound="Classes_List_RowDataBound" AutoGenerateColumns="False" \>
\<AlternatingRowStyle BorderWidth="1px" /\>
\<Columns\>
\<asp:TemplateField HeaderText="Monday"\>
\<ItemTemplate\>  
\<asp:DropDownList ID="Monday" AppendDataBoundItems="true" runat="server" AutoPostBack="True" OnSelectedIndexChanged="Monday_SelectedIndexChanged"\>
\</asp:DropDownList\>  
\</ItemTemplate\>

                </asp:TemplateField>
                <asp:TemplateField HeaderText="Tuesday">
                                        <ItemTemplate>  
                        <asp:DropDownList ID="Tuesday" runat="server">  
                        </asp:DropDownList>  
                    </ItemTemplate> 
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Wednesday"></asp:TemplateField>
                <asp:TemplateField HeaderText="Thursday"></asp:TemplateField>
                <asp:TemplateField HeaderText="Friday"></asp:TemplateField>
            </Columns>
            <EditRowStyle BorderStyle="Solid" BorderWidth="1px" />
            <EmptyDataRowStyle BorderStyle="Solid" BorderWidth="1px" />
            <FooterStyle BorderStyle="Solid" />
            <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
            <PagerStyle BorderColor="Black" BorderStyle="Solid" BorderWidth="1px" />
            <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
            <SelectedRowStyle BorderStyle="Solid" />
            <SortedAscendingCellStyle BackColor="#F4F4FD" />
            <SortedAscendingHeaderStyle BackColor="#5A4C9D" />
            <SortedDescendingCellStyle BackColor="#D8D8F0" />
            <SortedDescendingHeaderStyle BackColor="#3E3277" />
        </asp:GridView>

Select the information

protected void Page_Load(object sender, EventArgs e)
{
Sql_Connection.Open();
SqlCommand Select_Subject_R = new SqlCommand("SELECT SID,Period,Time,Monday,Tuesday,Wednesday,Thursday,Friday  FROM \[OrisonSystemRAWAFED\].\[dbo\].\[CCE_Schedule\] where Academicyear =(select AcademicYear from School_AcademicYear where Status='Current') and Class='10' and Division='A'", Sql_Connection);
SqlDataAdapter sda = new SqlDataAdapter(Select_Subject_R);
DataTable dt = new DataTable();
sda.Fill(dt);
Sql_Connection.Close();
Grid_View1.DataSource = dt;
Grid_View1.DataBind();
}

Select the Subjects from Table1

protected void Classes_List_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string f = e.Row.Cells\[0\].Text;
SqlCommand cmd = new SqlCommand("SELECT TOP (100) PERCENT dbo.CCE_SubjectGroup.SubjectCode, dbo.CCE_SubjectMaster.SubjectName FROM dbo.CCE_SubjectGroup INNER JOIN dbo.CCE_SubjectMaster ON dbo.CCE_SubjectGroup.SubjectCode = dbo.CCE_SubjectMaster.SubjectCode WHERE (dbo.CCE_SubjectGroup.AcademicYear = (select AcademicYear from School_AcademicYear where Status='Current')) AND (dbo.CCE_SubjectGroup.Class = '10') GROUP BY dbo.CCE_SubjectGroup.SubjectCode, dbo.CCE_SubjectMaster.SubjectName ORDER BY dbo.CCE_SubjectGroup.SubjectCode", Sql_Connection);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);

            if (dt.Rows.Count != 0)
            {
    
                DropDownList DropDownList1 = (DropDownList)e.Row.FindControl("Monday");
    
                DropDownList1.DataSource = dt;
                DropDownList1.DataTextField = "SubjectName";
                DropDownList1.DataValueField = "SubjectCode";
                DropDownList1.DataBind();
                DropDownList1.Items.Insert(0, new ListItem("", "0"));
    
    
                DropDownList1 = (DropDownList)e.Row.FindControl("Tuesday");
                DropDownList1.DataSource = dt;
                DropDownList1.DataTextField = "SubjectName";
                DropDownList1.DataValueField = "SubjectCode";
                DropDownList1.DataBind();
                DropDownList1.Items.Insert(0, new ListItem("", "0"));
    
    
                DropDownList ddl = new DropDownList();
                foreach (GridViewRow row in Grid_View1.Rows)
                {
                    ddl = (DropDownList)row.FindControl("Monday");
                    ddl.SelectedIndex = 2;
                }
    
    
            }
    
        }

When I change the Selectedindex manually, it change it for all rows

DropDownList ddl = new DropDownList();
foreach (GridViewRow row in Grid_View1.Rows)
{
ddl = (DropDownList)row.FindControl("Monday");
ddl.SelectedIndex = 2;
}

Answer 1

Ok, so, we have 2 things to do:

For each row of the grid, we have to fill each combo box with the course data. This part you look to have correct.

Then we ALSO need to check the current row we are working on, and then SET the combo box. But we ALSO need to check if the value is not null. And if the value is null, then we don't set nor change the combo box, and leave it to default to your default value of "".

And you do NOT need some row loop in your item data bound event. That event fires for ONE data row - ONLY one row, and thus the code for data bound event is to be FOR THE ONE row - no looping should occur here.

Now, since the course list is needed over and over, then we can actually load up the course list ONE TIME and THEN bind the grid. Since each combo box data source is the same course list, then we only need to load that list ONE time. We still however have to set (load) each combo box with that course list, and then STILL have to check the database value, and if we have a value, then we ALSO must set the current value of the combo box. As noted, your filling of combo box looks good, but is missing the setting of the current value from the database. And as noted, we also have to check and NOT set the combo if the current value is null in the database, and display the "" (default value) you setup for the combo box when no selecting has been made.

After all above is done, you also should have a single save button (right below the GV), and you click that save button then all of the changes are sent back (saved) to the database.

So, let’s first address the combo filling, and setting of the combo box to the current value in the database (assuming a value exists (non null) in the db).

Also, you don't mention how many rows of data we are to have here - (looks like 8 rows), and that again is fine.

On the other hand, if you normalized your data, you should ONLY need one row for the week, and then a child table of courses selected for each day is a better and more correct table design (more normalized). And such a design would save you having to generate the 8 rows of data.

For now, you have what you have, and lets address the "setting" of the combo box with your existing design.

Ok, first, as noted, we really don't have to pull + re-load the course choices over and over. However, I suppose if different course selections were to be ONLY allowed on Monday, and then a different set on Tuesday, then we WOULD need to load up the course combo with a different data selection (in row data bind for the GV).

Since you did NOT note and mention that course selections are different for Mon and Tues, then I will assume that your current code shows the correct assumption here (that mon and tues have the same allowed course list to choose from).

So, let’s first introduce the idea of having to only pull + load our data for the combo box, and then use that one data set for each combo over and over.

And you also have a BIG bug issue in your on-load event.

Remember, any button click, any post-back will ALWAYS fire the page on load event EACH AND EVERY time.

That means a simple button click for any actions on the page will force your grid re-loading code to run again (and that means any changes to the gv and our combo boxes will be lost). This issue is not limited to a gv.

In fact a simple combo box, or even listbox or anything on a page? If we make a change or selection, and then hit some “done” button, then our page load code will run again first (and then button code). As a result, we will lose all changes. (because our code runs again to re-load and re-bind the combo box.

So, new rule, and NEVER break this rule again:

Your on-page load code can ONLY EVER run one time to load up controls. If we re-load those data bound controls on each post-back (page load), then such code running again will destroy our data choices in those drop downs, grids, listbox whatever. And why run some code each time – it slows down the page load process anyway.

As a FYI? We tend to not notice this issue when we drop + place + use a sqldatasource in the page markup. However, as your example shows, once we get bit more experience, then we start tending away from using those automatic built sql data sources on the page – they are handy, but you ALSO tend to lose control and flexibility, so we then do what everyone (and you) are doing – you build your own data sets in code behind.

Regardless?

So, let’s fix this huge “on-load” issue. So, the above NEVER break rule is beyond important to remember. I find about 2-5 posts per week on Stack Overflow that are the result of failing to follow the above very simple rule!!! (Only run the data bound controls loading up code ONE TIME!!!).

And lets build a helper routine, since we have to load data in a few places, and lets save our keyboards by not having to write that simple code over and over.

So, our page load event. We as noted have to fix this, and follow the above rule.

So, that becomes this:

    DataTable rstCourses = new DataTable();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        // first load up our courses data (for combo)

        string strSQL = 
            @"SELECT dbo.CCE_SubjectGroup.SubjectCode, dbo.CCE_SubjectMaster.SubjectName 
              FROM dbo.CCE_SubjectGroup 
              INNER JOIN dbo.CCE_SubjectMaster ON  
                    dbo.CCE_SubjectGroup.SubjectCode = dbo.CCE_SubjectMaster.SubjectCode 
              WHERE (dbo.CCE_SubjectGroup.AcademicYear = 
                     (select AcademicYear from School_AcademicYear where Status='Current')) 
              AND (dbo.CCE_SubjectGroup.Class = '10') 
              GROUP BY dbo.CCE_SubjectGroup.SubjectCode, dbo.CCE_SubjectMaster.SubjectName
              ORDER BY dbo.CCE_SubjectGroup.SubjectCode";

        rstCourses = MyRst(strSQL);

        // now load grid

        strSQL = @"SELECT SID, Period, Time, Monday, Tuesday, Wednesday, Thursday, Friday  
                   FROM [OrisonSystemRAWAFED].[dbo].[CCE_Schedule] 
                   where Academicyear = 
                       (select AcademicYear from School_AcademicYear where Status = 'Current')
                   AND 
                      Class = '10' and Division = 'A'";

        Grid_View1.DataSource = MyRst(strSQL);
        Grid_View1.DataBind();
    }

And really, for such long sql ? create a view - that is just WAY WAY too much in-line sql in code. (and hint: using @"SOME string goes here - you don't need to escape characters when you do this).

Ok so, above will load the gv, trigger the data bound event.

So, we need to load combo for each row, and THEN set the combo to the current database value.

That code becomes this:

    protected void Classes_List_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            // get data base ONE row bind
            DataRowView gRowData = (DataRowView)e.Row.DataItem;

            DropDownList DropDownList1 = (DropDownList)e.Row.FindControl("Monday");
            DropDownList1.DataSource = rstCourses;
            DropDownList1.DataTextField = "SubjectName";
            DropDownList1.DataValueField = "SubjectCode";
            DropDownList1.DataBind();
            DropDownList1.Items.Insert(0, new ListItem("", "0"));
            // set combo drop to row value
            if (gRowData["Monday"] != null)
                DropDownList1.Text = gRowData["Monday"].ToString();


            DropDownList1 = (DropDownList)e.Row.FindControl("Tuesday");
            DropDownList1.DataSource = rstCourses;
            DropDownList1.DataTextField = "SubjectName";
            DropDownList1.DataValueField = "SubjectCode";
            DropDownList1.DataBind();
            DropDownList1.Items.Insert(0, new ListItem("", "0"));
            if (gRowData["Tuesday"] != null)
                DropDownList1.Text = gRowData["Tuesday"].ToString();

        }

And our little helper routine - (no need to wear out a keyboard typing the same code over and over to just fill and return a datatable for some given sql).

    public DataTable MyRst(string strSQL)
    {
        // return data table based on sql
        DataTable rstData = new DataTable();
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                cmdSQL.Connection.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }

So, note close - on gv data bind, we can get the row that is active during the current binding with this:

            // get data base ONE row bind
            DataRowView gRowData = (DataRowView)e.Row.DataItem;

keep in mind that e.Row.DataItem IS ONLY ACTIVE and available during the binding process. Once data binding is done, then e.Row.DataItem is null and void.