我正在用Winforms,MySQL和C#进行大学出勤项目。
在此我想在一个表格中更新“Total Absent”和“Total Present”。所以我为此写了代码:
int p = 0;
int a = 0;
string sno = "";
MySqlDataReader Reader;
command.CommandText = "select * from attendance_monthly_rpt";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
p = 0;
a = 0;
sno = Reader[1].ToString();
for (int k = 3; k <= 33; k++)
{
if ((Reader[k].ToString() == "P") || (Reader[k].ToString() =="OD"))
{
p += 1;
}
else if (Reader[k].ToString() == "Ab")
{
a += 1;
}
}
}
connection.Close();现在我得到了“sno”,“a”,“p”值。但是我想在MySQL表中更新这条记录。所以我添加了UPDATE声明:
while (Reader.Read())
{
p = 0;
a = 0;
sno = Reader[1].ToString();
for (int k = 3; k <= 33; k++)
{
if ((Reader[k].ToString() == "P") || (Reader[k].ToString() =="OD"))
{
p += 1;
}
else if (Reader[k].ToString() == "Ab")
{
a += 1;
}
}
command.CommandText = "update attendance_monthly_rpt set tot_persent = " +
p + ", tot_absent = " + a + " where student_no = '" +
sno + "'";
connection.Open(); // the connection is already open
command.ExecuteNonQuery();
connection.Close();
}
connection.Close()这会引发错误“连接已经打开”。如果我在循环之后放置更新语句,我只能得到最后一条记录。
请帮助我为每个“学生”更新我的“Tot absent”,“Tot present”。