locked
How to access the following SqlDataSource? RRS feed

  • Question

  • User1310320473 posted

    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) starting from the fourth cell up to the last cell. 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. I could be able to do that by doing the following:

    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;
    
                    //The following if-else statements are for checking the GroupID and give each group
                    //a specific color
                    if (group[0].ToString().Equals("1"))
                        headerCell.BgColor = "lightBlue";
                    else if (group[0].ToString().Equals("2"))
                        headerCell.BgColor = "lightYellow";
                    else if (group[0].ToString().Equals("3"))
                        headerCell.BgColor = "Orange";
    
                    //the header cells to the header
                    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);
    
            }
        }


    There is a different number of courses in each group but the coloring should be started from the fourth cell up to the last cell. I did many tries in the following part of the above code but I failed and I don't know why. So how to do that?

    //for adding the headers to the table
                foreach (DataColumn column in dv.Table.Columns)
                {
                    HtmlTableCell headerCell = new HtmlTableCell("th");
                    headerCell.InnerText = column.Caption;
    
                    //The following if-else statements are for checking the GroupID and give each group
                    //a specific color
                    if (group[0].ToString().Equals("1"))
                        headerCell.BgColor = "lightBlue";
                    else if (group[0].ToString().Equals("2"))
                        headerCell.BgColor = "lightYellow";
                    else if (group[0].ToString().Equals("3"))
                        headerCell.BgColor = "Orange";
    
                    //the header cells to the header
                    header.Cells.Add(headerCell);
                }
                table.Rows.Add(header);



    Wednesday, February 15, 2012 5:24 AM

Answers

  • User-2107833406 posted

    I had a problem like yours and I solved it by not using sqldataSource. Instead I retrieved the data via the code behind by sqlConnection...

    It is better that way - especially when you don't need to handle insert, update or delete...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 16, 2012 7:01 AM