Not able to send sms in loop properly, sending multiple sms to single person by mistake

Question

I'm trying to send sms to the absentees number using gridview in asp.net. the functionality goes as follows: - out of all students in a class, as the user unchecked the the students will be marked as absent and vice versa. - now the problem is that when I'm trying to uncheck a student and submit,, it sends me around 50 messages at once even though only one student is absent. it should send only the message to the absentees. Kindly help me out referring the below code of mine. Thanks in advance:

protected void InsertAttendence()
{
    DateTime systemdate2 = DateTime.Today.Date;
    foreach (GridViewRow row in gvStudents.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
            CheckBox chkAttendance = row.FindControl("chkAttendence") as CheckBox;
            string attendanceStatus = chkAttendance.Checked ? "Present" : "Absent";
            string Class = ((row.FindControl("lblclass") as Label).Text.Trim());
            string StudentName = ((row.FindControl("lblname") as Label).Text.Trim());
            string STSNO = ((row.FindControl("lblstsno") as Label).Text.Trim());
            string Mobile = ((row.FindControl("lblmobile") as Label).Text.Trim());
            string Division = ((row.FindControl("lbldiv") as Label).Text.Trim());
            //string sttendenceDate = (this.txtDate.Text.Trim());
            string constring = ConfigurationManager.ConnectionStrings["stjosephconnect"].ConnectionString;
            using (SqlConnection conInsert = new SqlConnection(constring))
            {
                try
                {
                    string query = "INSERT INTO studentattendance(attdate, stsno, name, mobile, class, div, attendance, remarks, status, rem) "
                                   + "VALUES(@attdate, @stsno, @name, @mobile, @class, @div, @attendance, @remarks, @status, @rem)";
                    using (SqlCommand cmd = new SqlCommand(query, conInsert))
                    {
                        conInsert.Open();
                        cmd.Parameters.AddWithValue("@attdate", systemdate2);
                        cmd.Parameters.AddWithValue("@stsno", STSNO);
                        cmd.Parameters.AddWithValue("@name", StudentName);
                        cmd.Parameters.AddWithValue("@mobile", Mobile);
                        cmd.Parameters.AddWithValue("@class", Class);
                        cmd.Parameters.AddWithValue("@div", Division);
                        cmd.Parameters.AddWithValue("@attendance", attendanceStatus);
                        cmd.Parameters.AddWithValue("@remarks", "Test Remarks!");
                        cmd.Parameters.AddWithValue("@status", "1");
                        cmd.Parameters.AddWithValue("@rem", "0");
                        int i = cmd.ExecuteNonQuery();
                        if (i > 0)
                        {
                            ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Data saved successfully, Thank-You!');", true);
                            chkAttendance.Checked = false;
                            using (SqlConnection GetAbsentCon = new SqlConnection(constring))
                            {
                                string TodaysDate = DateTime.Now.ToString("yyyy-MM-dd");
                                GetAbsentCon.Open();
                                using (SqlCommand GetAbsentCmd = new SqlCommand("select mobile from studentattendance where  convert(varchar(10), attdate, 120) = '" + TodaysDate + "' and attendance='Absent'", GetAbsentCon))
                                {
                                    SqlDataReader dr = GetAbsentCmd.ExecuteReader();
                                    while (dr.Read())
                                    {
                                        numbers = dr["mobile"].ToString().TrimStart('0');
                                        SendSMS();
                                    }
                                    GetAbsentCon.Close();
                                }
                            }
                        }
                        else
                        {
                            ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('There was some error in connection');", true);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Response.Write("<script language='javascript'>alert('" + Server.HtmlEncode(ex.Message.ToString()) + "')</script>");
                }
                finally
                {
                    conInsert.Close();
                }
            }
        }
    }
}

Please help me out from this awkward situation..

Answer 1

May be you should add a condition for studentId or StudentNo in this Sql Statement.

using (SqlCommand GetAbsentCmd = new SqlCommand("select mobile from studentattendance where  convert(varchar(10), attdate, 120) = '" + TodaysDate + "' and attendance='Absent'", GetAbsentCon))