为什么更新数据库表时会创建新行? [英] Why it creates new row when I update database table?
问题描述
您好,我有一个winforms项目,需要为学生出勤.我有一个文本框,上面插入了值,并根据该值将数据插入表格中.我有2个标志来证明出勤率,主要是我有一个要到达的人,一个要离开的人.当学生插入数据时,第一个标志(参加)将设置为true,表示复选框将打勾.我的问题是,当第二次插入数据时,表将不会不会使用已离开"标志进行更新,这意味着它将打勾,但它会创建与以前相同的行.我不知道为什么会这样.如果我在插入数据时将正在参加"标志保留为0,则已离开的标志会起作用.这是我的出勤清单:
CREATE TABLE [dbo].[AttendanceList]( [sNr] [int] IDENTITY(1,1) NOT NULL, [SN] [char](10) NOT NULL, [fName] [nvarchar](max) NOT NULL, [lName] [nvarchar](max) NOT NULL, [dateArrival] [datetime] NOT NULL, [dateDeparture] [datetime] NULL, [Attending] [bit] NULL, [CourseID] [nvarchar](50) NULL, [Departed] [bit] NULL, PRIMARY KEY CLUSTERED ( [sNr] ASC
这是在datagridview中保存和更新表的方法:
private void btnSave_Click(object sender, EventArgs e) { var result = checkCourse(); if(result==true) { using (var cn = new SqlConnection(connstr)) { SqlCommand checkData = new SqlCommand("SELECT COUNT(dateArrival) FROM AttendanceList WHERE SN = @Id and Attending=0", cn); checkData.Parameters.AddWithValue("@Id", txtStudentId.Text); cn.Open(); int Exist = (int)checkData.ExecuteScalar(); cn.Close(); if (Exist % 2 == 0) { try { using (var cnn = new SqlConnection(connstr)) { var query = "Insert into AttendanceList(SN,fName,lName,dateArrival,Attending,Departed,CourseId)Values(@SN,@fName,@lName,@dateArrival,@Attending,@Departed,@ClassId)"; using (var cmd = new SqlCommand(query, cnn)) { try { string Studentquery = "select fName,lName from RegisterStudent WHERE (SN = @SN)"; using (var Student = new SqlCommand(Studentquery, cnn)) { Student.Parameters.AddWithValue("@SN", txtStudentId.Text); cnn.Open(); cn.Open(); dr = Student.ExecuteReader(); if (dr.HasRows == true) { while (dr.Read()) { if (dr.HasRows == true) { cmd.Parameters.AddWithValue("@fName", dr["fName"].ToString()); cmd.Parameters.AddWithValue("@lName", dr["lName"].ToString()); } } } } } catch (Exception ex) { // write exception info to log or anything else MessageBox.Show(ex.Message); cmd.Parameters.AddWithValue("@fName", ""); cmd.Parameters.AddWithValue("@lName", ""); } cmd.Parameters.AddWithValue("@SN", txtStudentId.Text); cmd.Parameters.AddWithValue("@dateArrival", dtArrival.Text); cmd.Parameters.AddWithValue("@ClassId", cmbClassId.SelectedValue.ToString()); cmd.Parameters.AddWithValue("@Departed", 0); cmd.Parameters.AddWithValue("@Attending", 1); //here i set the attending flag to 1 when student registers into the attedancelist dr.Close(); cmd.ExecuteNonQuery(); cn.Close(); cnn.Close(); dg.Update(); dg.Refresh(); LoadData(); Clr(); } } } catch (Exception ex) { // write exception info to log or anything else MessageBox.Show(ex.Message); } } else { using (var cnn = new SqlConnection(connstr)) { checkData = new SqlCommand( "update AttendanceList set Departed=@Departed, dateDeparture=@dateDeparture where sNr=(SELECT MAX (sNr) FROM AttendanceList) and SN =@Id", cn); cn.Open(); checkData.Parameters.AddWithValue("@Id", txtStudentId.Text); checkData.Parameters.AddWithValue("@dateDeparture", dtArrival.Text); checkData.Parameters.AddWithValue("@Departed", 1);//here i set it when the student inserts his data the second time and the departed flag is set to 1. checkData.ExecuteNonQuery(); cn.Close(); Clr(); LoadData(); Clr(); } } } }
我尝试过的事情:
我试图将Attending标志设置为0,然后由于某种原因它起作用了,但是当学生第一次插入数据时,它不会设置为1(当学生第一次插入他的数据时,它将设置到1时,当他第二次插入数据时-这是相同的数据,基本上是一个串行nr,它将更新Departed标志),但是当我第二次插入数据时,departed标志将打开. ="h2_lin">解决方案
我不理解在dateArrival
字段上使用COUNT
查询并在返回偶数时创建新数据集的逻辑.当WHERE
子句匹配但dateArrival
字段为NULL
时,也将返回零.
您还应该为更新命令捕获错误.
我想您的问题是由您的UPDATE
命令引起的.
的目的是什么sNr =( SELECT MAX(sNr) FROM AttendanceList
WHERE
子句?
那只会匹配最近添加的记录.
如果数据已更新,则可以使用checkData.ExecuteNonQuery()
调用的返回值(我希望它为零)来进行简单检查.
为什么不让初始查询返回sNR
和可选的其他一些字段,这些字段仅寻找匹配的SN
?
如果不匹配,请创建一个新记录.否则,请使用其他字段来决定是否应更新数据,然后可以使用唯一的sNR
来完成.
Hello,I have a project in winforms where i need to make attendance for students.I have a textbox that takes the value inserted and based on that it inserts data into the table.I have 2 flags that takes evidence of the attendance,mainly i have one for arrival and one for departure.When the student inserts data,the first flag(Attending) will set true,meaning the checkbox will tick.My problem is that when it inserts data the second time,the table won''t update with Departed flag,meaning that it will tick,but it will create the same row as before.I don''t understand why is that happening.If i leave the Attending flag 0 on inserting data,the departed one works.This is my AttendanceList:
CREATE TABLE [dbo].[AttendanceList]( [sNr] [int] IDENTITY(1,1) NOT NULL, [SN] [char](10) NOT NULL, [fName] [nvarchar](max) NOT NULL, [lName] [nvarchar](max) NOT NULL, [dateArrival] [datetime] NOT NULL, [dateDeparture] [datetime] NULL, [Attending] [bit] NULL, [CourseID] [nvarchar](50) NULL, [Departed] [bit] NULL, PRIMARY KEY CLUSTERED ( [sNr] ASC
And this is the method for saving and updating the table in datagridview:
private void btnSave_Click(object sender, EventArgs e) { var result = checkCourse(); if(result==true) { using (var cn = new SqlConnection(connstr)) { SqlCommand checkData = new SqlCommand("SELECT COUNT(dateArrival) FROM AttendanceList WHERE SN = @Id and Attending=0", cn); checkData.Parameters.AddWithValue("@Id", txtStudentId.Text); cn.Open(); int Exist = (int)checkData.ExecuteScalar(); cn.Close(); if (Exist % 2 == 0) { try { using (var cnn = new SqlConnection(connstr)) { var query = "Insert into AttendanceList(SN,fName,lName,dateArrival,Attending,Departed,CourseId)Values(@SN,@fName,@lName,@dateArrival,@Attending,@Departed,@ClassId)"; using (var cmd = new SqlCommand(query, cnn)) { try { string Studentquery = "select fName,lName from RegisterStudent WHERE (SN = @SN)"; using (var Student = new SqlCommand(Studentquery, cnn)) { Student.Parameters.AddWithValue("@SN", txtStudentId.Text); cnn.Open(); cn.Open(); dr = Student.ExecuteReader(); if (dr.HasRows == true) { while (dr.Read()) { if (dr.HasRows == true) { cmd.Parameters.AddWithValue("@fName", dr["fName"].ToString()); cmd.Parameters.AddWithValue("@lName", dr["lName"].ToString()); } } } } } catch (Exception ex) { // write exception info to log or anything else MessageBox.Show(ex.Message); cmd.Parameters.AddWithValue("@fName", ""); cmd.Parameters.AddWithValue("@lName", ""); } cmd.Parameters.AddWithValue("@SN", txtStudentId.Text); cmd.Parameters.AddWithValue("@dateArrival", dtArrival.Text); cmd.Parameters.AddWithValue("@ClassId", cmbClassId.SelectedValue.ToString()); cmd.Parameters.AddWithValue("@Departed", 0); cmd.Parameters.AddWithValue("@Attending", 1); //here i set the attending flag to 1 when student registers into the attedancelist dr.Close(); cmd.ExecuteNonQuery(); cn.Close(); cnn.Close(); dg.Update(); dg.Refresh(); LoadData(); Clr(); } } } catch (Exception ex) { // write exception info to log or anything else MessageBox.Show(ex.Message); } } else { using (var cnn = new SqlConnection(connstr)) { checkData = new SqlCommand( "update AttendanceList set Departed=@Departed, dateDeparture=@dateDeparture where sNr=(SELECT MAX (sNr) FROM AttendanceList) and SN =@Id", cn); cn.Open(); checkData.Parameters.AddWithValue("@Id", txtStudentId.Text); checkData.Parameters.AddWithValue("@dateDeparture", dtArrival.Text); checkData.Parameters.AddWithValue("@Departed", 1);//here i set it when the student inserts his data the second time and the departed flag is set to 1. checkData.ExecuteNonQuery(); cn.Close(); Clr(); LoadData(); Clr(); } } } }
What I have tried:
I have tried to set the Attending flag to 0 ,and then it works for some reason,but when the student first inserts data,it won''t set to 1(when the student inserts the first time his data,it will set the Attending to 1 and when he inserts the data the second time-which is the same data,basically a serial nr,it will update the Departed flag) but when i insert data the second time,the departed flag will be on.
I don''t understand the logic using aCOUNT
query on thedateArrival
field and creating a new dataset when that returns an even value. That would also return zero when theWHERE
clause matches but thedateArrival
field isNULL
.
You should also catch errors for your update command.
I guess your problem is sourced by yourUPDATE
command. What is the purpose of the
sNr=(SELECT MAX (sNr) FROM AttendanceList
WHERE
clause?
That would only match the recently added record.
If the data has been updated can be simply checked with the return value of thecheckData.ExecuteNonQuery()
call which I expect to be zero.
Why not let the initial query return thesNR
and optionally some other fields looking only for a matchingSN
?
If there is no match, create a new record. Otherwise use the other fields to decide if the data should be updated which can be then done with the uniquesNR
.
这篇关于为什么更新数据库表时会创建新行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!