在phpmyAdmin中查询完美运行时出现以下错误

Question

我有以下代码，我得到系统。IndexOutOfRangeException: '无法在结果:类别中找到指定的列。'执行时的Category_id '。有什么问题吗?

public List<Category> GetCat()
{
try
{
List<Category> categories = new List<Category>();
con.Open();

var cmd = con.CreateCommand();
cmd.CommandText = "SELECT categories.category_id, categories.category, subcategory.subCatName, subcategory.subCategory_id  from subcategory inner join categories on categories.category_id = subcategory.category_id";
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{

Category category = new Category(dr.GetInt32("categories.category_id"), dr.GetString("categories.category"), dr.GetInt32("subcategory.subCategory_id"), dr.GetString("subcategory.subCatName"));
categories.Add(category);

}
return categories;

}
catch (Exception)
{
throw;
}
finally
{
this.con.Close();
}
}

Answer 1

错误不是由SQL生成的，而是来自这一行;

Category category = new Category(dr.GetInt32("categories.category_id"), dr.GetString("categories.category"), dr.GetInt32("subcategory.subCategory_id"), dr.GetString("subcategory.subCatName"));

此时数据是本地的，列名不有表前缀。因此，错误告诉你它找不到这些列名。

试试这个…

Category category = new Category(dr.GetInt32("category_id"), dr.GetString("category"), dr.GetInt32("subCategory_id"), dr.GetString("subCatName"));

(顺便说一下，如果在phpMyAdmin中运行SQL，您将看到实际的列名)