C# mysql 查询返回错误"Could not find specified column in results"

Question

在这段代码中，我找不到问题，有人能帮我吗?

int getids;
using (var SqlCommand = new MySqlCommand($"SELECT a.id+1 AS start FROM table AS a, table AS b WHERE a.id < b.id GROUP BY a.id HAVING start < MIN(b.id) LIMIT 1", Program.SQL.conn))
{
var check = SqlCommand.ExecuteReader();
if (check.HasRows)
{
check.Read();
getids = Convert.ToInt32(check["id"]);
check.Close();

我得到的错误是:

Exception IndexOutOfRangeException: Could not find specified column in results: id

但是表存在和列，在phpMyAdmin的查询工作顺利，所以我如何检查c#中的字段id ?

Answer 1

你有

"SELECT a.id+1 AS start..."

所以你的id变成了start

getids = Convert.ToInt32(check["start"]);

Answer 2

返回的是单行单列。这正是使用ExecuteScalar而不是ExecuteReader的场景。
请记住，您的查询可能返回null(有一个WHERE和HAVING条件)。因此，在将ExecuteScalar的返回值转换为整数

之前，需要检查它的返回值。

int getids;
using (var SqlCommand = new MySqlCommand($"SELECT a.id+1 AS start FROM table AS a, table AS b WHERE a.id < b.id GROUP BY a.id HAVING start < MIN(b.id) LIMIT 1", Program.SQL.conn))
{
var check = SqlCommand.ExecuteScalar();
if(check != null)
getids = Convert.ToInt32(check);
}