How to display multiple rows with the same ID-CodePudding

Was hoping for some help on this matter. Title pretty much explains what I'm trying to do.

I'm using MySql Database to read the data off the UserID for the purchases they have made, however I've hit a wall because I'm stuck on how to read multiple rows with the same ID.

For exmaple

1, TestProduct
1, TestProduct2

^^^ As there are more rows populated with the same ID how can I read multiple rows?

This is what I'm currently doing and I'm aware this is not working as it's only taking/finding the first ID result it finds and using that one however, I haven't needed to populate multiple rows. So I'm at a loss

SearchUser_COMMAND.Parameters.Add("@userid", MySqlDbType.VarChar).Value = Lbl_UserID.Text

Dim reader2 As MySqlDataReader

reader2 = SearchUser_COMMAND.ExecuteReader()


If reader2.Read() Then
  Lbl_Active.Text = reader2(3)
  Lbl_ProductName.Text = reader2(2)
  Lbl_ProductExpire.Text = reader2(6)
End If

Any help on this matter would be much appreciated.

Thank very much in advance

CodePudding user response：

You could make a class to hold your data, populate a List of those objects, then use some LINQ to iterate over them.

Private Class Data
    Public Property Active As Boolean
    Public Property Name As String
    Public Property Expire As DateTime
End Class

Dim items As New List(Of Data)

If reader2.HasRows Then
    While reader2.Read()
        items.Add(New Data() With {.Name = CStr(reader2(2)), .Active = CBool(reader2(3)), .Expire = CDate(reader2(6))})
    End While
    Lbl_Active.Text = String.Join(Environment.NewLine, items.Select(Function(i) i.Active))
    Lbl_ProductName.Text = String.Join(Environment.NewLine, items.Select(Function(i) i.Name))
    Lbl_ProductExpire.Text = String.Join(Environment.NewLine, items.Select(Function(i) i.Expire))
End If
' maybe clear labels otherwise

For a reader with three items, this should result in something like this

Lbl_Active:

True
True
True

Lbl_ProductName:

name1
name2
name3

Lbl_ProductExpire:

date1
date2
date3

I took the liberty to assume the data types based on the names. You may have all strings in the database (you shouldn't) but then you should use strings.