CODEWITHSUNDEEP
c#mysql
xyz
xyz

Reputation: 549

MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

I am trying to query the MySQL database from a c# application. Below is the code , here I am using parameterized query

 public static void ValidateName(MySqlConnection conn,List<Employee> EmpList, string Group)
 {
   string selectQuery = "Select Name from Employee where Group = @Group  AND @Name in (FirstName, LastName);";
   using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
    {
     for (int i = 0; i < EmpList.Count; i++)
      {
        cmd.Parameters.Add("@Group", MySqlDbType.VarChar).Value = Group;
        cmd.Parameters.Add("@Name", MySqlDbType.VarChar).Value = EmpList[i].Name;
        var reader = cmd.ExecuteReader();
        List<string> lineList = new List<string>();
        while (reader.Read())
        {
            lineList.Add(reader.GetString(0));
        }
        if (lineList.Count <=0)
        {
           WriteValidationFailure(EmpList[i], "Failed");
        }
}       
}

But the above code is throwing error in below line saying 

 cmd.Parameters.Add("@Group", MySqlDbType.VarChar).Value = Group;

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll' @Group has already been defined

Upvotes: 0

Views: 2424

Answers (1)

sujith karivelil
sujith karivelil

Reputation: 29026

This is happening because you are adding the same set of parameters in each iterations. You can either clear then in each iteration or else add them before starting the loop and change the value of existing parameter during each iteration. I think second option would be great. One more thing I have to specify here is about the reader, you have to use reader as an using variable so that each time it will get disposed at the end of the using block and your code works fine. Which means you can try something like this:

using (MySqlCommand cmd = new MySqlCommand(selectQuery, conn))
{
    cmd.Parameters.Add(new MySqlParameter("@Group", MySqlDbType.VarChar));
    cmd.Parameters.Add(new MySqlParameter("@Name", MySqlDbType.VarChar));
    for (int i = 0; i < EmpList.Count; i++)
    {
        cmd.Parameters["Group"].Value = group;
        cmd.Parameters["Name"].Value = EmpList[i].Name;
        // rest of code here
       using (MySqlDataReader reader = cmd.ExecuteReader())
       {
           // Process reader operations 
       }

    }
}

Upvotes: 2

Related Questions