How to solve error "You have specified an invalid column ordinal"-CodePudding

Why I'm not getting the values of the column "nome_pizza"?

This is my code:

try
            {
                MySqlConnection objconect = new MySqlConnection("DATABASE=pizzaria ; port=3306 ; SERVER=localhost ; UID=root ; pwd='' ");

                objconect.Open();

                MySqlCommand objcmd = new MySqlCommand("SELECT nome_pizza FROM pedidos ORDER BY id_pedido ASC", objconect);

                MySqlDataReader reader = objcmd.ExecuteReader();

                while (reader.Read())
                {
                    textBox1.Text = reader.GetString(0);
                    textBox1.Text = reader.GetString(1);
                    textBox3.Text = reader.GetString(2);
                    textBox4.Text = reader.GetString(3);
                    textBox5.Text = reader.GetString(4);
                }

I tried so many times but it still appearing this error message:enter image description here

The textbox1 is getting the right value, but when it turns to textbox2 the error message appears

What am I doing wrong??

CodePudding user response：

You are selecting only 1 column in your SELECT query, but trying to fetch too many column values after your read, reader.GetString(1) will start giving you the error. If you want to set the value of the column nome_pizza to all the text boxes, then initialize all of them to reader.GetString(0) only. Or, were you missing to select few other columns and missed in your SELECT query, then mention those column names in your SELECT query.

CodePudding user response：

Databases produce result sets that are rectangular:

SELECT * FROM Person

Name, Age, City
---------------
Joe,  21,  Chicago
Jane, 22,  New York

That result set is 2 rows high by 3 columns wide

SELECT Name FROM Person

Name
----
Joe
Jane

That result set is 2 rows high by 1 column wide

SELECT Age, City FROM Person WHERE Name = 'Joe'

Age, City
---------
21,  Chicago

That result set is 1 row high by 2 columns wide

SELECT ... affects how many columns you get. WHERE affects how many rows you get

In c#

reader.GetString(x) gets column number x
reader.Read() moves on by one row

This means if you want to get 5 columns for 5 textboxes:

textBox1.Text = reader.GetString(0);
textBox2.Text = reader.GetString(1);
textBox3.Text = reader.GetString(2);
textBox4.Text = reader.GetString(3);
textBox5.Text = reader.GetString(4);

You have to have SELECTed at least 5 columns. You need to have 5 words after your select: SELECT Name, Age, City, Address, JobTitle FROM person (Or you need to be SELECT *'ing on a table with at least 5 columns)

5 textboxes can reasonably show a result set that is 1 row high by 5 columns wide. It doesn't make sense to use textboxes if you have more than one row. The while(reader.Read()) will loop over every row but you only get the last row data showing in the text boxes

If your result set is 5 rows high by 1 column wide (as your code suggests it might be) you need to Read() repeatedly to move the row on, and always retrieve the first column (there is only one column)

textBox1.Text = reader.GetString(0);
reader.Read();

textBox1.Text = reader.GetString(0);
reader.Read();

textBox3.Text = reader.GetString(0);
reader.Read();

textBox4.Text = reader.GetString(0);
reader.Read();

textBox5.Text = reader.GetString(0);

Get straight in your mind your rows and your columns. GetString(99) gets the 100th column. Doing reader.Read() 50 times means you're getting the data from the 50th row