为什么更新数据库表时会创建新行? [英] Why it creates new row when I update database table?

查看:137
本文介绍了为什么更新数据库表时会创建新行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

您好,我有一个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 a COUNT query on the dateArrival field and creating a new dataset when that returns an even value. That would also return zero when the WHERE clause matches but the dateArrival field is NULL.

You should also catch errors for your update command.

I guess your problem is sourced by your UPDATE 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 the checkData.ExecuteNonQuery() call which I expect to be zero.

Why not let the initial query return the sNR and optionally some other fields looking only for a matching SN?
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 unique sNR.


这篇关于为什么更新数据库表时会创建新行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆