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)
{
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);
dr.Close();
cmd.ExecuteNonQuery();
cn.Close();
cnn.Close();
dg.Update();
dg.Refresh();
LoadData();
Clr();
}
}
}
catch (Exception ex)
{
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);
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.
Submit an article or tip