How a commit a loop of inserting

[ad]

I use loop to insert 12 record into a table:
But the for_Loop only loop once and throw an error:
[System.Data.SqlClient.SqlException] = {"The variable name '@PID' has
already been declared. Variable names must be unique within a query batch or
stored procedure."}

How can I do ?

//=========================================================
SqlConnection cnn = DM.cnn;
SqlTransaction trans;
SqlCommand cmd = new SqlCommand();
if (cnn.State == ConnectionState.Closed)
cnn.Open();
trans = cnn.BeginTransaction();
cmd.Connection = cnn;
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
string sSql = "Insert into Sight (PID, GradeID, Sem) values (@PID,
@GradeID, @Sem)";
cmd.CommandText = sSql;
string sPID = Request.QueryString["PID"].ToString();
try
{
for (int i = 1; i < 13; i++)
for (int j = 1; i < 3; i++)
{
cmd.Parameters.AddWithValue("PID", sPID);
cmd.Parameters.AddWithValue("GradeID", i);
cmd.Parameters.AddWithValue("Sem", j);
cmd.ExecuteNonQuery();
}
trans.Commit();

}
catch (Exception ex)
{
//Trace.Write(ex.Message);
e.Cancel = true;
trans.Rollback();
}

 

[Guest]

ad,

In the loop, before adding the parameters:

cmd.Parameters.Clear

Kerry Moorman

 

[Jim Hughes]

I use loop to insert 12 record into a table:
But the for_Loop only loop once and throw an error:
[System.Data.SqlClient.SqlException] = {"The variable name '@PID' has
already been declared. Variable names must be unique within a query batch
or
stored procedure."}

How can I do ?

//=========================================================
SqlConnection cnn = DM.cnn;
SqlTransaction trans;
SqlCommand cmd = new SqlCommand();
if (cnn.State == ConnectionState.Closed)
cnn.Open();
trans = cnn.BeginTransaction();
cmd.Connection = cnn;
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
string sSql = "Insert into Sight (PID, GradeID, Sem) values (@PID,
@GradeID, @Sem)";
cmd.CommandText = sSql;
string sPID = Request.QueryString["PID"].ToString();
try
{
for (int i = 1; i < 13; i++)
for (int j = 1; i < 3; i++)
{
cmd.Parameters.AddWithValue("PID", sPID);
cmd.Parameters.AddWithValue("GradeID", i);
cmd.Parameters.AddWithValue("Sem", j);
cmd.ExecuteNonQuery();
}
trans.Commit();

}
catch (Exception ex)
{
//Trace.Write(ex.Message);
e.Cancel = true;
trans.Rollback();
}

You only need to add the parameters once, not each time through the loop.

Then just reset the values assigned to each paramter in the loop.

cmd.Parameters.Add("PID");
cmd.Parameters.Add("GradeID");
cmd.Parameters.Add("Sem");
try
{
for (int i = 1; i < 13; i++)
for (int j = 1; i < 3; i++)
{
cmd.Parameters("PID") = sPID;
cmd.Parameters("GradeID") = i;
cmd.Parameters("Sem") = j;
cmd.ExecuteNonQuery();
}
trans.Commit();

}