Not getting all records in mysql in c#-CodePudding

I have a wordpress site that has a MySQL database. I created a custom table to create this page https://www.my-pocket.cc/test-store-list/ I am creating a c# desktop app to change the tables. The problem is under the site name I am only getting the last record not the two of them. I am using the MySqlDataReader. I copied the select statement into MySQL and get the right answers (see SQL select statement) I also included the output I am getting and the code.

sql select statments c# output

enter code here

string connStr = "server=127.0.0.1;user=root;database=mypocket;port=3306;password=green2Apple#"; MySqlConnection conn = new MySqlConnection(connStr); conn.Open();

        try
        {
            
            MySqlCommand cmd = new MySqlCommand("select Heading_id,Heading_Name, Heading_Order,Group_Class from button_headings order by Heading_Order", conn);


            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string connStr2 = "server=127.0.0.1;user=root;database=mypocket;port=3306;password=green2Apple#";
                MySqlConnection conn2 = new MySqlConnection(connStr2);
                conn2.Open();
                txtOutput.Text  = $"{reader.GetString("Heading_Name")} ";
                string StoreId = $"{reader.GetString("Group_Class")}";
                txtOutput.Text  = "\r\n";
                try
                {
                    MySqlCommand cmd2 = new MySqlCommand("select Site_Url, FullName FROM siteinfo  where StoreId = "   StoreId   " and showsite = 0", conn2);
                    MySqlDataReader reader1 = cmd2.ExecuteReader();
                    txtOutput.Text  = "\t";
                    if (reader1.Read()) {

                        while (reader1.Read())

                        {
                            txtOutput.Text  = "\t";
                            txtOutput.Text  = $"{reader1.GetString("FullName")}";
                            txtOutput.Text  = "\r\n";
                        }
                    }
                    else
                    {
                        txtOutput.Text  = "\t";
                        txtOutput.Text  = "No Records";
                        txtOutput.Text  = "\r\n";
                    }
                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error Getting Records"   ex.ToString());
                }


            }



        }
        catch (Exception ex)
        {
            MessageBox.Show("Error Getting Records"   ex.ToString());
        }

        btnReturn.Focus();
    }
}

CodePudding user response：

you are calling read() twice but discarding one result

if (reader1.Read()) {
    while (reader1.Read())

The if statement will read the first record, and then the while loop will read the second (and any subsequent) records and actually display them.

I would get rid of the if statement, and create boolean variable that is set to true when entering the loop. If the variable is false, then you know that no records have been retrieved and you can display the message for no records found.

bool hasRecords = false;   

while (reader1.Read())
{
    hasRecords = true;
    txtOutput.Text  = "\t";
    txtOutput.Text  = $"{reader1.GetString("FullName")}";
    txtOutput.Text  = "\r\n";
}

if(!hasReords)
{
    txtOutput.Text  = "\t";
    txtOutput.Text  = "No Records";
    txtOutput.Text  = "\r\n";
}