Row doesnot exists in DB- Exception

Question

We have a dataset dsActualCost which fetches query value: MonthName in one column and Cost in another.
We have a code to display data in table format.

 For MonthIndex = 0 To dsMonth.Tables(0).Rows.Count - 1
   Dim tdMonthlycost As New TableCell
   If dsActualCost.Tables(0).Rows.Count > 0 Then
     If IsDBNull(dsActualCost.Tables(0).Rows(MonthIndex).Item(1)) Then         
           tdMonthlycost.Text = 0
      Else
           tdMonthlycost.Text = dsActualCost.Tables(0).Rows(MonthIndex).Item(1)
      End If
    Else
      tdMonthlycost.Text = 0
    End If
       trBody.Cells.Add(tdMonthlycost)
  Next  

This code works fine when
we have data in all rows
we have no data at all

But it gives exception for all other cases. I tried to debug and issue arises when it searches for any row which is not available in DB.

Say the dataset has only 2 rows for Feb month,so when it executes line
IsDBNull(dsActualCost.Tables(0).Rows(2).Item(1))
It throws Db null exception.

please suggest how should i handle it. the loop will run for 12 months.

Answer 1

Shouldn't you be checking:

If dsActualCost.Tables(0).Rows.Count > 0 And
   MonthIndex < dsActualCost.Tables(0).Rows.Count

Without that check, you could index out of the Rows array if MonthIndex refers to a position that isn't present.

Answer 2

Here is the code now. Do let me know if you have any suggestions:

Dim Flag As Integer = 0
For MonthIndex = 0 To dsMonth.Tables(0).Rows.Count - 1
Dim tdMonthly As New TableCell
Dim CostMonth As Integer
Dim CostValue As Integer
If (dsActualCost.Tables(0).Rows.Count > 0) Then
If (Flag <> dsActualCost.Tables(0).Rows.Count) Then
CostMonth = dsActualCost.Tables(0).Rows(Flag).Item(1)
CostValue = dsActualCost.Tables(0).Rows(Flag).Item(0)
If (dsMonth.Tables(0).Rows(MonthIndex).Item(0) = CostMonth Or (dsMonth.Tables(0).Rows(MonthIndex).Item(0) - 1 = CostMonth)) Then
tdMonthly.Text = CostValue
Flag = Flag + 1
Else
tdMonthly.Text = 0
End If
Else
tdMonthly.Text = 0
End If
Else
tdMonthly.Text = 0
End If
trBody.Cells.Add(tdMonthly)
Next

The month in dsMonth dataset is an integer and shows only even number( like Jan-2, Feb-4 etc). But the month in dsActual shows either of 1,2 for Jan and 3,4 for feb. That is the reason i had put
If (dsMonth.Tables(0).Rows(MonthIndex).Item(0) = CostMonth Or (dsMonth.Tables(0).Rows(MonthIndex).Item(0) - 1 = CostMonth))

Please let me know your suggestions if any.