VB.Net how to change variable based on counter

Question

I have a For Next Loop that will connect to a database, then search through the results and assign the results to a variable. Right now I'm displaying it in the form of a message box. I'd like that variable to change with the counter

My program:

For x = 1 to 5

            dtstartdate = dtpStartDate.Value
            dtenddate = dtpEndDate.Value.AddDays(1).AddSeconds(-1)


            Try
                connetionString = "Data Source=..."
                sql = "Select * discounts"
                connection = New SqlConnection(connetionString)
                connection.Open()
                command = New SqlCommand(sql, connection)
                adapter.SelectCommand = command
                adapter.SelectCommand.CommandTimeout = 130
                adapter.SelectCommand.Parameters.AddWithValue("@StartDate", dtstartdate)
                adapter.SelectCommand.Parameters.AddWithValue("@EndDate", dtenddate)
                adapter.Fill(ds)
                connection.Close()
                connection.Dispose()

            Catch ex As Exception
                MsgBox(ex.Message)

        For Each Result As DataRow In ds.Tables(0).Rows
            Select Case Result("Report")
                Case "TOTALS"
                    Select Case Result("Description")
                        Case "Coupons", "Coupons Tax-Free", "GC"
                            sum = sum + (Result("netAmt"))
                    End Select

            End Select
        Next

Next

How would I assign "SUM" to change for each loop through. For example:

If x is 1 i'd like "Sum" to be "Sum1":

For Each Result As DataRow In ds.Tables(0).Rows
                Select Case Result("Report")
                    Case "TOTALS"
                        Select Case Result("Description")
                            Case "Coupons", "Coupons Tax-Free", "GC"
                                sum1 = sum1 + (Result("netAmt"))
                        End Select

                End Select

If x is 2 I'd like sum to be sum2

For Each Result As DataRow In ds.Tables(0).Rows
                Select Case Result("Report")
                    Case "TOTALS"
                        Select Case Result("Description")
                            Case "Coupons", "Coupons Tax-Free", "GC"
                                sum2 = sum2 + (Result("netAmt"))
                        End Select

                End Select

SO on and so forth. At the end of the program, I'll be displaying "Sum1", "Sum2", "Sum3" on a report.

Answer 1

The first thing that I would change is your query.
If I am not mistaken you are interested only in the rows where the Report field contains the word 'TOTALS' and the Description field contains the words 'Coupons' or 'Coupons Tax-Free' or 'GC'.

So you could change your query to express a WHERE condition.
This will dramatically reduce the rows returned and the complexity of the final totals.
(By the way, are you sure that you don't need to increment also the @startDate parameter?)

Second thing is the use of a List(Of Double) instead of a single variable

Dim sumTotals = new List(Of Double)()
For x = 1 to 5
   ....
   sql = "Select * discounts WHERE Report = 'TOTALS' " & _ 
                             "AND Description IN('Coupons', 'Coupons Tax-Free, 'GC'"
   ....

   Dim partialSum As Double
   For Each Result In ds.Tables(0).Rows
       partialSum = partialSum + (Result("netAmt"))
   Next
   sumTotals.Add(partialSum)
Next

Now, at the end of the For Loop you have a List of 5 double values (one for each day) that you can use as a normal array

For each x in sumTotals
   Console.WriteLine(x)
Next
....

Or apply a Linq extension

Dim grandTotal = sumTotals.Sum()

Answer 2

Use an array for your sum variable. Instead of changing the name of the variable (sum1, sum2, sum3) you would just have to change the index of the array ( sum(1), sum(2), sum(3)... which would look like sum(x) ) and store your values in those indexes.