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

ad,

In the loop, before adding the parameters:

cmd.Parameters.Clear

Kerry Moorman


"ad" wrote:

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

"ad" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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();

}