您好，我正在使用这段代码从 MySQL 数据库的 5 个不同表中获取我需要的数据。

private void goDateBtn_Click(object sender, EventArgs e)
        {
            reportList.Items.Clear();
            var db = new DBConnect();
            MySqlCommand cmd = null;
            MySqlDataReader dr = null;
            double totalsales = 0;
            try
            {
                if (db.OpenConnection() == true)
                {

                    string cmdstr = "SELECT ol.*, o.*, m.* "+
                                    "FROM orderlist ol "+
                                    "INNER JOIN orderdb o ON ol.order_ID = o.order_ID "+
                                    "INNER JOIN menudb m ON ol.menu_ID = m.menu_ID "+
                                    "INNER JOIN recipelist r ON r.menu_ID = m.menu_ID "+
                                    "INNER JOIN "+
                                    "( SELECT stock_ID, SUM(stock_pricePerPiece) menu_cost "+
                                        "FROM stocksdb "+
                                        "GROUP BY stock_ID )"+
                                    "s ON r.stock_ID = s.stock_ID "+
                                    "WHERE o.order_date >= #" + fromDate.Value.Date + "# AND " +
                                        "o.order_date <= #" + toDate.Value.Date + "#";
                    cmd = new MySqlCommand(cmdstr, db.mycon);
                    dr = cmd.ExecuteReader();
                    string[] info = new string[20];
                    while (dr.Read())
                    {
                        info[1] = (dr["order_ID"].ToString());
                        info[2] = (dr["order_date"].ToString());
                        info[3] = (dr["menu_name"].ToString());
                        info[4] = (dr["menu_cost"].ToString());
                        info[5] = (dr["menu_price"].ToString());
                        info[6] = (Convert.ToDouble(info[5]) - Convert.ToDouble(info[4])).ToString("#0.00");
                        this.reportList.Items.Add(new ListViewItem(new string[] { info[1], info[2], info[3], info[4], info[5], info[6] }));

                        totalsales += Convert.ToDouble(info[6]);
                    }
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                db.CloseConnnection();
            }
            totalSalesTxtBox.Text = totalsales.ToString("#0.00");
            MessageBox.Show("SALES REPORT FINISHED!");
        }

我真的不知道我做错了什么，尤其是在最后一个 INNER JOIN 应该将 中的所有 stock_pricePerPiece 加起来的 sql 字符串中stocksdb 表，其中股票用于同一菜单，因此在 recipedb 表中具有相同的 menu_ID。

我在 dr = cmd.ExecuteReader(); 上遇到错误

最佳答案

我意识到这个问题的答案并不容易解释，但它与我试图通过命令字符串虚拟地重新创建的列有关。我所做的以及解决问题的方法是将要检索的特定列放入 SELECT 查询中。

工作代码:

 private void goDateBtn_Click(object sender, EventArgs e)
        {
            reportList.Items.Clear();
            var db = new DBConnect();
            MySqlCommand cmd = null;
            MySqlDataReader dr = null;
            double totalsales = 0;
            try
            {
                if (db.OpenConnection() == true)
                {

                    string cmdstr = "SELECT DISTINCT ol.*, o.order_ID, o.order_date, o.order_status, m.*, mc.menu_cost " +
                                    "FROM  orderlist ol " +
                                        "INNER JOIN orderdb    o ON ol.order_ID = o.order_ID " +
                                        "INNER JOIN menudb     m ON ol.menu_ID  = m.menu_ID " +
                                        "INNER JOIN " +
                                            "(SELECT SUM(s.stock_pricePerPiece * r.recipe_quantity) AS menu_cost, m.menu_ID " +
                                                "FROM recipelist r " +
                                                    "INNER JOIN stocksdb s ON r.stock_ID = s.stock_ID " +
                                                    "INNER JOIN menudb m ON r.menu_ID= m.menu_ID " +
                                                "GROUP BY r.menu_ID " +
                                             ")" +
                                        "mc ON m.menu_ID = mc.menu_ID " +
                                    "WHERE o.order_date >= '" + fromDate.Value.Date.ToString("yyyy-MM-dd") + " 00:00:00" + "' AND " +
                                        "o.order_date <= '" + toDate.Value.Date.ToString("yyyy-MM-dd") + " 23:59:59" + "' AND " +
                                        "o.order_status = 'COMPLETED'";

                    if (menuCheckBox.Checked == true)
                    {
                        cmdstr = cmdstr + " AND m.menu_name = '" + menuBox.Text + "'";
                    }

                    cmd = new MySqlCommand(cmdstr, db.mycon);
                    dr = cmd.ExecuteReader();

                    string[] info = new string[20];
                    while (dr.Read())
                    {
                        info[1] = (dr["order_ID"].ToString());
                        info[2] = (dr["order_date"].ToString());
                        info[3] = (dr["menu_name"].ToString());
                        info[4] = (dr["order_quantity"].ToString());
                        info[5] = Convert.ToDouble(dr["menu_cost"].ToString()).ToString("#0.00");
                        info[6] = Convert.ToDouble(dr["menu_price"].ToString()).ToString("#0.00");
                        info[7] = ((Convert.ToDouble(dr["menu_price"].ToString()) * Convert.ToDouble(info[4])) - (Convert.ToDouble(dr["menu_cost"].ToString()) * Convert.ToDouble(info[4]))).ToString("#0.00");
                        this.reportList.Items.Add(new ListViewItem(new string[] { info[1], info[2], info[3], info[4], info[5], info[6], info[7] }));

                        totalsales += Convert.ToDouble(info[7]);
                    }
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                db.CloseConnnection();
            }
            totalSalesTxtBox.Text = totalsales.ToString("#0.00");
            MessageBox.Show("SALES REPORT FINISHED!");
        }

关于c# - 如何解决 "Index was outisde the bound of the array"错误？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14065990/