Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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)
                                   {
                                       // write exception info to log or anything else
                                       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)
                       {
                           // write exception info to log or anything else
                           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[^]
Posted
Updated 22-Jun-18 0:47am
v2
Comments
Mike V Baker 22-Jun-18 14:02pm    
It looks like there's a possibility in your logic where a record can exist but still fail the test. Look at this:
SqlCommand checkData = new SqlCommand("SELECT COUNT(dateArrival) FROM AttendanceList WHERE SN = @Id and Attending=0", cn);

Your criteria here is SN = @Id *AND* Attending=0. So if Attending doesn't equal 0 it won't be returned. I'm guessing that your primary key is just the SN so there you are. You're going to try to insert a new record with an SN that's already there. I'm not sure about the use of modulo.
if (Exist %2 == 0)
So you're checking to see if the number of records is an even number? Also... now that I think about it, I don't see anything here about a particular class. There's a student, and attendance... where's the specification in your query about what class is being attended? I see CourseId in the insert query but I don't see it used in the check to find a previous entry.

You mentioned in the intro that this data was connected to a data grid. But the names of the controls suggest individual controls, not rows and columns in a grid.
Eliza Maria 23-Jun-18 7:32am    
Thank you for answering.I can see that,since you don't understand what i'm trying to figure out,it makes it difficult for you also to give me an explanation.So,this is my logic in the code:
The query SqlCommand checkData = new SqlCommand("SELECT COUNT(dateArrival) FROM AttendanceList WHERE SN = @Id and Attending=0", cn);
inserts the data of the student into the AttendanceList table which is then displayed in the datagridview.Selecting the count in dateArrival is basically just a count that states that the Student is registered in RegisterStudent table with the info required,then the info from registerStudent will be inserted into the AttendanceList if the Serial nr from the textbox matches the serialnr in RegisterStudent(basically the record will be added based on dateTime).The Atteding=0 is logical that it has to be 0,because otherwise it means that the student is already attending that specific course.Indeed,the parameter ClassId and CourseID don't match but there are one and the same because parameter ClassId defines CourseId.There's no specification about what class is attending the student,but what course he's attending.Also,I don't understand what you mean by "It looks like there's a possibility in your logic where a record can exist but still fail the test."because this is the query for inserting data into AttendanceList,the other query checkData = new SqlCommand(
"update AttendanceList set Departed=@Departed, dateDeparture=@dateDeparture where SN=@SN", cn);
cn.Open();
is for updating the AttendanceList with dateDeparture and Departed which means that when the textbox will identify one more time the same serial nr,it should update the dateDeparture and Departed.I hope you understand my logic,because the part that you mentioned in your comments works perfectly,the update part is tricky.Please let me know if you understand why is that part not working.Best regards!
Mike V Baker 23-Jun-18 14:00pm    
Thanks for the added information. I'm not sure it helped :) I'll try to concentrate just on the duplicate key part. One thing for certain, you cannot insert a record with the same PK as another record. It defeats the purpose of PRIMARY KEY. So what is the PK of the AttendanceList table? If it's the SN + the CourseId then you can only have one record for that student and that course.
What's an sNr? Does that play a part in the PK? Is the combination of those three things always a unique value?

At one point you said that the "else" checks to see if there's a duplicate key with the count. However that "else" stems from the if (Exist % 2 == 0) which is testing to see if you have an even number of records. What if there are records in that table for other courses? It doesn't appear to include all the values it would need for the PK so it might not be ruling out some records that it should.

I just noticed one more thing. In the 'insert' side you're setting departed=0 and attending=1. In the 'update' side you're setting departed=1 but you're not setting attending=0. Will attending always be 1 once they have attended at lease once?
Eliza Maria 24-Jun-18 10:43am    
Thank you for your response.Attending and departed are 2 separate flags.When I insert into the AttendanceList-which has SN(which is the student nr of the student)the PK-it inserts the dateArrival(with datetime)and updates the Attending bite to 1,leaving the Departed one 0.When the SN will be inserted the second time,it will update the dateDeparture and Departed flag will be 1 along with the Attending flag.I have tried with the syntax "ON duplicate key update" but this didn't work.Also I have used the count to determine if there is a duplicate key,in that key it should update.My problem is why is not working the updating part based on the duplicate key?Is my approach wrong?


Best regards
Mike V Baker 26-Jun-18 10:12am    
So you're saying that the PK in the AttendanceList table is the SN. Only the SN, no other fields are part of the Primary Key? This would mean that you can only have one record in the AttendanceList table for that student. The student cannot attend more than one class at a time, ever. Is this a transactional table where it's only used for a limited time and records are deleted when you're done with the process? If the PK is just the SN, then using a count to determine if there are multiple should never return > 1 because you can't insert two records with the same PK.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900