更新多个复选框值为SQL DB asp.net c#
本文关键字:DB asp net SQL 复选框 更新 | 更新日期: 2023-09-27 18:16:49
我正在使用下面的代码更新SQL DB中的一行。循环工作,它更新行,但问题是,每次它通过循环,它只更新一个值,其他值被覆盖。所以最后,它更新了,但不是为每个项目ID输入多个值,而是为每个项目ID只输入一个值。我没有收到任何错误。如有任何帮助,不胜感激。
for (int i = 0; i < cbAvailableEntities.Items.Count - 1; i++)
{
SqlConnection connection = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("UpdateProjectEntity", connection);
using (connection)
{
connection.Open();
using (cmd)
{
if (cbAvailableEntities.Items[i].Selected)
{
cmd.CommandType = CommandType.StoredProcedure;
//the following is the Project ID for the row being updated.
SqlParameter paramPID = new SqlParameter("@ProjectID", nr.ProjectID);
cmd.Parameters.Add(paramPID);
nr.Entities = cbAvailableEntities.Items[i].Value;
cmd.Parameters.AddWithValue("@CorpID", nr.Entities);
cmd.ExecuteNonQuery();
}
}
}
}
下面是存储过程"UpdateProjectEntity"
的SQL查询ALTER PROCEDURE [dbo].[UpdateProjectEntity]
@ProjectID int,
@CorpID int
AS
BEGIN
UPDATE [dbo].[ProjectEntity]
SET
[CorpID] = @CorpID
WHERE
ProjectID = @ProjectID
END
下面是运行程序时输入和结果的屏幕截图。这些是我保存到DB
的复选框这是我保存到DB后的结果
我更改了日期,以显示程序中其他一切都正常。
我可以看到你保存你的实体INT
,也许你应该保存为逗号分隔字符串。
所以你可以保存1,2,3
,而不是保存1
当然,您必须在保存构建和连接字符串之前添加一些逻辑。并且还需要做一些解析,当你从数据库读取做分割,
另一种方法是创建一个关系表来指示所选择的选项。
但是当你删除一个选择并添加新的选择时,这也会出现问题。
ProjectID CorpID
1 1
1 2
1 3
在不更改数据库的情况下解决这个问题的方法是使用DELETE语句删除带有ProjectID的行,然后使用以前使用过的插入存储过程。这比在所有已经存在的表中创建另一个表要快得多。代码是这样的
for (int i = 0; i < cbAvailableEntities.Items.Count - 1; i++) {
SqlConnection connection = new SqlConnection(connString);
SqlCommand com = new SqlCommand("InsertProjectEntity", connection);
SqlCommand dcm = new SqlCommand();
using(connection) {
//First time going through the loop, i = 0 is true.
if (i == 0) {
connection.Open();
using(com) {
//This will remove anything in the DB related to the ProjectID being edited.
dcm.Connection = connection;
dcm.CommandText = "DELETE FROM [dbo].[ProjectEntity] WHERE ProjectID = " + _pID;
dcm.ExecuteNonQuery();
//This will insert all items checked in the checkboxlist but will not insert the unchecked.
if (cbAvailableEntities.Items[i].Selected) {
com.CommandType = CommandType.StoredProcedure;
SqlParameter paramPID = new SqlParameter("@ProjectID", nr.ProjectID);
com.Parameters.Add(paramPID);
nr.Entities = cbAvailableEntities.Items[i].Value;
com.Parameters.AddWithValue("@CorpID", nr.Entities);
com.ExecuteNonQuery();
}
}
} else {
connection.Open();
using(com) {
//This will insert all items checked in the checkboxlist but will not insert the unchecked.
if (cbAvailableEntities.Items[i].Selected) {
com.CommandType = CommandType.StoredProcedure;
SqlParameter paramPID = new SqlParameter("@ProjectID", nr.ProjectID);
com.Parameters.Add(paramPID);
nr.Entities = cbAvailableEntities.Items[i].Value;
com.Parameters.AddWithValue("@CorpID", nr.Entities);
com.ExecuteNonQuery();
}
}
}
}