mysql - 根据每行的repeater控件的值执行计数 C# ASP.NET

Question

抱歉，如果这听起来非常无用，但我对 ASP.NET 和 C# [编者注:和 SQL] 还很陌生。基本上，我有一个中继器，可以绑定(bind)数据库中约会表中的数据。我想查看各个运算符(operator)汇总的总工作岗位(即第 1 行 =“John Smith”10 个工作岗位，第 2 行 =“Jim Baker”16 个工作岗位等)。

我已经设法从数据库中进行计数，但它只返回总作业数(即 26 个作业)，并且该值显示在行中每个运算符旁边(即第 1 行 = 'John Smith' 26 个作业) ，第 2 行 =“Jim Baker”26 个工作等)。无论如何，我可以做得更好并让它发挥作用吗？

我将再次感谢任何帮助和道歉，我的编程技能非常基础。

ASPX:

<div class="container">
        <div class="col-lg-4">
            <asp:DropDownList ID="listWCSummary" runat="server" DataSourceID="SqlDataSource2" DataTextField="ConvertedWC" DataValueField="Id" Width="200" AutoPostBack="true" OnSelectedIndexChanged="listWCSummary_SelectedIndexChanged"></asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:GAUConnectionString1 %>" SelectCommand="select * from JobWeekTable"></asp:SqlDataSource>
        </div>
        <table class="table table-bordered table-condensed table-hover table-responsive">
            <thead>
                <tr>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblWeekNo" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay1" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay2" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay3" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay4" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay5" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay6" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>
                    <td class="auto-style1" style="background-color: #428BCA;">
                        <strong>
                            <asp:Label ID="lblDay7" runat="server" Text="Label" ForeColor="White"></asp:Label></strong></td>


                </tr>
            </thead>
            <tbody>
                <asp:Repeater ID="rptrSummaryView" runat="server" OnItemDataBound="rptrSummaryView_ItemDataBound">
                    <ItemTemplate>
                        <tr>
                            <td style="text-align: center;">
                                <div class="panel-heading">
                                    <asp:Label ID="lbOpName" runat="server" Text='<%# Eval("EngineerName") %>'></asp:Label>


                                </div>
                                <td>
                                    <div class="col-lg-4">
                                        <div class="row" style="background-color: forestgreen; text-align: center;">
                                            <asp:Label ID="Label1" runat="server" Text="AM" Font-Bold="True" ForeColor="White"></asp:Label>
                                        </div>
                                        <div class="row" style="text-align: center; color: red;">
                                            <asp:Label ID="lblCountAMDay1" runat="server" Text="Count" ForeColor="Red"></asp:Label>
                                        </div>
                                    </div>
                                    <div class="col-lg-4">
                                        <div class="row" style="background-color: green; text-align: center;">
                                            <asp:Label ID="Label2" runat="server" Text="PM" Font-Bold="True" ForeColor="White"></asp:Label>
                                        </div>
                                        <div class="row" style="text-align: center;">
                                            <asp:Label ID="lblCountPMDay1" runat="server" Text="Count" ForeColor="Red"></asp:Label>
                                        </div>
                                    </div>
                                    <div class="col-lg-4">
                                        <div class="row" style="background-color: darkgreen; text-align: center;">
                                            <asp:Label ID="Label4" runat="server" Text="EVE" Font-Bold="True" ForeColor="White"></asp:Label>
                                        </div>
                                        <div class="row" style="text-align: center;">
                                            <asp:Label ID="lblCountEVEDay1" runat="server" Text="Count" ForeColor="Red"></asp:Label>
                                        </div>
                                    </div>






                                </td>
                    </ItemTemplate>
                </asp:Repeater>
            </tbody>
        </table>

    </div>

C#

 protected void rptrSummaryView_ItemDataBound(object sender, RepeaterItemEventArgs e)
{

    if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
    {
        string cs = ConfigurationManager.ConnectionStrings["GAUConnectionString1"].ConnectionString;

        using (SqlConnection con = new SqlConnection(cs))
        {


            con.Open();
        Label lblAM1 = (Label)e.Item.FindControl("lblCountAMDay1");
        Label lblPM1 = (Label)e.Item.FindControl("lblCountPMDay1");
        Label lblEV1 = (Label)e.Item.FindControl("lblCountEVEDay1");
        string Day1 = "select Day1 from JobWeekTable where Id='" + listWCSummary.SelectedValue + "'";
        SqlCommand GetFName = new SqlCommand(Day1, con);
        string Firstday = GetFName.ExecuteScalar().ToString().Replace("00:00:00", "");

        }

        //Day1

        {

            Label lblAM1 = (Label)e.Item.FindControl("lblCountAMDay1");
            Label lblPM1 = (Label)e.Item.FindControl("lblCountPMDay1");
            Label lblEV1 = (Label)e.Item.FindControl("lblCountEVEDay1");



            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                string Day1 = "select Day1 from JobWeekTable where Id='" + listWCSummary.SelectedValue + "'";

                SqlCommand GetFName = new SqlCommand(Day1, con);
                string Firstday = GetFName.ExecuteScalar().ToString().Replace("00:00:00", "");

                SqlCommand cmdDay1AM = new SqlCommand("select EngineerTable.EngineerName, Count(AppointmentTable.CustomerID) As CountAM from EngineerTable inner join AppointmentTable on EngineerTable.OpId=AppointmentTable.OpId where TimebandId = '1' and Dateconverted = '" + Firstday + "' group by EngineerName, TimebandId", con);
            using (SqlDataReader rd = cmdDay1AM.ExecuteReader())
            {
                while (rd.Read())
                {
                    lblAM1.Text = rd["CountAM"].ToString();
                    if (Convert.ToInt32(lblAM1.Text) < 2)
                    {
                        lblAM1.ForeColor = System.Drawing.Color.Red;

                    }
                    else
                    {
                        lblAM1.ForeColor = System.Drawing.Color.Green;
                    }
                }
            }
            }
        }


    }


}

protected void listWCSummary_SelectedIndexChanged(object sender, EventArgs e)
{
    BindSummaryViewRptr();
    string WeekId = listWCSummary.SelectedValue;
    string cs = ConfigurationManager.ConnectionStrings["GAUConnectionString1"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))

    {
        SqlCommand cmd = new SqlCommand("select * from JobWeekTable where Id=@ID", con);
        SqlParameter parameter = new SqlParameter("@ID", WeekId);
        cmd.Parameters.Add(parameter);
        con.Open();
        string Day1 = "select Day1 from JobWeekTable where Id='" + listWCSummary.SelectedValue + "'";
        SqlCommand GetFName = new SqlCommand(Day1, con);
        string FirstName = GetFName.ExecuteScalar().ToString().Replace("00:00:00", "");
    }


}

private void BindSummaryViewRptr()
{
    string WorkWeekId = listWCSummary.SelectedValue;
    string CustomerId = Request.QueryString["Id"];
    string cs = ConfigurationManager.ConnectionStrings["GAUConnectionString1"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("select distinct EngineerTable.EngineerName from EngineerTable inner join AppointmentTable on EngineerTable.OpId=AppointmentTable.OpId where AppointmentTable.WorkWeek = @WorkWeekId", con);
        SqlParameter parameter = new SqlParameter("@WorkWeekId", WorkWeekId);
        cmd.Parameters.Add(parameter);

        con.Open();
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

        {

            DataTable dtBrands = new DataTable();
            sda.Fill(dtBrands);
            rptrSummaryView.DataSource = dtBrands;
            rptrSummaryView.DataBind();

        }


    }
}

Answer 1

从约会中选择职位 AS Bakerjobs WHERE name = 'jim Baker'

从约会中选择职位 AS johnjobs WHERE name = 'John Smith'

这些查询将根据名称字段返回相应的作业编号。