user1858332 user1858332 - 2 months ago 8
ASP.NET (C#) Question

The variable name '@VarName' has already been declared Issue

I am inserting multiple items into table based on a selection from drop down list. When I select one item from the drop down then everything works fine but when I select multiple items then I get this error

The variable name '@CompName' has already been declared. Variable names must be unique within a query batch or stored procedure.


what am i doing wrong? thanks
here is my code

protected void DV_Test_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
foreach (ListItem listItem in cblCustomerList.Items)
{
if (listItem.Selected)
{
string Name= listItem.Value;
sqlcon.Open();
string CompName= ((TextBox)DV_Test.FindControl("txtCompName")).Text.ToString();
string Num = ((TextBox)DV_Test.FindControl("txtNum")).Text.ToString();
SqlCommand cmd = new SqlCommand("select CompNamefrom MyTable where CompName= '" + CompName+ "' and Num = '" + Num + "' and Name= '" + Name+ "' ", sqlcon);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
lblmsg.Text = "Not Valid";
}
else
{
dr.Close();

sqlcmd.CommandText = "INSERT INTO MyTable(CompName, Num, Name) VALUES(@CompName, @Num, @Name)";
sqlcmd.Parameters.Add("@CompName", SqlDbType.VarChar).Value = CompName;
sqlcmd.Parameters.Add("@Num", SqlDbType.VarChar).Value = Num;
sqlcmd.Connection = sqlcon;
sqlcmd.ExecuteNonQuery();
DV_Test.ChangeMode(DetailsViewMode.Insert);
sqlcon.Close();
}
sqlcon.Close();
}

}
}

Answer

You are adding the paramters @CompName and @Num to your query every time through the loop.

You need to move the call to Add outside the loop and use the following to update them:

sqlcmd.Parameters["@CompName"].Value = CompName;
sqlcmd.Parameters["@Num"].Value = Num;

So your code becomes:

sqlcmd.CommandText = "INSERT INTO MyTable(CompName, Num, Name) VALUES(@CompName, @Num, @Name)";
sqlcmd.Parameters.Add("@CompName", SqlDbType.VarChar);
sqlcmd.Parameters.Add("@Num", SqlDbType.VarChar);

foreach (ListItem listItem in cblCustomerList.Items)
{
    if (....)
    {
        ....
    }
    else
    {
        dr.Close();

        sqlcmd.Parameters["@CompName"].Value = CompName;
        sqlcmd.Parameters["@Num"].Value = Num;
        sqlcmd.Connection = sqlcon;                 
        sqlcmd.ExecuteNonQuery();
        DV_Test.ChangeMode(DetailsViewMode.Insert);
        sqlcon.Close();
    }

}
Comments