CODEWITHSUNDEEP
c#mysql.net-coremysql-workbench
Onkar Nirhali
Onkar Nirhali

Reputation: 15

This MySqlConnection is already in use for .net with MySQL

I have been trying to get data from the table. The code can be found below. When I test the API I get an error "This MySqlConnection is already in use". Can anyone tell me what wrong am I doing over here? Or missing to mention some more details here?

    public async Task<object> GetFormListDetails()
    {
        try
        {
            MySqlConnection con = new MySqlConnection(@"Server=Localhost;Database=HotelManagement;Uid=root;Pwd=Onkar@123;");
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select * from HotelManagement.MealType", con);

            MySqlDataReader tableData = cmd.ExecuteReader();

            var data = cmd.ExecuteNonQuery();
            var count = tableData.FieldCount;
            var myString = String.Empty;
            using (tableData)
            {
                while (tableData.Read())
                {
                    for (var i = 0; i < count; i++)
                    {
                        myString = tableData.GetValue(i).ToString();
                    }
                    tableData.Close();
                    return await Task.FromResult(new ResponseModel(ResponseCode.OK, myString, null));
                }
                con.Close();
                return await Task.FromResult(new ResponseModel(ResponseCode.Error, "", "Faild to add data"));
            }
            
        }

        catch (Exception ex)
        {
            return await Task.FromResult(new ResponseModel(ResponseCode.Error, ex.Message, null));
        }
    }

The stack trace shows the below details.

 at MySqlConnector.Core.ServerSession.StartQuerying(ICancellableCommand command) in /_/src/MySqlConnector/Core/ServerSession.cs:line 294
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__69.MoveNext() in /_/src/MySqlConnector/MySqlCommand.cs:line 271
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 101
   at Hotel.Controllers.RoomController.<GetFormListDetails>d__2.MoveNext() in /Users/onkar/Projects/hm/HM/Controllers/RoomController.cs:line

Upvotes: 0

Views: 1228

Answers (1)

Glenn Kees
Glenn Kees

Reputation: 106

Please comment out this line of code as such and try it again:

//var data = cmd.ExecuteNonQuery();

Not 100% certain without running the code myself but, I believe that is the issue. In your code, you are already calling cmd.ExecuteReader() to retrieve your data. MySql seems to have an issue with interleaving these database calls.

Upvotes: 1

Related Questions