引用上一个在循环中创建的对象来更新SQL [英] Reference previous object created in loop to update SQL
本文介绍了引用上一个在循环中创建的对象来更新SQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个循环设置窗体，它是以下代码（在加载事件形式）。这将显示一个带有人名的复选框。如果位字段为1，它将检查复选框。
int xAxisCheckbox = 130;
int yAxisCheckbox = 30;
for（int i = 0; i
this.myCheckBox = new CheckBox（）;
myCheckBox.Location = new Point（xAxisCheckbox，yAxisCheckbox）;
myCheckBox.Size = new Size（120，20）;
myCheckBox.Text = selectDS.Tables [0] .Rows [i] [FullName]。ToString（）;
myCheckBox.Checked =（bool）selectDS.Tables [0] .Rows [i] [InOperation];
yAxisCheckbox = yAxisCheckbox + 80;
}
在代码中（对于保存按钮单击事件）相同的选择，但也加载更新以将InOperation字段设置为true / false，取决于tick。如果您要添加到操作，它还会重置OperationOrder。
for（int i = 0; i
userID =（int）selectDataSet.Tables [0] .Rows [i] [UserID];
if（myCheckBox.Checked）
{
connection.runUpdate（UPDATE Users SET InOperation = 1，OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation = 1 THEN OperationOrder ELSE SELECT COUNT（*）+ 1 FROM Users WHERE InOperation = 1 AND OperationOrder> 0）END END WHERE UserID =+ userID）;
connection.runUpdate（UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN（SELECT COUNT（*）FROM Users WHERE InOperation = 1）ELSE OperationOrder END WHEREInOperation = 1）;
}
else
{
connection.runUpdate（UPDATE Users SET InOperation = 0，OperationOrder = 0 WHERE UserID =+ userID）;
connection.runUpdate（UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN（SELECT COUNT（*）FROM Users WHERE InOperation = 1）ELSE OperationOrder -1 END WHERE InOperation = 1）;
}
}
问题是它更新每一行基于最后创建的对象（例如，如果5行，只有底部的复选框将计算运行SQL，它适用于所有的）。如何更新每一行，是否有一种方法可以引用我创建的每个对象，而不仅仅是最后一个创建的对象？
更新：这里是一些新的导致错误的代码。
public partial class SelectUsers：Form
{
public int userID;
public List myBoxes;
public SelectUsers（）
{
InitializeComponent
}
private void SelectUsers_Load（object sender，EventArgs e）
{
DataSet ds = myconnection.runSelect（new DataSet（），THE SELECT）;
int xAxisCheckbox = 40;
int yAxisCheckbox = 50;
myBoxes = new List< CheckBox>（）;
for（int i = 0; i< ds.Tables [0] .Rows.Count; i ++）
{
this.myCheckBox = new CheckBox（）;
myCheckBox.Location = new Point（xAxisCheckbox，yAxisCheckbox）;
myCheckBox.Size = new Size（120，20）;
myCheckBox.Text = ds.Tables [0] .Rows [i] [FullName]。ToString（）;
myCheckBox.Checked =（bool）ds.Tables [0] .Rows [i] [InOperation];
yAxisCheckbox = yAxisCheckbox + 80;
myBoxes.Add（myCheckBox）;
}
}
private void saveBtn_Click（object sender，EventArgs e）
{
DataSet ds = myconnection.runSelect（new DataSet（）， THE SELECT）;
for（int i = 0; i
userID =（int）ds.Tables [0 ] .Rows [i] [UserID];
if（myBoxes [i] .Checked）
{
myconnection.runUpdate（UPDATE Users SET InOperation = 1，OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation = 1 THEN OperationOrder ELSE（SELECT COUNT（*）+ 1 FROM Users WHERE InOperation = 1 AND OperationOrder> 0）END END WHERE UserID =+ userID）;
myconnection.runUpdate（UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN（SELECT COUNT（*）FROM Users WHERE InOperation = 1）ELSE OperationOrder END WHEREInOperation = 1）;
}
else
{
myconnection.runUpdate（UPDATE Users SET InOperation = 0，OperationOrder = 0 WHERE UserID =+ userID）;
myconnection.runUpdate（UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN（SELECT COUNT（*）FROM Users WHERE InOperation = 1）ELSE OperationOrder -1 END WHERE InOperation = 1）;
}
}
}
解决方案>
Carlos Grappa的答案是通过使用数组指向我的正确方向，但它是设置错误。阅读完编程书之后，我发现你创建了一个数组如下：
//全局
CheckBox [] myCheckBox;
DataSet ds;
private void SelectUsers_Load（object sender，EventArgs e）{
ds = myconnection.runSelect（new DataSet（），THE SELECT）;
int xAxisCheckbox = 40;
int yAxisCheckbox = 50;
//创建CheckBox数组（基于行数）
myBoxes = new CheckBox [ds.Tables [0] .Rows.Count];
for（int i = 0; i< ds.Tables [0] .Rows.Count; i ++）
{
//创建一个新的复选框
myCheckBox [i] = new CheckBox（）;
myCheckBox.Location = new Point（xAxisCheckbox，yAxisCheckbox）;
myCheckBox.Size = new Size（120，20）;
myCheckBox.Text = ds.Tables [0] .Rows [i] [FullName]。ToString（）;
myCheckBox.Checked =（bool）ds.Tables [0] .Rows [i] [InOperation];
yAxisCheckbox = yAxisCheckbox + 80;
//添加复选框
Controls.Add（myCheckBox [i]）;
}
}
private void saveBtn_Click（object sender，EventArgs e）{
for（int i = 0; i< ds.Tables [0]。 rows.Count; i ++）
{
userID =（int）ds.Tables [0] .Rows [i] [UserID];
if（myBoxes [i] .Checked）
{
myconnection.runUpdate（UPDATE Users SET InOperation = 1，OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation = 1 THEN OperationOrder（SELECT COUNT（*）+ 1 FROM Users WHERE InOperation = 1 AND OperationOrder> 0）END END WHERE UserID =+ userID）;
myconnection.runUpdate（UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN（SELECT COUNT（*）FROM Users WHERE InOperation = 1）ELSE OperationOrder END WHEREInOperation = 1）;
}
else
{
myconnection.runUpdate（UPDATE Users SET InOperation = 0，OperationOrder = 0 WHERE UserID =+ userID）;
myconnection.runUpdate（UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN（SELECT COUNT（*）FROM Users WHERE InOperation = 1）ELSE OperationOrder -1 END WHERE InOperation = 1）;
}
}
}
Deroby你可能是对的，但我不知道该怎么做。
I have a loop that sets up the form, which is the following code (in the form load event). This displays a checkbox with the persons name. It checks the checkbox if a bit field is 1.
int xAxisCheckbox = 130;
int yAxisCheckbox = 30;
for (int i = 0; i < selectDS.Tables[0].Rows.Count; i++)
{
this.myCheckBox = new CheckBox();
myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
myCheckBox.Size = new Size(120, 20);
myCheckBox.Text = selectDS.Tables[0].Rows[i]["FullName"].ToString();
myCheckBox.Checked = (bool)selectDS.Tables[0].Rows[i]["InOperation"];
yAxisCheckbox = yAxisCheckbox + 80;
}
Later on in the code (for a save button click event), it runs the same select but also a load of updates to set the InOperation field to true/false depending on the tick. It also resets the OperationOrder if you are being added to the Operation.
for (int i = 0; i < selectDataSet.Tables[0].Rows.Count; i++)
{
userID = (int)selectDataSet.Tables[0].Rows[i]["UserID"];
if (myCheckBox.Checked)
{
connection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
connection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
}
else
{
connection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
connection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
}
}
The problem with this is that it updates every single row based upon the last object created (e.g. if 5 rows, only the bottom checkbox would count for running the SQL, and it applies to all of them). How can I update every single row, is there a way I can reference each object I create rather than just the last one created?
UPDATE: Here is some of the new code that is causing errors. public partial class SelectUsers : Form { public int userID; public List myBoxes;
public SelectUsers()
{
InitializeComponent();
}
private void SelectUsers_Load(object sender, EventArgs e)
{
DataSet ds = myconnection.runSelect(new DataSet(), "THE SELECT");
int xAxisCheckbox = 40;
int yAxisCheckbox = 50;
myBoxes = new List<CheckBox>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
this.myCheckBox = new CheckBox();
myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
myCheckBox.Size = new Size(120, 20);
myCheckBox.Text = ds.Tables[0].Rows[i]["FullName"].ToString();
myCheckBox.Checked = (bool)ds.Tables[0].Rows[i]["InOperation"];
yAxisCheckbox = yAxisCheckbox + 80;
myBoxes.Add(myCheckBox);
}
}
private void saveBtn_Click(object sender, EventArgs e)
{
DataSet ds = myconnection.runSelect(new DataSet(), "THE SELECT");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
userID = (int)ds.Tables[0].Rows[i]["UserID"];
if (myBoxes[i].Checked)
{
myconnection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
}
else
{
myconnection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
}
}
}
解决方案
Carlos Grappa answer was pointing me in the right direction by saying use an array, but it was setup wrong. After reading through programming books, I found that you create an array as follows:
// Global
CheckBox[] myCheckBox;
DataSet ds;
private void SelectUsers_Load(object sender, EventArgs e) {
ds = myconnection.runSelect(new DataSet(), "THE SELECT");
int xAxisCheckbox = 40;
int yAxisCheckbox = 50;
// Create the CheckBox array (amount based on number of rows)
myBoxes = new CheckBox[ds.Tables[0].Rows.Count];
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
// Create a new Checkbox
myCheckBox[i] = new CheckBox();
myCheckBox.Location = new Point(xAxisCheckbox, yAxisCheckbox);
myCheckBox.Size = new Size(120, 20);
myCheckBox.Text = ds.Tables[0].Rows[i]["FullName"].ToString();
myCheckBox.Checked = (bool)ds.Tables[0].Rows[i]["InOperation"];
yAxisCheckbox = yAxisCheckbox + 80;
// Add the Checkbox
Controls.Add(myCheckBox[i]);
}
}
private void saveBtn_Click(object sender, EventArgs e) {
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
userID = (int)ds.Tables[0].Rows[i]["UserID"];
if (myBoxes[i].Checked)
{
myconnection.runUpdate("UPDATE Users SET InOperation = 1, OperationOrder = CASE WHEN OperationOrder = 1 THEN 1 ELSE CASE WHEN InOperation=1 THEN OperationOrder ELSE (SELECT COUNT(*)+1 FROM Users WHERE InOperation=1 AND OperationOrder > 0) END END WHERE UserID=" + userID);
myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder END WHERE InOperation=1");
}
else
{
myconnection.runUpdate("UPDATE Users SET InOperation = 0, OperationOrder = 0 WHERE UserID=" + userID);
myconnection.runUpdate("UPDATE Users SET OperationOrder = CASE WHEN OperationOrder -1 = 0 THEN (SELECT COUNT(*) FROM Users WHERE InOperation=1) ELSE OperationOrder -1 END WHERE InOperation=1");
}
}
}
Deroby you are probably right, however I do not know how to do this.
这篇关于引用上一个在循环中创建的对象来更新SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
