none
There is already an open DataReader associated with this Connection which must be closed first. - Error in MySQL RRS feed

  • Question

  • Hello everyone. It seems this issue is a common one, but I've not found anything similar enough to what's happening to me.

     

    I've posted my code as-is, with comments as to where the error occurs.

     

    The following code seems to be what's causing the issue. If I set the Value to null instead of "%", it works.

                        if (searchField == "birthdate") {
              //FOLLOWING LINE CAUSES ERROR
                            msParams.Value = "%"; //+searchValue.ToString() + '%';
                        } else {
                            msParams.Value = searchValue;
                        }

     

    My stored procedure has the parameter as a varchar(11), and the stored procedure works in SQLyog.

     

    I just added the parameter today, and have encountered this problem.

     

    Thanks in advance.

     

    C# source code:

     

    public PagedDataSource Search(string searchField, string searchType, string searchValue, int maximumRows, int startRowIndex) {
            MySqlConnection connection = new MySqlConnection(connectionString);
            string selectCommand = "GetParticipantInfo";

            MySqlCommand command = new MySqlCommand(selectCommand, connection);
            try {
                MySqlDataAdapter daNISRA = new MySqlDataAdapter();

                MySqlParameter partParam = new MySqlParameter();
                partParam.ParameterName = "?partId";
                partParam.Value = null;
                command.Parameters.Add(partParam);

                MySqlParameter firstNameParam = new MySqlParameter();
                firstNameParam.ParameterName = "?firstName";
                firstNameParam.Value = null;
                command.Parameters.Add(firstNameParam);

                MySqlParameter lastNameParam = new MySqlParameter();
                lastNameParam.ParameterName = "?lastName";
                lastNameParam.Value = null;
                command.Parameters.Add(lastNameParam);

                MySqlParameter districtCodeParam = new MySqlParameter();
                districtCodeParam.ParameterName = "?districtCode";
                districtCodeParam.Value = null;
                command.Parameters.Add(districtCodeParam);

                MySqlParameter familyIDParam = new MySqlParameter();
                familyIDParam.ParameterName = "?familyId";
                familyIDParam.Value = null;
                command.Parameters.Add(familyIDParam);

                MySqlParameter birthdateParam = new MySqlParameter();
                birthdateParam.ParameterName = "?birthdate";
                birthdateParam.Value = null;
                command.Parameters.Add(birthdateParam);

                MySqlParameter msParams = new MySqlParameter();

                switch (searchType) {
                    case "B":
                        msParams.ParameterName = "?" + searchField;
                        msParams.Value = searchValue + '%';
                        break;
                    case "C":
                        msParams.ParameterName = "?" + searchField;
                        msParams.Value = '%' + searchValue + '%';
                        break;
                    case "E":
                        msParams.ParameterName = "?" + searchField;
                        if (searchField == "birthdate") {
              //FOLLOWING LINE CAUSES ERROR
                            msParams.Value = "%"; //+searchValue.ToString() + '%';
                        } else {
                            msParams.Value = searchValue;
                        }
                        break;
                    default:
                        msParams.ParameterName = "?abc";
                        msParams.Value = "";
                        break;

                }
                command.Parameters.Add(msParams);
                daNISRA.SelectCommand = command;
                daNISRA.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DataSet dsParticipants = new DataSet();

    //FOLLOWING LINE IS WHERE ERROR OCCURS OF "There is already an open DataReader associated with this Connection which must be closed first."
                daNISRA.Fill(dsParticipants, "Participants");

                connection.Close();
                PagedDataSource pdsParticipants = new PagedDataSource();
                pdsParticipants.DataSource = dsParticipants.Tables["Participants"].DefaultView;
                pdsParticipants.AllowPaging = false;
                connection.Close();
                daNISRA.Dispose();
                return pdsParticipants;
            } catch (Exception e) {
                connection.Close();
                throw new Exception(command.CommandText.ToString() + "<br/>" + e.Message + "<br/>" + e.InnerException);
            }
            connection.Close();
            connection = null;


        }

    Thursday, February 21, 2008 10:53 PM

All replies

  • 1)  Can you try changing the parms from "%" to "'%'"

    2)  Make sure Connection Pooling is enabled in the connection string that no other instance of your apps has opened and left it open

     

    Laddie

    Friday, February 22, 2008 2:28 PM
  •  Laddie wrote:

    1)  Can you try changing the parms from "%" to "'%'"

     

    If I use "'%'" (single-quote inside double-quote), I get the same error.

     

    If I use '%' (single-quote only), I get the following error: Only byte arrays and strings can be serialized by MySqlBinary

     

     Laddie wrote:

    2)  Make sure Connection Pooling is enabled in the connection string that no other instance of your apps has opened and left it open

     

    Laddie

     

    How do I do Connection Pooling with MySQL?

     

    The oddest thing, IMHO, is that setting the parameter to a NULL value does not give me an error.

    Friday, February 22, 2008 3:18 PM
  • Check this bug report to MySQL and the replies for some possible solutions to your problem.

     

    http://bugs.mysql.com/bug.php?id=7248

     

    Friday, February 22, 2008 4:07 PM
  • I've decided to create my own DataSet, instead of using the "Fill" function, as I'll have more control over it.

     

    Thanks to everyone who replied.

    Monday, February 25, 2008 2:41 PM