Почему он создает новую строку, когда я обновляю таблицу базы данных?
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
И это метод сохранения и обновления таблицы в 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(когда студент вставляет свои данные в первый раз,он установит Attending на 1,а когда он вставляет данные во второй раз-это те же самые данные,в основном последовательный nr,он обновит флаг Departed), но когда я вставляю данные во второй раз, флаг departed будет включен.