Problem / Question with MySql

[Erlendftw]

Hello there Daniweb community, today i encountered a problem while developing a Desktop Application in C# With Mysql (MySql.Data.MySqlClient).

Im not using ODBC.

The situation is the following:

When NOT executing two queries, the data gets processed and everything works fine.

However, once there are two queries, problems appear.
Its hard for me to describe the situation, but please take a look at the code:

For your info: I have checked indexes etc, and they are correct. All data is going into a ListView.

Not working

private void act_update_inbox()
        {
            MySqlCommand mysql_cmd = connection.CreateCommand();
            MySqlDataReader mysql_reader; //Reader for the message info

            MySqlCommand mysql_cmd_sender = connection.CreateCommand();
            MySqlDataReader mysql_reader_sender;


            //Define the user list request / query
            mysql_cmd.CommandText = "SELECT * FROM `msgs`";

            //Pass data from the results of mysql_cmd onto the reader
            mysql_reader = mysql_cmd.ExecuteReader();

            while (mysql_reader.Read())
            {
                //First find the senders username, to do that we have to query the users table.

                string senderName = ""; 


                mysql_cmd_sender.CommandText = "SELECT * FROM `users` WHERE `id`='" + mysql_reader.GetInt32(1) + "'";
                

                mysql_reader_sender = mysql_cmd_sender.ExecuteReader();
                while (mysql_reader_sender.Read())
                {
                    senderName = mysql_reader_sender.GetString(3);
                }


                //Add (Convert first)
                lv_innboks.Items.Add("Les"); //Link to message
                //Ok, we have created a new row, now lets find its ID
                int itemnr = lv_innboks.Items.Count - 1; //Because it starts at 0
                //We got it, now insert subitems.
                lv_innboks.Items[itemnr].SubItems.Add(senderName); //SENDER 
                lv_innboks.Items[itemnr].SubItems.Add(Convert.ToString(mysql_reader.GetString(3))); //Subject 

                lv_innboks.Items[itemnr].SubItems.Add(Convert.ToString(mysql_reader.GetString(5))); //Date
                lv_innboks.Items[itemnr].SubItems.Add(Convert.ToString(mysql_reader.GetString(6))); //Time
            }
            
        }

Working

private void act_update_inbox()
        {
            MySqlCommand mysql_cmd = connection.CreateCommand();
            MySqlDataReader mysql_reader; //Reader for the message info

            //Define the user list request / query
            mysql_cmd.CommandText = "SELECT * FROM `msgs`";

            //Pass data from the results of mysql_cmd onto the reader
            mysql_reader = mysql_cmd.ExecuteReader();

            while (mysql_reader.Read())
            {

                //Add (Convert first)
                lv_innboks.Items.Add("Les"); //ID OF PM
                //Ok, we have created a new row, now lets find its ID
                int itemnr = lv_innboks.Items.Count - 1; //Because it starts at 0
                //We got it, now insert subitems.
                lv_innboks.Items[itemnr].SubItems.Add(""); //SENDER (sender-id in this test)
                lv_innboks.Items[itemnr].SubItems.Add(Convert.ToString(mysql_reader.GetString(3))); //Subject 

                lv_innboks.Items[itemnr].SubItems.Add(Convert.ToString(mysql_reader.GetString(5))); //Date
                lv_innboks.Items[itemnr].SubItems.Add(Convert.ToString(mysql_reader.GetString(6))); //Time
            }
            
        }

Thanks in advance :)

[hericles]

If you are getting an error please include it in the post; but one solution would be to put the results from the first reader into a dataTable instead of keeping the reader open - so you would be better off using a dataAdapter to fill the dataTable. Then loop through the dataTable using the second reader as you have it here. I think closing the first reader as soon as you can might fix the problem.
Also don't use SELECT * from the database if you don't need all the data. It's lazy and bad practice:)

[Momerath]

The problem is that you have an open read operation on the connection when you try to execute another read operation. You need two connection objects if you want to have two 'read' operations open at the same time.

[Erlendftw]

Thank you both for your answers. Ill do some research on dataTable's. :)