C# & SQL Server : Index was outside the bounds of the array

Question

I'm trying to get data from the SQL Server database but the error state that

System.IndexOutOfRangeException: Index was outside the bounds of the array.

This is my table

My code:

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=....;Integrated Security=True;Connect Timeout=30;User Instance=True;");

SqlCommand cmd = new SqlCommand();
SqlDataReader dtr;

protected void Page_Load(object sender, EventArgs e)
{
    cmd.Connection = conn;
    conn.Open();

    cmd.CommandText = "select BicycleType from TourDisplay order by TDateTime asc";

    dtr = cmd.ExecuteReader();
    bool temp = false;

    while (dtr.Read())
    {
        TBBicycleType.Text = dtr.GetString(0);
        TBBicycleType2.Text = dtr.GetString(1);
        temp = true;
    }     
}

As you can see, the code should be fine because I got 3 data inside the table but I'm having problem on GetString(1).

Could you please include more code, I mean the query and how you are populating the dtr. Then it would be easy for us to find the issue — sujith karivelil
– sujith karivelil, Commented May 10, 2017 at 4:06
it looks like you are only selecting one column: BicycleType but trying to fetch two from the read array. Either select more columns in your sql statement, or remove the second GetString — WhiteRuski
– WhiteRuski, Commented May 10, 2017 at 4:21
See, in the query you are selecting one column ie, BicycleType and you are trying to access the second column using GetString() which is not yet fetched. Your issue will be fixed if you add the second column name in the query — sujith karivelil
– sujith karivelil, Commented May 10, 2017 at 4:25

WhiteRuski · Accepted Answer · 2017-05-10 05:17:49Z

2

It appears that in your select statement, you are selecting ONE column, but in the reader, you're trying to select two.

EXAMPLE: SELECT Id, BicycleType FROM TourDisplay ORDER BY TDateTime

Please either add another column to your select statement, or remove the second selector from the reader:

TBBicycleType2.Text = dtr.GetString(1);

Edit per comment

It appears that you may be trying to set two different buttons, using the first two results from your query with this while reader loop. I'd tweak it a little as follows:

protected void Page_Load(object sender, EventArgs e)
{
    cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = "SELECT BicycleType FROM TourDisplay ORDER BY TDateTime";
    dtr = cmd.ExecuteReader();
    bool temp = false;

    // Added this variable.  name it anything you like.
    bool isFirstItem = true;
    while (dtr.Read())
    {
        // After the first loop iteration, isFirstItem will be set to false
        if (isFirstItem)
        {
            TBBicycleType.Text = dtr.GetString(0);
            isFirstItem = false;
        }
        // All consequent iterations of the loop will set the second 
        // item's 'Text' property.  So if you have only two rows, it 
        // will be the second row.  If you have 20 rows, it will be the 20th row.
        else
            TBBicycleType2.Text = dtr.GetString(0);

        temp = true;
    }     
}

Although, if you're just setting two buttons using a while loop and from the database, I'd rethink your design a little?

Edit 2 per changes to the OP mentioning 3 total rows in the database

protected void Page_Load(object sender, EventArgs e)
{
    cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = "SELECT BicycleType FROM TourDisplay ORDER BY TDateTime";
    dtr = cmd.ExecuteReader();

    // Again, I'd rethink your design, but if you're just playing
    // around this should be fine
    int count = 0;
    while (dtr.Read())
    {
        switch (count)
        {
            case 0:
                TBBicycleType.Text = dtr.GetString(0);
                break;
            case 1:
                TBBicycleType2.Text = dtr.GetString(0);
                break;
            // Add whatever you want to do with the third one here
            // case 2:
            //     TBBicycleType3.Text = dtr.GetString(0);
            //     break;
         }
        count++;
    }     
}

edited May 10, 2017 at 5:17

answered May 10, 2017 at 4:24

WhiteRuski

1074 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nao Over a year ago

I see my mistake now, but I'm trying to get data only from column BicycleType to be put inside the texbox.

nao Over a year ago

I'm not using any button, but I want to retrieve the data once the page is loaded.

WhiteRuski Over a year ago

@nao the variable name doesn't matter. you can name it anything. if you refresh you can see i changed it to isFirstTextbox. Please just try it and let me know if it works. You can name it isItAKittyCat or whatever else you like.

nao Over a year ago

Thank you for your answer. It still not 100% for what I want, but I get the idea now.

Nathaniel Angeles Jr. · Accepted Answer · 2017-05-10 04:28:32Z

The index is indeed out of bounds. Since you're trying to get the second column on dtr.getString(1). To fix, remove the dtr.getString(1).

In each iteration, dtr.GetString(0) gets the next row of BicycleType. So first iteration, TBBicycleType.Text = "Time Trial". Second iteration, TBBicycleType.Text = "MTB". and so on.

SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=....;Integrated Security=True;Connect Timeout=30;User Instance=True;");
SqlCommand cmd = new SqlCommand();
SqlDataReader dtr;


protected void Page_Load(object sender, EventArgs e)
{
    cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = "select BicycleType from TourDisplay order by TDateTime asc";
    dtr = cmd.ExecuteReader();
    bool temp = false;
    while (dtr.Read())
    {
        TBBicycleType.Text = dtr.GetString(0);
        temp = true;
    }     
}

Collectives™ on Stack Overflow

C# & SQL Server : Index was outside the bounds of the array

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related