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

         }
 }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

}

Method 2

Add sqlcmd.Parameters.Clear(); at the end. This way every parameter will not exist at the end of each loop.

Example:

// ...
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();
                    sqlcmd.Parameters.Clear();
//...

Method 3

My assumption here is because you’ve declared sqlcmd outside of the scope of the loop; so when you iterate over it, you could potentially be adding in the same parameter name multiple times.

And please please please fix this line: SqlCommand cmd = new SqlCommand("select CompNamefrom MyTable where CompName= '" + CompName+ "' and Num = '" + Num + "' and Name= '" + Name+ "' ", sqlcon); as it’s SQL injection waiting to happen.

Method 4

Just add this:

SqlCmd.Parameters.Clear();

in your code, before parameter adding. It will clear already added parameters list and consider your parameters as new.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0