Hello,
I have a method which inserts info into a table(Attendance) that is connected to a datagridview(dg).My question would be how can i determine a duplicate key so that I can update the datagridview when the duplicate key is detected?I have found some examples over the internet regarding using a count and this is how my method looks like now:
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,sNr,fName,lName,dateArrival,Attending,Departed,CourseId)Values(@SN,@sNr,@fName,@lName,@dateArrival,@Attending,@Departed,@ClassId) ";
using (var cmd = new SqlCommand(query, cnn))
{
try
{
string Studentquery = "select sNr, 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("@sNr", dr["sNr"].ToString());
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("@sNr", 0);
}
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))
{
int count = (int)checkData.ExecuteScalar();
if (count>0)
{
checkData = new SqlCommand(
"update AttendanceList set Departed=@Departed, dateDeparture=@dateDeparture where sNr=(SELECT MAX (sNr) FROM AttendanceList) and SN=@SN", cn);
cn.Open();
checkData.Parameters.AddWithValue("@SN", txtStudentId.Text);
checkData.Parameters.AddWithValue("@dateDeparture", dtArrival.Text);
checkData.Parameters.AddWithValue("@Departed", 1);
checkData.ExecuteNonQuery();
cn.Close();
dg.Update();
dg.Refresh();
Clr();
LoadData();
Clr();
}
}
}
}
}
}
When the PK is detected the first time,the records
"Insert into AttendanceList(SN,sNr,fName,lName,dateArrival,Attending,Departed,CourseId)Values(@SN,@sNr,@fName,@lName,@dateArrival,@Attending,@Departed,@ClassId) ";
are inserted into the database,but when i enter the same PK again,I get the error that there is a violation of the PK.The code part
else
{
using (var cnn = new SqlConnection(connstr))
{
int count = (int)checkData.ExecuteScalar();
if (count>0)
{
checkData = new SqlCommand(
"update AttendanceList set Departed=@Departed, dateDeparture=@dateDeparture where SN=@SN", cn);
cn.Open();
checkData.Parameters.AddWithValue("@SN", txtStudentId.Text);
checkData.Parameters.AddWithValue("@dateDeparture", dtArrival.Text);
checkData.Parameters.AddWithValue("@Departed", 1);
checkData.ExecuteNonQuery();
cn.Close();
dg.Update();
dg.Refresh();
Clr();
LoadData();
Clr();
}
}
}
checks to see if there is a duplicate key with the count.If it is,then it should update the rest of the information.The checkData comes from the method
private bool checkCourse()
{
using (var cn = new SqlConnection(connstr))
{
SqlCommand checkData = new SqlCommand("SELECT * FROM StudentCourses WHERE StudentId ='"+txtStudentId.Text+"' and courseid='"+ cmbClassId.SelectedValue.ToString() + "'", cn);
cn.Open();
SqlDataReader sdr = checkData.ExecuteReader();
if (sdr.Read())
{
return true;
}
cn.Close();
return false;
}
}
which checks to see if the student with that PK is at a specific course(but this works perfectly). Why is my approach not correct?How can I make it so that it will work as expected?Please let me know if there's any info that need to be provided.Thank you in advance!
What I have tried:
c# update if record exists else insert new record - Stack Overflow[
^]
https://stackoverflow.com/questions/23191951/how-to-avoid-insert-duplicate-record-in-sql-server-using-windows-form-in-c-sharp
[
^]