joe joe - 1 year ago 69 Question

Creating a string list of values in a datatable

I'm trying to create a list of rates in a program where the rates are running out a month from today, so that a reminder can be sent to the user to extend them. My code for doing so is below

Dim rDt As New DataTable
Dim r1Dt As New DataTable

Using rDa = New OleDbDataAdapter("SELECT DISTINCT [Comm_Code] FROM [Acquisition Commission] WHERE DateTo=?", con)
rDa.SelectCommand.Parameters.Add("@date", OleDbType.Date).Value = Date.Today.AddMonths(1)
End Using

Dim r2Dt As New DataTable
Using r2Da = New OleDbDataAdapter("SELECT DISTINCT [Comm_Code] FROM [Commission Rates] WHERE DateTo=?", con)
r2Da.SelectCommand.Parameters.Add("@date", OleDbType.Date).Value = Date.Today.AddMonths(1)
End Using

Dim r3Dt As New DataTable
Using r3Da As New OleDbDataAdapter("SELECT DISTINCT [Comm_Code] FROM [Customer Special Rates] WHERE DateTo=?", con)
r3Da.SelectCommand.Parameters.Add("@date", OleDbType.Date).Value = Date.Today.AddMonths(1)
End Using

rDt = r1Dt.Copy

If rDt.Rows.Count > 0 Then
Dim rates As String = ""
For Each dr As DataRow In rDt.Rows
rates = dr.Item("Comm_Code") & ", "
If MsgBox("The following rates; " & rates & "are set to expire in 1 month. Would you like to automatically extend these rates by 6 months?", MsgBoxStyle.YesNo, "Extend Rates") = MsgBoxResult.No Then


However, when stepping through this code, I can see that
has 3 rows (As I expected, 1 rate from each table as a test), but the
For Each
loop only iterates once, so only 1 of the 3 items is concatenated onto the string.

Why is this, and how do I fix it?

Answer Source

You override rates each time.

rates = dr.Item("Comm_Code") & ", "

You need to change it to

rates &= dr.Item("Comm_Code") & ", "

Be wary of the extra comma.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download