Member Avatar for millus

I am inserting values into a database but i get an error, it says the variable name @emp_login has already been declared. How do i resolve this? This is my code

`SqlDataReader readerReference = cmd_Reference.ExecuteReader();
 SqlCommand cmd_Insert = Connexion_Insert.CreateCommand();


string insert = "INSERT INTO EMP_tracking(emp_login, emp_dom_code, emp_domain,emp_surname") 
                 VALUES (@emp_login,@emp_dom_code,@emp_domain,@emp_surname);


        int loginIndex = readerReference.GetOrdinal("a column");
        int DomCodeLoginIndex = readerReference.GetOrdinal("a column");
        int dom_domain = readerReference.GetOrdinal("dom_domaine"); 
        int emp_surname = readerReference.GetOrdinal("a column");


 while (readerReference.Read())//from a different query
      {
cmd_Insert.Parameters.AddWithValue("@emp_login", readerReference.GetInt32(loginIndex));
cmd_Insert.Parameters.AddWithValue("@emp_dom_code", readerReference.GetInt32(DomCodeLoginIndex));
cmd_Insert.Parameters.AddWithValue("@emp_domain", readerReference.GetString(dom_domain));
cmd_Insert.Parameters.AddWithValue("@emp_surname", readerReference.GetString(emp_surname));

cmd_Insert.ExecuteNonQuery();
}
`
  • 3 Contributors
  • 3 Replies
  • 188 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by ChrisHunter

Dani AI

Generated

Short diagnosis and a safer fix.

The error came from adding the same named parameters repeatedly to the same SqlCommand. ’s suggestion to clear the Parameters collection fixes that symptom, and correctly pointed out the malformed SQL literal — make sure the INSERT text is valid and actually assigned to the command. For a more robust and efficient solution, create the SqlCommand once, add each parameter exactly once (with an explicit SqlDbType), call Prepare, then inside the reader loop only set parameter .Value and ExecuteNonQuery. That avoids repeated Add/Remove, prevents the duplicate-name error, and is clearer about types.

Example pattern (replace names/types/sizes to match your schema):

using (var cmd_Insert = Connexion_Insert.CreateCommand())
{
    cmd_Insert.CommandText = "INSERT INTO EMP_tracking (emp_login, emp_dom_code, emp_domain, emp_surname) VALUES (@emp_login, @emp_dom_code, @emp_domain, @emp_surname)";

    cmd_Insert.Parameters.Add("@emp_login", SqlDbType.Int);
    cmd_Insert.Parameters.Add("@emp_dom_code", SqlDbType.Int);
    cmd_Insert.Parameters.Add("@emp_domain", SqlDbType.NVarChar, 100);
    cmd_Insert.Parameters.Add("@emp_surname", SqlDbType.NVarChar, 200);
    cmd_Insert.Prepare();

    while (readerReference.Read())
    {
        cmd_Insert.Parameters["@emp_login"].Value = readerReference.IsDBNull(loginIndex) ? (object)DBNull.Value : readerReference.GetInt32(loginIndex);
        cmd_Insert.Parameters["@emp_dom_code"].Value = readerReference.IsDBNull(DomCodeLoginIndex) ? (object)DBNull.Value : readerReference.GetInt32(DomCodeLoginIndex);
        cmd_Insert.Parameters["@emp_domain"].Value = readerReference.IsDBNull(dom_domain) ? (object)DBNull.Value : readerReference.GetString(dom_domain);
        cmd_Insert.Parameters["@emp_surname"].Value = readerReference.IsDBNull(emp_surname) ? (object)DBNull.Value : readerReference.GetString(emp_surname);

        cmd_Insert.ExecuteNonQuery();
    }
}

Extra notes: prefer explicit parameter types over AddWithValue (it can choose suboptimal types), verify your reader ordinals and types to avoid InvalidCastException, and wrap connections/commands/readers in using blocks. For large inserts consider a transaction, table-valued parameter or SqlBulkCopy instead of per-row ExecuteNonQuery for much better performance.

Recommended Answers

All 3 Replies

Member Avatar for deceptikon

Clear your parameters collection when the values change, you're doubling them up and the variable name is a unique key:

while (readerReference.Read())
{
    cmd_Insert.Parameters.Clear();
    cmd_Insert.Parameters.AddWithValue("@emp_login", readerReference.GetInt32(loginIndex));
    cmd_Insert.Parameters.AddWithValue("@emp_dom_code", readerReference.GetInt32(DomCodeLoginIndex));
    cmd_Insert.Parameters.AddWithValue("@emp_domain", readerReference.GetString(dom_domain));
    cmd_Insert.Parameters.AddWithValue("@emp_surname", readerReference.GetString(emp_surname));
    cmd_Insert.ExecuteNonQuery();
}
Member Avatar for millus

Thanks deception it works.

Member Avatar for ChrisHunter

Unless it was just a typo when you were posting you have a closed the speach marks too early in your SQL statement. It needs to be at the end of the statement instead of after emp_surname.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.