ERROR: Index was outside the bounds of the array in a data set C# and T-SQL

Question

Currently I am struggling to create a dataset in C# using Visual Studio, this will eventually be used to create a chart that show the retention time of users to groups (Like a clock in and out system)

Unfortunately whilst debugging I am brought to the attention of an error

Index was outside the bounds of the array

Below is my code creating the data set, I was wondering if anyone could spot any small errors with this code that I cannot.

Thank you.

  {
        string connectionString = null;

        SqlConnection connection ;
        SqlCommand command ;

        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet RetDs = new DataSet();

        int Counter = 0;

        string Chrt_NamesSql = null;
        string Chrt_RetenSql = null;

        connectionString = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = G:\NEA\GardenRegister\GardenRegister\bin\Debug\GardenRegister.mdf; Integrated Security = True";

        Chrt_NamesSql = "SELECT Name FROM Member";
        Chrt_RetenSql = "SELECT RetentionTime FROM Attendance";

        connection = new SqlConnection(connectionString);

        try
        {
            connection.Open();

            command = new SqlCommand(Chrt_NamesSql, connection);
            adapter.SelectCommand = command;
            adapter.Fill(RetDs, "Member");

            adapter.SelectCommand.CommandText = Chrt_RetenSql;
            adapter.Fill(RetDs, "Attendance");

            adapter.Dispose();
            command.Dispose();
            connection.Close();

            // retrieve first table data 
            for (Counter = 0; Counter <= RetDs.Tables[0].Rows.Count - 1; Counter++)
            {
                MessageBox.Show(RetDs.Tables[0].Rows[Counter].ItemArray[0] + " -- " + RetDs.Tables[0].Rows[Counter].ItemArray[1]);
            }

            // retrieve second table data 
            for (Counter = 0; Counter <= RetDs.Tables[1].Rows.Count - 1; Counter++)
            {
                MessageBox.Show(RetDs.Tables[1].Rows[Counter].ItemArray[0] + " -- " + RetDs.Tables[1].Rows[Counter].ItemArray[1]);
            }
        }

When asking a question about an exception, you might want to highlight the line at which the exception is thrown. This makes it easier for others to diagnose the problem. — C.Evenhuis
– C.Evenhuis, Commented Feb 18, 2018 at 12:27

C.Evenhuis · Accepted Answer · 2018-02-18 12:27:04Z

4

You only select one column from each table, then you try to access ItemArray[1]. You'll have to change your queries to include the other column you are trying to retrieve.

For example, if you wanted to include the "Id" column of the "Member" table, use:

SELECT Name, Id FROM Member

And the same for the "Attendance" table, of course.

answered Feb 18, 2018 at 12:27

C.Evenhuis

26.5k2 gold badges60 silver badges73 bronze badges

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

1 Comment

2185069 Over a year ago

Thank you very much, that quick fix solved it. I have used a message box to display what errors are shown and now all the data is sent through the try catch nicely with no errors, now I can progress to adding this to the chart. Thank you again

Collectives™ on Stack Overflow

ERROR: Index was outside the bounds of the array in a data set C# and T-SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related