Error: You have specified an invalid column ordinal

Question

i'm having an issue loading data from my database to my windows form. I'm using the code below to retrieve the information through a datareader and then set the retrieved information to the appropriate labels and pictureboxes but when the AirSpace form is show, I get the exception in the title. I've researched this a bit and have come to the conclusion that this exception is given when the application tries to access a ordinal outside of the bounds but that isn't valid in this example (I don't think).

If you need any further explanation or details, please ask. Thanks in advance.

Code:

private void AirSpace_Shown(object sender, EventArgs e)
    {
        string connectionString = "datasource=localhost;port=3306;username=********;password=********";
        Login login = new Login();
        using (MySqlConnection conn = new MySqlConnection(connectionString))
        {
            using (MySqlCommand cmd = conn.CreateCommand())
            {
                string select = "SELECT username, premium, picture FROM userinfo.users WHERE username = @username;";
                //                        (0)       (1)      (2)
                conn.Open();
                cmd.CommandText = select;
                cmd.Parameters.AddWithValue("@username", login.UsernameTextBox.Text);
                using (MySqlDataReader Reader = cmd.ExecuteReader())
                {
                        While(Reader.read())
                        {
                        //Set the user's profile picture to the user's profile picture.
                        ProfilePicture.Load(Reader.GetString(2));
                        //Set the username to the user's username
                        Username.Text = Reader.GetString(0);
                        //Set the app version to the user's version
                        if (Reader.GetString(1) == "1")
                        {
                            AppVersionLabel.Text = "Premium";
                        }
                        else
                        {
                            AppVersionLabel.Text = "Free";
                        }
                     }
                }
            }
        }

@kostyan Yea, my apologies, I didn't realize that I completely overlooked the fact that I started at 1. I thought my problem was different. — Noah Cordoba, Dec 11 '13 at 18:54

Habib · Answer 1 · 2013-12-11T19:00:24.350

2

Columns oridnals starts with 0 not 1

string select = "SELECT username, premium, picture FROM userinfo.users WHERE username = @username;";
 //                        (0)       (1)      (2)

So the following line

ProfilePicture.Load(Reader.GetString(3));

should be:

ProfilePicture.Load(Reader.GetString(2));

See: 25.2.3.5. MySqlDataReader

25.2.3.5.5. GetString

Gets the value of the specified column as a String object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

EDIT:

You need to read through your DataReader like:

using (MySqlDataReader Reader = cmd.ExecuteReader())
{
    while (Reader.Read())
    {
        //Set the user's profile picture to the user's profile picture.
        ProfilePicture.Load(Reader.GetString(2));
        //Set the username to the user's username
        Username.Text = Reader.GetString(0);
        //Set the app version to the user's version
        if (Reader.GetString(1) == "1")
        {
            AppVersionLabel.Text = "Premium";
        }
        else
        {
            AppVersionLabel.Text = "Free";
        }
    }
}

edited Dec 11 '13 at 19:00

answered Dec 11 '13 at 18:51

Habib

219,104
29
407
436

Wow, I can't believe I just derped that hard. Thank you for the quick answer. – Noah Cordoba Dec 11 '13 at 18:54
Using the updated code in the question I get the exception `Invalid attempt to access a field before calling Read()` – Noah Cordoba Dec 11 '13 at 18:58
@NoahCordoba, just update the answer with some code, you need to read your DataReader through method Read like in the above code – Habib Dec 11 '13 at 19:00
Running the code while Reader.Read() is true causes the code to not do anything at all. I'm not sure why this is, maybe you could explain it to me. Also, updated the code in the question. – Noah Cordoba Dec 11 '13 at 19:03
@NoahCordoba, are you sure you are getting back something from the database ? – Habib Dec 11 '13 at 19:05
Not entirely, I know the database has the information and there doesn't seem to be anything wrong with the query though. – Noah Cordoba Dec 11 '13 at 19:08
@NoahCordoba, what is the value in the `TextBox` also copy the Command Text from debugger and try running it in SQL server, it seems you are not passing the correct value in parameter – Habib Dec 11 '13 at 19:09
I ran the Command Text in the MySQL server and it returned all of the information i'm trying to load. The value of the textbox is whatever I enter in the textbox, the application would return an error if the database didn't contain a username by the textbox's value. – Noah Cordoba Dec 11 '13 at 19:20
@NoahCordoba, just put a debug point and step through the debugger to see what is going on, I am not sure why or what exactly is wrong with your code – Habib Dec 11 '13 at 19:22
I figured it out. The value of the textbox clears itself when the form is hidden. I've realized that in order to get that value I would have to assign it to another public string and access it from my main form. Do you know the code for doing that? I can't seem to figure it out. – Noah Cordoba Dec 11 '13 at 19:54

Error: You have specified an invalid column ordinal

1 Answers1