MySQL DataReader not working

Question

User1177026165 posted

Hi

I have a MySQL stored procedure that returns a varchar and I have tested the sql, and it does return the correct results. However, when I call the Stored Procedure from my C# code the data reader has no results, and is empty. My Stored Procedure compiles without error, too. Here is my C# code, and I would welcome some help, please:

 MySqlCommand cmd = new MySqlCommand("get_menu", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new MySqlParameter("username", "ariome"));
                cmd.Parameters.Add("menulist", MySqlDbType.VarChar);
                cmd.Parameters["menulist"].Direction = ParameterDirection.Output;
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader();

                List<MenuItems> MenubarList = new List<MenuItems>();
                while (dr.Read())
                {
                    MenuItems items = new MenuItems();
                    items.ItemName = Convert.ToString(dr["itemname"]);
                    MenubarList.Add(items);
                }
                dr.Close();

                return MenubarList;

Answer 1

User269602965 posted

Show your stored procedure as well.

Answer 2

User-809550105 posted

First you should open the connection before passing parameters....

MySqlCommand cmd = new MySqlCommand("get_menu", connection);

cmd.Connection.Open();

Secondly, Dispose the objects of command and connection before returning.....

cmd.Displose();

con.close();

return MenubarList;

Answer 3

User1177026165 posted

Wrong answer I am afraid, but the problem I found was in the stored procedure itself, which I have fixed.

Thanks

Answer 4

User-809550105 posted

You did not given us the whole code with stored procedure thats why i use some other option to solve this problem.

Thanks