CODEWITHSUNDEEP
c#mysql.net.net-core
Yoru Ten
Yoru Ten

Reputation: 29

C# mysql query return error "Could not find specified column in results"

In this code, I cannot find the problem, can someone help me?

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();

The error I get is:

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

But the table exists and the columns as well, the query in phpMyAdmin works smoothly, so how do I check the field id in C#?

Upvotes: 0

Views: 854

Answers (2)

Serge
Serge

Reputation: 43860

You have

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

So you id became start

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

Upvotes: 1

Steve
Steve

Reputation: 216243

You are returning a single row with a single column. This is the exact scenario where you use ExecuteScalar instead of an ExecuteReader.
Just remember that your query could return null (there is a WHERE and an HAVING condition). So you need to check the return from ExecuteScalar before converting it to an integer

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);
}

Upvotes: 0

Related Questions