Index was outside the bounds of the array SqlDataReader.GetDecimal

Question

I want to sum up my Amount column, but I receive this error message

Additional information: Index was outside the bounds of the array.

Here is the definition of the Balance table from SQL database:

CREATE TABLE [dbo].[Balance]
(
    [BalanceID] [int] IDENTITY(1,1) NOT NULL,
    [Sn] [nvarchar](50) NULL,
    [Description] [nvarchar](1000) NULL,
    [Date] [date] NULL,
    [Amount] [decimal](8, 0) NULL
)

Here is the code C# that accesses the table via SQL inline

cn.Open();

SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select sum(Amount) from Balance where Sn=@sn and Date Between @SD and @ED";

cmd.Parameters.Add(new SqlParameter("@sn", txtSn.Text));
cmd.Parameters.Add(new SqlParameter("@SD", DTPStart.Text));
cmd.Parameters.Add(new SqlParameter("@ED", DTPEnd.Text));
SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    Debt = reader.GetDecimal(4);
}

reader.Close();
cn.Close();

Any help will be much appreciated!

If you only want a single value, why not use ExecuteScalar ? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 24, 2017 at 8:12

Andrey Korneyev · Accepted Answer · 2017-05-24 08:16:04Z

2

Method GetDecimal gets the value of column having zero-based index provided as argument of method call.

You're returning only one column in your query Select sum(Amount) from Balance, but trying to achieve fifth column in reader.GetDecimal(4)

So change your code to

Debt = reader.GetDecimal(0);

Update: As it was noted in comments - in your particular case you don't need ExecuteReader at all. Since you're returning single value from server - you can use ExecuteScalar instead, which executes the query, and returns the first column of the first row in the result set returned by the query.

edited May 24, 2017 at 8:16

answered May 24, 2017 at 8:09

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

Collectives™ on Stack Overflow

Index was outside the bounds of the array SqlDataReader.GetDecimal

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related