Sql query execution inside a for loop

Question

for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
    string toemail = GridView1.Rows[i].Cells[2].Text;
    string FID1 = GridView1.Rows[i].Cells[0].Text;
    GridViewRow row = GridView1.Rows[i];
    CheckBox Ckbox = (CheckBox)row.FindControl("CheckBoxMark1");
    if (Ckbox.Checked == true)
    {
        sendMail(toemail);

        //ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Email send Succesfully')</script>");
        ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Email sent Succesfully on " + test + "')</script>");

        cn1.Open();
       //cmd4.CommandText = "Insert into TrackingFaculty_det  (EmailsentDate)  values (@EmailsentDate) WHERE FID=@FID";
       cmd4.CommandText = "update TrackingFaculty_det SET EmailsentDate=@Email WHERE FID=@FID  ";
       cmd4.CommandType = CommandType.Text;
       cmd4.Connection = cn1;

       cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);
       cmd4.Parameters["@Email"].Value = sdt;
       cmd4.Parameters.Add("@FID",SqlDbType.VarChar,10);
       cmd4.Parameters["@FID"].Value = FID1;
       cmd4.ExecuteNonQuery();
       cn1.Close();
    }
}

//This is a part of c# code along with asp.net

The problem arises if whenever the for loop gets executed the updation should be done in the database but when for loop executes more than once the update query will give a error as The variable name '@Email' has already been declared. Variable names must be unique within a query batch or stored procedure..Any solution for this?

Answer 1

Try clearing command parameters -

cmd4.Parameters.Clear();

and then add the parameter

cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);

So your final code would be-

cmd4.Parameters.Clear();  //  <- Just add this line
cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);

Answer 2

check the Run Stored Procedure with table data as parameter

or second is create string with below example and exec(string) in sql server

sqlstr= "update TrackingFaculty_det SET EmailsentDate=[e-mail] WHERE FID=1; update TrackingFaculty_det SET EmailsentDate=[e-mail] WHERE FID=@FID ;pdate TrackingFaculty_det SET EmailsentDate=[e-mail] WHERE FID=1;"

Answer 3

Just move the invariant part of your loop outside the loop. That means, the initialization of the SqlCommand, the opening of the connection and the creation of the parameters collection.
Then inside the loop just change the value of the parameters and execute. (This will be always faster than recreating the same list of parameters inside the loop)

   SqlCommand cmd4 = new SqlCommand("update TrackingFaculty_det " + 
                                    "SET EmailsentDate=@Email WHERE FID=@FID", cn1);
   cn1.Open();
   cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);
   cmd4.Parameters.Add("@FID",SqlDbType.VarChar,10);
   for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
   {
       .....
       if (Ckbox.Checked == true)
       {
          ....
          cmd4.Parameters["@Email"].Value = sdt;
          cmd4.Parameters["@FID"].Value = FID1;
          cmd4.ExecuteNonQuery();
       }
   }
   cn1.Close();

Answer 4

cmd4 object should be refreshed everytime.

  cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);
  cmd4.Parameters["@Email"].Value = sdt;
  cmd4.Parameters.Add("@FID",SqlDbType.VarChar,10);
  cmd4.Parameters["@FID"].Value = FID1;

your adding the same parameters more than one time to parameter list of "cmd4"

Clear your paramters before adding once again same parameters

cmd4.Parameters.Clear();