How to access the following SqlDataSource?

Question

I developed a training matrix for my company that shows all employees with all training courses provided in the company. Now, I have to develop it in such a way to be easy to edit and update by the admin. I did everything correct except one thing which is giving each group of courses a specific color (since I have 3 types of courses). FYI, I have two SqlDataSources for developing this matrix:

SqlDataSource1 is for retrieving the GroupID:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                SelectCommand="SELECT [ID] FROM [groups]"></asp:SqlDataSource>

And the SqlDataSource2 will take the GroupID from the SqlDataSource1 and use it to generate the matrix:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                                            ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
                                            SelectCommandType="StoredProcedure" SelectCommand="kbiReport" FilterExpression="[Division] like '{0}%'">

                            <SelectParameters>
                                <asp:Parameter  Name="GroupID"/>
                            </SelectParameters>

                            <FilterParameters>
                                <asp:ControlParameter ControlID="ddlDivision" Name="DivisionName" 
                                                         PropertyName="SelectedValue" Type="String" />
                            </FilterParameters>

            </asp:SqlDataSource>

Now, since I am using HTMLTable, I need to access the SqlDataSource1 to do some logic like: if the GroupID = 1, then give this group Blue color and so on. But I don't know how to do that. So could you please help me with this issue?

My code-behind in C#:

protected void Page_Load(object sender, EventArgs e)
    {

        DataView dv2 = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
        foreach (DataRowView group in dv2)
        {
            SqlDataSource2.SelectParameters[0].DefaultValue = group[0].ToString();
            //create a new HtmlTable object
            HtmlTable table = new HtmlTable();

            DataView dv = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
            int columns = dv.Table.Columns.Count;
            int rows = dv.Count;

            //table's formating-related properties
            table.Border = 2;
            table.CellPadding = 3;
            table.CellSpacing = 3;
            table.Width = "900px";

            //to get the css style
            table.Attributes["class"] = "uGrid";

            //create a new HtmlTableRow and HtmlTableCell objects
            HtmlTableRow row;
            HtmlTableRow header = new HtmlTableRow();
            HtmlTableCell cell;


            //for adding the headers to the table
            foreach (DataColumn column in dv.Table.Columns)
            {
                HtmlTableCell headerCell = new HtmlTableCell("th");
                headerCell.InnerText = column.Caption;
                header.Cells.Add(headerCell);
            }
            table.Rows.Add(header);

            //loop for adding rows to the table
            foreach (DataRowView datarow in dv)
            {
                row = new HtmlTableRow();
                //row.BgColor = "yellow";


                //loop for adding cells
                for (int j = 0; j < columns; j++)
                {
                    cell = new HtmlTableCell();
                    if (j < 4)
                    {
                        cell.InnerText = datarow[j].ToString();
                    }
                    else
                    {

                        CheckBox checkbox = new CheckBox();

                        int checkBoxColumns = dv.Table.Columns.Count - 5;
                        string fieldvalue = datarow[j].ToString();
                        string yes = fieldvalue.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries)[1];
                        string courseid = fieldvalue.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries)[0];
                        checkbox.ID = row.Cells[3].InnerText + "," + courseid.Trim();
                        checkbox.Checked = yes.Equals("Yes");
                        cell.Controls.Add(checkbox);

                    }

                    //add the cell to the current row
                    row.Cells.Add(cell);
                }

                //add the row to the table
                table.Rows.Add(row);
            }

            //add the table to the page
            PlaceHolder1.Controls.Add(table);

        }
    }

I tried to do it by doing the following but I failed:

//for adding the headers to the table
            foreach (DataColumn column in dv.Table.Columns)
            {
                HtmlTableCell headerCell = new HtmlTableCell("th");
                headerCell.InnerText = column.Caption;
                if (group[0].Equals(1)){
                    headerCell.BgColor = "Blue";
                else if (group[0].Equals(2))
                    headerCell.BgColor = "Yellow";
                header.Cells.Add(headerCell);
            }
            table.Rows.Add(header);

EDIT:

By the way, is it possible to determine which cells will be colored with Blue? For example, In Group#1 which has 8 courses, I want the coloring to start from the fourth cell up to the last one. Whereas, Group#2 which has 10 courses, also, I want the coloring to be start from the fourth cell up to the last one. Could you please help me with this issue?

Andrii Kalytiiuk · Accepted Answer · 2012-02-15 12:50:07Z

1

Please tell, did you make sure that first DataRowViews item from your DataView correspond to GroupId in your query? And have you tried applying ToString() method to value that you retrieve from first item of DataRowView in the last code snippet, like in following:

if (group[0].ToString().Equals("1"))
    headerCell.BgColor = "Blue";

Also, to change color of your rows in table - you need to change color of fields with data - or even better the whole rows, instead of changing color of headers.

Edit:

To change table's data field background color you can assign BgColor property of HtmlTableCell instances while populating table - like in following:

        if (j < 4)
        {
            cell.InnerText = datarow[j].ToString();
        }
        else
        {
            CheckBox checkbox = new CheckBox();

            int checkBoxColumns = dv.Table.Columns.Count - 5;
            string fieldvalue = datarow[j].ToString();
            string yes = fieldvalue.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries)[1];
            string courseid = fieldvalue.Split(new string[] { ", " }, StringSplitOptions.RemoveEmptyEntries)[0];
            checkbox.ID = row.Cells[3].InnerText + "," + courseid.Trim();
            checkbox.Checked = yes.Equals("Yes");
            cell.Controls.Add(checkbox);

            // set field background color  
            if (group[0].Equals(1)){
                cell.BgColor = "Blue";
            else if (group[0].Equals(2))
                cell.BgColor = "Yellow";
            }

Edit 2:

To set color of header cells I would recommend to replase usage of Equals method with equality operator (starting from column with certain index):

        //for adding the headers to the table

        int counter = 0;
        foreach (DataColumn column in dv.Table.Columns)
        {
            HtmlTableCell headerCell = new HtmlTableCell("th");
            headerCell.InnerText = column.Caption;

            if (++counter >= 5)
            {
                if (int.Parse(group[0]) == 1){
                    headerCell.BgColor = "Blue";
                else if (int.Parse(group[0]) == 2)
                    headerCell.BgColor = "Yellow";
                header.Cells.Add(headerCell);
            }
        }
        table.Rows.Add(header);

edited Feb 15, 2012 at 12:50

answered Feb 15, 2012 at 6:22

Andrii Kalytiiuk

1,49714 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ali Ahmed Over a year ago

Thank you very much. I really appreciate your help. By the way, is it possible to determine which cells will be colored with Blue? For example, In Group#1 which has 8 courses, I want the coloring to start from the fourth cell up to the last one. Whereas, Group#2 which has 10 courses, also, I want the coloring to be start from the fourth cell up to the last one. Could you please help me with this issue?

Andrii Kalytiiuk Over a year ago

@AliAhmed It is possible to change color of a table field with BgColor property of HtmlTableCell class. You can assign it in else block of your if operator in code snippet where you are looping through DataView. Near comment //loop for adding cells - in your thrird code snippet - where HtmlTable is created.

Ali Ahmed Over a year ago

Thanks but I am talking about the Header Cells not the Rows cells. Could you please provide me with the code snippet?

Andrii Kalytiiuk Over a year ago

@AliAhmed Your code seems to be ok for setting header color - you need to make sure that: 1. your group number is trigerred properly - e.g. is extracted from DataRowView and correctly compared to value that you supply - to be sure that color gets assigned to header fields and 2. colour takes effect in Html markup of the table.

Andrii Kalytiiuk Over a year ago

@AliAhmed The simpliest way is to define int counter variable with value 0 before start of the loop and then: 1. increase it in each loop iteration and 2. set cell color depending on value of counter variable - see updated answer.

|

Collectives™ on Stack Overflow

How to access the following SqlDataSource?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related