There is no Row at Position -1

Question

I am receiving this sql error there is no row at position - 1.

This is what I have done.

void showData(int index)
    {

        Connection con = new OrderManager.Connection();
        SqlDataAdapter sda = new SqlDataAdapter("Select * from [MasterDatabase].[dbo].[Neworder] Where OrderID = '" + TxtBox_OrderID.Text + "'", con.ActiveCon());

        dt = new DataTable();
        sda.Fill(dt);

        TxtBox_OrderID.Text = dt.Rows[index][0].ToString();
        ClearTextBoxes();
        dataGridView1.Rows.Clear();
        foreach (DataRow item in dt.Rows)
        {
            int n = dataGridView1.Rows.Add();

            dataGridView1.Rows[n].Cells[0].Value = item["OrderID"].ToString();
            dataGridView1.Rows[n].Cells[1].Value = item["Date"].ToString();
            dataGridView1.Rows[n].Cells[2].Value = item["Customer_Name"].ToString();
            dataGridView1.Rows[n].Cells[3].Value = item["ProductID"].ToString();
            dataGridView1.Rows[n].Cells[4].Value = item["Product_Name"].ToString();
            dataGridView1.Rows[n].Cells[5].Value = item["Product_Color"].ToString();
            dataGridView1.Rows[n].Cells[6].Value = item["Product_PCs"].ToString();
            dataGridView1.Rows[n].Cells[7].Value = item["Product_Cutting"].ToString();
            dataGridView1.Rows[n].Cells[8].Value = item["Product_TotalYards"].ToString();
        }
        label12.Text = "Row Count: " + dt.Rows.Count.ToString();

    }

I want to display only those records while navigating whose OrderID is equals to the order ID in the database.

Why don't you just set the DataSource property of the grid with your table (and select just the fields you need in the grid) ? — Steve, Jun 01 '17 at 18:46
However this error happens because one of your DataRow field names is mispelled. Check them — Steve, Jun 01 '17 at 18:48
What is `dataGridView1` *bound to*? what is it a view *over*? it is possible that it *can't add*. Is there a DataSource, for example? — Marc Gravell, Jun 01 '17 at 18:49
not related to your question, but **please, please** don't concatenate input into SQL; there is a huge SQL Injection risk in your code - and I strongly suspect in a lot more of it... use parameters — Marc Gravell, Jun 01 '17 at 18:50
@MarcGravell Please tell me the correct way. I found a error in my code. I am matching Order ID with nothing. I wanted to show only those records in the grid whose Order ID is same. so first I initialized my first order id to Order0001, now it works. — Patrick, Jun 01 '17 at 18:51
@Patrick well, I hate to say it but if I were to show you "the proper way" (according to my subjective opinion), it wouldn't involve `SqlDataAdapter` or `DataTable`. Sorry, but it wouldn't. It would be: `int orderId = ...; var rows = conn.Query("Select * from [MasterDatabase].[dbo].[Neworder] Where OrderID = @orderId", new {orderId}).AsList();` using "dapper", where `SomeType` is a `class` that matches your data shape. — Marc Gravell, Jun 01 '17 at 18:58
actually sorry i am still getting my error, there is no row at position 1 despite i have 3 rows in it. — Patrick, Jun 01 '17 at 19:07
Where do you call this function? But more importat. What is the value of the variable index passed as argument to this method? And exactly on which line do you get the error? — Steve, Jun 01 '17 at 20:26

score 0 · Answer 1 · answered Jun 01 '17 at 20:33

I think your error happens on this line

  TxtBox_OrderID.Text = dt.Rows[index][0].ToString();

this is not an SQL error but a simple index out of the bounds of the array.
For some reasons, when you try to use a row that is not included in the Rows collection of the datatable you get this error message instead of the less ambiguous IndexOutOfRangeException. This message comes if you pass some value for the index variable that is less than zero or bigger than the number of rows in the datatable dt.
You don't have any check on the number of rows returned by the query and thus is possible that your query doesn't return any record or simple the value of index is -1

void showData(int index)
{

    Connection con = new OrderManager.Connection();
    SqlDataAdapter sda = new SqlDataAdapter(".......", con.ActiveCon());
    dt = new DataTable();
    sda.Fill(dt);

    // Protect the access to the rows collection of the table...
    if(index < dt.RowsCount && index >= 0)
    {
        TxtBox_OrderID.Text = dt.Rows[index][0].ToString();
        // the code that fills the datagrid
    }
    else
    {
        // Message for your user about a record not found
    }
}

As a side note, please follow ASAP the advice given to parameterize your query. You will avoid Sql Injection and parsin problems

this query `SqlDataAdapter sda = new SqlDataAdapter("Select * from Neworder", con.ActiveCon());` shows me this http://i63.tinypic.com/imts7d.jpg — Patrick, Jun 02 '17 at 15:54
and this query `SqlDataAdapter sda = new SqlDataAdapter("Select * from [MasterDatabase].[dbo].[Neworder] Where OrderID = '" + TxtBox_OrderID.Text + "'", con.ActiveCon());` shows me this error http://i63.tinypic.com/szcxm1.jpg upon navigation gives me this: http://i68.tinypic.com/jv3dra.jpg — Patrick, Jun 02 '17 at 15:58
`private void btn_NextRecord_Click(object sender, EventArgs e) { pos++; if (pos < dt.Rows.Count) { showData(pos); } else { MessageBox.Show("End", "Last Record"); pos = dt.Rows.Count - 1;}}` When Pressed "Next Record", in debug mode. pos is 1 so condition 1 < dt.rows.count which comes 1) fails. and says last record, despite my database has 5 rows. can you explain this? — Patrick, Jun 02 '17 at 16:03
Sorry but your images don't load. For the pos problem I can't say. I can only suggest to use a breakpoint inside the showData and check what value ispassed to it — Steve, Jun 02 '17 at 16:47

There is no Row at Position -1

1 Answers1