OK so right after I said I couldn't think of a way to avoid it, I thought of one. The appropriateness of the solution depends on what you're trying to do, of course. But you can avoid repeating the item for the change from one to another by using a dictionary and processing each one identically. It removes the need to track a 'current' item and handle the transition and therefore the need to manage the tail end when there's no transition. It also, as a side-effect, removes the need for the items to be sorted by name which will make the select run faster. There is the added storage cost in the dictionary but depending on your needs... anyway, here you go.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim peopleCarsDict As New Dictionary(Of String, List(Of String))
Try
Using conn As New SqlConnection("Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=Tester;Persist Security Info=True;Integrated Security=true")
conn.Open()
Using cmd As New SqlCommand("SELECT * FROM PeopleCars", conn)
Using rdr = cmd.ExecuteReader()
If rdr.HasRows Then
While rdr.Read
AddToDict(peopleCarsDict, rdr.Item("FirstName").ToString(), rdr.Item("CarType").ToString())
End While
End If
End Using
End Using
End Using
Catch ex As Exception
' report error to user
End Try
' now anything can be done with the dictionary
For Each kvp As KeyValuePair(Of String, List(Of String)) In peopleCarsDict
Diagnostics.Debug.WriteLine(kvp.Key + " has " + kvp.Value.Count + " cars")
Next
End Sub
Private Sub AddToDict(ByRef dict As Dictionary(Of String, List(Of String)), key As String, val As String)
Dim valArray As New List(Of String)
' if new person then create dict entry
If Not dict.ContainsKey(key) Then
dict.Add(key, New List(Of String))
End If
' get the proper array and add the val
If dict.TryGetValue(key, valArray) Then
valArray.Add(val)
Else
' something went wrong here, we couldn't get the array!!
Throw New Exception("Could not get array")
End If
End Sub
BTW - the dictionary could alternatively be a List(Of Person) where Person is a custom class with a name and a List(Of String) cars.
Mike