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?

Use SP and TableValueParameters or just don't re use the Command — Sriram Sakthivel
– Sriram Sakthivel, Commented Oct 9, 2013 at 9:52
Clear the parameters of cmd4 after cmd4.ExecuteNonQuery(); — Saghir A. Khatri
– Saghir A. Khatri, Commented Oct 9, 2013 at 9:55

Steve · Accepted Answer · 2013-10-09 10:04:27Z

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();

Microsoft DN · Accepted Answer · 2013-10-09 09:54:18Z

2

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);

answered Oct 9, 2013 at 9:54

Microsoft DN

10.1k10 gold badges54 silver badges74 bronze badges

Comments

andy · Accepted Answer · 2013-10-09 09:53:59Z

1

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();

answered Oct 9, 2013 at 9:53

andy

6,0792 gold badges29 silver badges50 bronze badges

1 Comment

user2660112 Over a year ago

will you please elaborate it?

Community · Accepted Answer · 2017-05-23 12:30:24Z

1

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 [email protected] WHERE FID=1; update TrackingFaculty_det SET [email protected] WHERE FID=@FID ;pdate TrackingFaculty_det SET [email protected] WHERE FID=1;"

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Oct 9, 2013 at 9:57

MANISHDAN LANGA

2,2377 gold badges29 silver badges44 bronze badges

Collectives™ on Stack Overflow

Sql query execution inside a for loop

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related