MySqlException on ExecuteReader by Selecting UserID(PK)

Question

I try to Delete a Row from the Database on the phpAdmin the Query is working fine, but when I execute it with the

Code:

MySqlCommand getUserID = new MySqlCommand("SELECT UserID FROM User", connection);

MySqlDataReader reader = getUserID.ExecuteReader();

I get the

Error:

Destination array is not long enough to copy all the items in the collection. Check array index and length.

I Insert the to deleting user before without any trouble.

The Database has an UserID with properties Unique,Int(Length 9) and Auto-Increment and a UserName from type Char.

My Question is:

Why I can't receive the userID and how can I receive it ?

Edit

I can't receive any integer or date data only varchar.

Here is the Database creation query: Creation Query

Answer 1

The exception means that you are getting an unexpected result. The method ExecuteNonQuery tries to execute an query that returns no rows, and returns a integer displaying the amount of rows edited in the database. So it's trying to put the array of results into an integer field, which isn't possible.

So to answer your question, the cause of the error is the query. For select queries you should use the ExecuteReader() method.

Answer 2

Firstly since you want to retrieve a single value (UserID) you could use ExecuteScalar here:

MySqlCommand getUserID = new MySqlCommand("SELECT UserID FROM `User`", connection);
connection.Open();
int userId = (int)getUserID.ExecuteScalar();
connection.Close();

Secondly User is a reserved keyword, so you need to use backticks around table name to make it explicit:

MySqlCommand getUserID = new MySqlCommand("SELECT UserID FROM `User`", connection);

Answer 3

The Problem is the Database MySqlConnection can´t get Int or Date values if they are not unsigned, that also means the Exception:

"Destination array is not long enough to copy all the items in the collection. Check array index and length."