正在更新SQL中的多个数据项

本文关键字：数据项 更新 SQL | 更新日期: 2023-09-27 18:29:00

我得到了一个日期时间列表，并用下面的代码将它们存储在一个复选框列表中：

    <anthem:CheckBox ID="chkAll" runat="server" OnCheckedchanged="chkAll_CheckedChanged" Text="Select/Deselect All" AutoPostBack="true"
        style="margin-left: 128px" >
    </anthem:CheckBox>
    <anthem:CheckBoxList ID="CheckOpenTimesheets"  runat="server" OnSelectedIndexChanged="checkbox_Selected" AutoPostBack="true"
        style="margin-left: 128px" >
    </anthem:CheckBoxList>

以下是相关代码：

            List<ListItem> toBeRemoved = new List<ListItem>();
            for (int i = 1; i < CheckOpenTimesheets.Items.Count; i++)
            {
                toBeRemoved.Add(CheckOpenTimesheets.Items[i]);
            }
            for (int i = 0; i < toBeRemoved.Count; i++)
            {
                CheckOpenTimesheets.Items.Remove(toBeRemoved[i]);
            }
            String sql = "SELECT StartDate FROM Periods WHERE User_ID = @userid AND (PeriodStatus_ID = 1 OR PeriodStatus_ID = 2) ORDER BY StartDate DESC";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("userid", ddlActingAs.SelectedValue.ToString()));
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ListItem item = new ListItem();
                item.Text += reader.GetDateTime(0).ToString("MM/dd/yyyy") + " is open";
                item.Value = reader["StartDate"].ToString();
                CheckOpenTimesheets.Items.Add(item);
            }
            CheckOpenTimesheets.UpdateAfterCallBack = true;
            reader.Close();
//The functions below are for selecting/deselecting the items in the checklistbox
protected void chkAll_CheckedChanged(object sender, EventArgs e)
{
    foreach (ListItem item in CheckOpenTimesheets.Items)
    {
        item.Selected = chkAll.Checked;
    }
}
protected void checkbox_Selected(object sender, EventArgs e)
{
    chkAll.CheckedChanged -= chkAll_CheckedChanged;
    CheckBoxList checkOpenTimesheets = (CheckBoxList)sender;
    if (allItemsCheckedInCheckBoxList(checkOpenTimesheets))
    {
        chkAll.Checked = true;
    }
    else if (allItemsUnCheckedInCheckBoxList(checkOpenTimesheets))
    {
        chkAll.Checked = false;
    }
    chkAll.CheckedChanged += chkAll_CheckedChanged;
}
private bool allItemsCheckedInCheckBoxList(CheckBoxList checkBoxList)
{
    bool allItemsChecked = true;
    foreach (ListItem item in checkBoxList.Items)
    {
        allItemsChecked = item.Selected;
        if (!allItemsChecked)
            break;
    }
    return allItemsChecked;
}
private bool allItemsUnCheckedInCheckBoxList(CheckBoxList checkBoxList)
{
    bool allItemsUnChecked = false;
    foreach (ListItem item in checkBoxList.Items)
    {
        allItemsUnChecked = item.Selected;
        if (allItemsUnChecked)
            break;
    }
    return allItemsUnChecked;
}

我想做的是，当我在页面上的一个按钮被点击时，它会在复选框列表中循环，并且选中的每个项目都会将SQL表中每个项目的PeriodStatus_ID更新为5。

点击按钮的功能在这里（不确定如何进行）：

protected void SubmitAll_Click(object sender, EventArgs e)
{
    foreach (ListItem item in CheckOpenTimesheets.Items)
    {
        SqlCommand command = new SqlCommand();
        command.Connection = gConn;
        if (item.Selected == true)
        {
            String sql = "UPDATE Periods SET PeriodStatus_ID=5 WHERE User_ID = @userid AND StartDate = @startdate";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("userid", ddlActingAs.SelectedValue.ToString()));
            command.Parameters.Add(new SqlParameter("startdate", item.Value));
        }
    }
}

任何关于实现这一目标的想法都将是伟大的，提前感谢。

正在更新SQL中的多个数据项

你更新了你的代码，所以我更新了我的代码，但我改变了你想要如何构建它的想法。你还省略了ExecuteNonQuery语句，这是发送Update命令的方法。

protected void SubmitAll_Click(object sender, EventArgs e)
{
     SqlCommand command = new SqlCommand();
     command.Connection = gConn;
     String sql = "UPDATE Periods SET PeriodStatus_ID=5 WHERE User_ID = @userid AND StartDate = @startdate";
     command.CommandText = sql;
     command.Parameters.Add("userid");
     command.Parameters.Add("startdate");
    for (int i = 0; i < CheckOpenTimesheets.Items.Count; i++)
    {
     if (item.Selected == true)
     {
        command.Parameters("userid").Value = ddlActingAs.SelectedValue.ToString();
        command.Parameters("startdate").Value = item.Value;
        command.ExecuteNonQuery();
        }
    }
}
相关文章：