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

Question

I am trying to show Employee Name on basis of Personal_No but while doing I got error

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

My code:

protected void txtEmployeeNumber_TextChanged(object sender, EventArgs e)
{
    string EmployeeNo = "";
    string constring = ConfigurationManager.ConnectionStrings["SQLDBConnection"].ConnectionString;

    SqlConnection con = new SqlConnection(constring);
    SqlCommand cmd = new SqlCommand("select Employee_Name from [138.201.225.134].[iProfile].[dbo].[tbl_Employee] WHERE Personnel_Number= @Personnel_Number", con);
    cmd.CommandType = CommandType.Text;

    foreach (GridViewRow row in grdRegister.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
        {
            EmployeeNo = (row.Cells[1].FindControl("txtEmployeeNumber") as TextBox).Text;
        }
        cmd.Parameters.AddWithValue("@Personnel_Number", EmployeeNo);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        if (dt.Rows.Count > 0)
        {
            (row.Cells[2].FindControl("txtEmployeeName1") as TextBox).Text = dt.Rows[0]["Employee_Name"].ToString();
        }
    }
}

If i tried to keep (row.Cells[2].FindControl("txtEmployeeName1") as TextBox).Text = dt.Rows[0]["Employee_Name"].ToString(); out side of foreach loop then error occurs on row

Please guide me what should i change in this code I am getting error on cmd.ExecuteNonQuery(); while my loop runs 2nd time. My text box are inside the Grid view

Answer 1

Instantiate the command inside the loop:

    protected void txtEmployeeNumber_TextChanged(object sender, EventArgs e)
    {
        string EmployeeNo = "";
        string constring = ConfigurationManager.ConnectionStrings["SQLDBConnection"].ConnectionString;
        SqlConnection con = new SqlConnection(constring);

        foreach (GridViewRow row in grdRegister.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                EmployeeNo = (row.Cells[1].FindControl("txtEmployeeNumber") as TextBox).Text;
            }

            SqlCommand cmd = new SqlCommand("select Employee_Name from [138.201.225.134].[iProfile].[dbo].[tbl_Employee] WHERE Personnel_Number= @Personnel_Number", con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Personnel_Number", EmployeeNo);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                (row.Cells[2].FindControl("txtEmployeeName1") as TextBox).Text = dt.Rows[0]["Employee_Name"].ToString();
            }
        }
    }

Answer 2

You can use Clear method at the end of each iteration. Try.like:

cmd.Parameters.Clear(); <--Add it at the end of loop

Or alternatively you can palce the code that initializes the SQlCOmmand inside the for loop, so you will always have a new parameter. Something like:

foreach (GridViewRow row in grdRegister.Rows)
{
  SqlCommand cmd = new SqlCommand("select Employee_Name from [138.201.225.134].[iProfile].[dbo].[tbl_Employee] WHERE Personnel_Number= @Personnel_Number", con);
  cmd.CommandType = CommandType.Text;
  ....

Answer 3

Add the parameter before entering the loop, then change the value as you loop through each row:

cmd.Parameters.Add(new SqlParameter("@Personnel_Number", SqlDbType.VarChar));

foreach (GridViewRow row in grdRegister.Rows)
{
     //get EmployeeNo code

     cmd.Parameters["@Personnel_Number"].Value = EmployeeNo;    

     //rest of your code
}

HTH

Answer 4

If you have the Add() or AddWithValue() inside the loop but the SqlCommand is outside it, on the second iteration it would try to add another parameter with the same parameter name.

Do note that I would choose the former rather than the later, and you should consider stop using AddWithValue

You should either:

        cmd.Parameters.Add(new SqlParameter("@Personnel_Number", SqlDbType.[Your SQL Type]));
        foreach (GridViewRow row in grdRegister.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                EmployeeNo = (row.Cells[1].FindControl("txtEmployeeNumber") as TextBox).Text;
            }
            cmd.Parameters["@Personnel_Number"].Value = EmployeeNo;
            con.Open();
            // rest of your loop
        }

or

        foreach (GridViewRow row in grdRegister.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                EmployeeNo = (row.Cells[1].FindControl("txtEmployeeNumber") as TextBox).Text;
            }
            cmd.Parameters.AddWithValue("@Personnel_Number", EmployeeNo);
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            con.Close();
            // rest of your loop
        }

Answer 5

As mentioned by @apomene you can solve your problem by adding following single line in start of each iteration as below sample

foreach (GridViewRow row in grdRegister.Rows)
{
cmd.Parameters.Clear();
//Do rest of work
}

But IMHO you don't need to do the following**

 con.Open();
 cmd.ExecuteNonQuery();
 con.Close();

You can simply pass command to data adapter and you will get data in data table using fill function.

Answer 6

You're doing some things wrong.

-- Update: I was wrong too! Thanks to @Polyfun for clearing that out for me.

1) You shouldn't update inside of the textbox edit event.

Move your code to a button. Execute SQL query every time you enter a character isn't that good.

2) You're searching for control in a loop.

EmployeeNo = (row.Cells[1].FindControl("txtEmployeeNumber") as TextBox).Text;

Find it once outside the loop - it will speed up whole process.

Regarding your question, you're parsing database response manually. Why?

Try using Dapper! It can automatically builds native C# objects from database response!
It supports variables and provides protection from SQL injections if you're using it right.
Here's a documentation for it. Also you can use Dapper Extensions for better experience.

Answers above me are kinda right, but using Dapper you don't have to control where you add parameters and where you remove them.

Here's an example for Dapper:

var result = connection.Query<SomeClass>("select * from SomeTable where UserId = @userId", new { userId });

Pretty simple, huh?