user1806800 user1806800 - 1 year ago 183 Question

Looping through SqlDataReader returns same row twice

I'm making a VB.NET (4.6) Windows form application that collects info on our servers and allows us to do reports on it. It's coming together nicely but I've run into an issue I can't figure out. One part of the project is a service that queries the info on available Windows updates from WSUS and then stores them in an SQL database - that part works fine. I'm now trying to present this data in a DataGridView using an SqlDataReader to query the info from the database and fill up a DataTable with the response. The problem is that when I use the reader, it puts the same record in the DataTable twice. I'm not sure what I'm doing wrong, and I'm sure it's something super simple. Perhaps one of you folks can spot the problem?

Note: Earlier in the application, the updateid's are stored as unique strings in a list called dbupdateidlist, the results are stored in a DataTable called dbupTable, and the datagridview I'm trying to update is called UpdateDeetsView.

Public Sub getUpdateDetails()

For Each str As String In dbupdateidlist
Dim commGetUpdateDetails As String = "select upTableId, title, classification, description, " +
"releasedate, severity, articlenumber, url from updatedetails where updateid = '" + str + "'"
Using connObj As New SqlClient.SqlConnection(connectionString)
Using cmdObj As New SqlClient.SqlCommand(commGetUpdateDetails, connObj)
Using readerObj As SqlClient.SqlDataReader = cmdObj.ExecuteReader
While readerObj.Read

dbuptabid = readerObj("uptableid")
dbuptitle = readerObj("title")
dbupclass = readerObj("classification")
dbupdesc = readerObj("description")
dbupreleasedate = readerObj("releasedate")
dbupseverity = readerObj("severity")
dbuparticlenumber = readerObj("articlenumber")
dbupurl = readerObj("url")

row = dbupTable.NewRow()
row("uptableid") = dbuptabid
row("title") = dbuptitle
row("classification") = dbupclass
row("description") = dbupdesc
row("releasedate") = dbupreleasedate
row("severity") = dbupseverity
row("articlenumber") = dbuparticlenumber
row("url") = dbupurl
End While

End Using
End Using
End Using
UpdateDeetsView.DataSource = dbupTable
End Sub

Forgive the likely terrible code, I'm an SA not a dev...

Answer Source

Try this:

Public Sub getUpdateDetails()
    Dim sql As String = _    
        "SELECT DISTINCT upTableId, title, classification, description, releasedate, " & _
            " severity, articlenumber, url " & _
       " FROM updatedetails " & _ 
       " WHERE updateid = @updateID"

    Using cn  As New SqlClient.SqlConnection(connectionString), _
          cmd As New SqlClient.SqlCommand(sql, cn)

        cmd.Parameters.Add("@updateID", SqlDbType.Int).Value = Int32.Parse(dbupdateidlist.First())
        UpdateDeetsView.DataSource = cmd.ExecuteReader()
    End Using
End Sub

Note the use of DISTINCT and the complete lack of any explicit loops whatsoever.

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