trying to query a database

Question

Hello Fellow Programmers

I'm having a bit of a problem populating a collections class with the values from the database. Everytime I loop through a record in the WHILE DR.READ loop, the last record over writes all the other items in the collection. My returnVal collections has several of the same items despite the loop showing each individual record being added into returnVal. Thanks for any help.

Public Shared Function getStuff(ByVal sb As StringBuilder) As System.Collections.Generic.List(Of Minutes)

    Dim returnVal As New System.Collections.Generic.List(Of Minutes)

    Dim conn As New SqlConnection

    Dim cmd As New SqlCommand

    Dim dr As SqlDataReader

    conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    cmd.Connection = conn
    cmd.CommandText = sb.ToString

    Try
        conn.Open()
        dr = cmd.ExecuteReader

        While dr.Read
            Dim _minutes As New Minutes
            _minutes.Minutes = dr("minutes")
            _minutes.MinutesId = dr("minutesId")
            returnVal.Add(_minutes)
        End While


    Catch ex As Exception
        Dim _minutes As New Minutes
        _minutes.Minutes = ex.ToString
        _minutes.MinutesId = 0
        returnVal.Add(_minutes)
    End Try
    conn.Close()
    Return returnVal

End Function

This is my Minutes Class

Imports Microsoft.VisualBasic

Public Class Minutes

    Private Shared _minutesId As Integer

    Private Shared _minutes As String


    Public Property MinutesId() As Integer
        Get
            Return _minutesId
        End Get
        Set(ByVal value As Integer)
            _minutesId = value
        End Set
    End Property
    Public Property Minutes() As String
        Get
            Return _minutes
        End Get
        Set(ByVal value As String)
            _minutes = value
        End Set
    End Property
    Public Shared Function getStuff(ByVal sb As StringBuilder) As System.Collections.Generic.List(Of Minutes)
        Return MinutesDA.getStuff(sb)
    End Function
    Public Shared Function modify(ByVal sb As StringBuilder) As String
        Return MinutesDA.modify(sb)
    End Function
    Public Shared Property Id() As Integer
        Get
            Return MinutesDA.Id
        End Get
        Set(ByVal value As Integer)
            MinutesDA.Id = value
        End Set
    End Property
    Public Shared Property Index() As Integer
        Get
            Return MinutesDA.Index
        End Get
        Set(ByVal value As Integer)
            MinutesDA.Index = value
        End Set
    End Property
End Class

Answer 1

To confirm you are getting new values or the same value try this:

   Dim i as Integer
   i = 0
   While dr.Read

        Dim _minutes As New Minutes
        _minutes.Minutes = dr("minutes")
        ' _minutes.MinutesId = dr("minutesId")
        _minutes.MinutesId = i
        i = i + 1
        returnVal.Add(_minutes)
    End While

After this you can see if the MinuteId is different.

N.B. In VB6 you had to set the _minutes to Nothing at the end of the loop to get a new instance. However I wouldn't have thought that would be true in VB.NET

Answer 2

The problem is that the fields in the Minutes class are shared. There will be one instance of the fields shared by all instances of the class. Remove the "Shared" from the fields:

Private _minutesId As Integer

Private _minutes As String

This will store the values for each instance separately