System.IndexOutOfRangeException: the index was outside the array

Question

I am developing ATM software as a homework in which I want to know the total volume of transactions that is being processed today, for this I write the following code

public decimal getDayTransaction(int accountid, string date, string transactiontype) { decimal totalamount = 0; int i = 0; string connectionString = "Persist Security Info=False;User ID=sa; Password=123;Initial Catalog=ATMSoftware;Server=Bilal-PC"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand( "Select Amount From [Transaction] where AccountID = " + accountid + " AND CurrDate ='" + date + "' AND TransactionType = '" + transactiontype + "';", connection); connection.Open(); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { totalamount += Convert.ToDecimal(dr.GetString(i)); i++; } return totalamount; } } catch (Exception e) { return -1; } } 

But I get an exception. System.IndexOutOfRangeException: the index was outside the array, although several records are available in the database that are obtained by running the same query in the query window. But I do not know how to do this through coding.

Please help me.

Hello

Answer 1

This is because you are trying to read too many IMO columns.

  while (dr.Read()) { totalamount += Convert.ToDecimal(dr.GetString(i)); i++; } 

Who says more columns than rows? It seems you are trying to summarize a single column.

You lose time by selecting all the rows. if you are looking for SUM, use SUM(COLUMN1) instead

  SqlCommand command = new SqlCommand("Select SUM(Amount) as sAmount From [Transaction] where AccountID = " + accountid + " AND CurrDate ='" + date+ "' AND TransactionType = '" + transactiontype + "';", connection); connection.Open(); SqlDataReader dr = command.ExecuteReader(); while (dr.Read()) { totalamount += Convert.ToDecimal(dr.GetString(0)); break; // Only read once, since it returns only 1 line. } return totalamount; 

Answer 2

Change the time like this.

 while (dr.Read()) { totalamount += Convert.ToDecimal(dr.GetString(0)); } 

No need i there

Answer 3

I think the problem is in this line

  totalamount += Convert.ToDecimal(dr.GetString(i)); i++; 

Why does i increase for? you do not need to increase i

i represents column index here. You can read from one column, so you do not need to increase i .

It is also recommended that you use the value of column name instead of index

Answer 4

When you should get only one value, use SqlCommand.ExecuteScalar, which returns a single value.

 SqlCommand command = new SqlCommand("Select SUM(Amount) as TotalAmount From [Transaction] where AccountID = " + accountid + " AND CurrDate ='" + date + "' AND TransactionType = '" + transactiontype + "';", connection); connection.Open(); decimal totalAmount = (decimal)command.ExecuteScalar(); 

To avoid SQL injection attacks, consider using parameterized commands. You can find information on the sample Execute.Scalar and Parametrized commands in the MSDN documentation for SqlCommand .