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

Question

I have built a search function that will be searching by ID and by last name.

This is my code so far:

SqlCommand cmd = new SqlCommand("SELECT [ID],[Name],[LastName],[FirstName],[FinalGrade] FROM DATABASE WHERE ID = @ID OR LastName = @lname");

using (SqlConnection con = new SqlConnection(conString))
{
    cmd.Connection = con;
    con.Open();

    cmd.Parameters.AddWithValue("@ID", ID);
    cmd.Parameters.AddWithValue("lname",lname);

    /*if (ID == null)
    {
        unitsParam.Value = DBNull.Value;
    }*/

    if(String.IsNullOrEmpty(ID))
    {
        cmd.Parameters.AddWithValue("@ID", DBNull.Value);
    }

    if (String.IsNullOrEmpty(lname))
    {
        cmd.Parameters.AddWithValue("@lname", DBNull.Value);
    }

    using (SqlDataReader sdr = cmd.ExecuteReader())
        if (sdr.HasRows)
        {
            while (sdr.Read())
            {
                int i = 0;

                // vm.SearchResults.Add(new ApqcrDirSearch
                ApqcrDirModel aRec = new ApqcrDirModel();

                aRec.ID = sdr.IsDBNull(i) ? null : sdr.GetString(i);
                i++;

                aRec.Name = sdr.IsDBNull(i) ? null : sdr.GetString(i);
                i++;

                aRec.LastName = sdr.IsDBNull(i) ? null : sdr.GetString(i);
                i++;

                aRec.FirstName = sdr.IsDBNull(i) ? null : sdr.GetString(i);
                i++;

                vm.SearchResults.Add(aRec);
            }
        }

        con.Close();
}

How do I search by both ID and LastName ? Do I have to use SqlCommandParameter? If yes, how ?

Does this answer your question? [The variable name '@' has already been declared. Variable names must be unique within a query batch or stored procedure](https://stackoverflow.com/questions/19535486/the-variable-name-has-already-been-declared-variable-names-must-be-unique-w) — TylerH, Jan 18 '23 at 16:57

score 3 · Accepted Answer · answered Nov 24 '18 at 21:06

You are adding your parameters twice. Instead of using AddWithValue, use Add, and then you can check if ID or Name are null or empty, like this:

cmd.Parameters.Add("@ID", SqlDbType.VarChar)
    .Value = string.IsNullOrEmpty(ID) ? (object)DBNull.Value : ID;

cmd.Parameters.Add("@lname", SqlDbType.VarChar)
    .Value = string.IsNullOrEmpty(lname) ? (object)DBNull.Value : lname;

Note: I'm guessing VarChar, might be a different type.

score 2 · Answer 2 · edited Sep 12 '21 at 01:41

2

can use

cmd.Parameters.Clear();

after con.Open();

it will be clear other parameters

edited Sep 12 '21 at 01:41

U13-Forward

69,221
14
89
114

answered Sep 11 '21 at 20:25

Halim Bezek

331
3
5

score 0 · Answer 3 · answered Nov 24 '18 at 21:10

The reason why you are receiving the errormessage: "The variable name '@ID' has already been declared. Variable names must be unique within a query batch or stored procedure" Is because you are declaring the parameter @ID twice. You are also declaring the @lname parameter twice. Remove the following code from your method and the error should not appear again:

if(String.IsNullOrEmpty(ID))
{
    cmd.Parameters.AddWithValue("@ID", DBNull.Value);
}

if (String.IsNullOrEmpty(lname))
{
    cmd.Parameters.AddWithValue("@lname", DBNull.Value);
}

The above code does not make sense to implement. You should check if the lastname and the id parameters are empty before you enter the method. Think of it like input validation.

I removed that part of code, now its giving me this error : The parameterized query '(@ID nvarchar(4000),@lname nvarchar(4000))SELECT [ID],[Name],[La' expects the parameter '@ID', which was not supplied. — User123, Nov 24 '18 at 21:31

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

3 Answers3