Looping through SqlDataReader returns same row twice

Question

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)
                connObj.Open()
                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
                        dbupTable.Rows.Add(row)
                    End While

                End Using
                connObj.Close()
            End Using
        End Using
    Next
    UpdateDeetsView.DataSource = dbupTable
End Sub

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

There is no need to move the rows one by one to a datatable. dbupTable.Load(readerObj.ExecuteReader) will fill the table for you. If you have that table elsewhere, you could just use a DataView of it. But by all means, turn on Option Strict — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Apr 29, 2016 at 18:40
Even if just an SA, always use parameters to avoid SQL Injection and formatting issues. — LarsTech
– LarsTech, Commented Apr 29, 2016 at 18:41
This is not a sql-server question, please use tags properly. — FLICKER
– FLICKER, Commented Apr 29, 2016 at 18:47
@Plutonix thanks for the tip! But, I replaced the entire While loop with dbupTable.Load(cmdObj.ExecuteReader) and it still shows two results. What would Option Strict do for me? — user1806800
– user1806800, Commented Apr 29, 2016 at 18:50

Joel Coehoorn · Accepted Answer · 2016-04-29 21:49:49Z

2

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())
        cn.Open()
        UpdateDeetsView.DataSource = cmd.ExecuteReader()
    End Using
End Sub

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

Also note that I'm only looking at one entry in dbupdateidlist. The real source of your old bug may have been to have the the ID in that list twice.

edited Apr 29, 2016 at 21:49

answered Apr 29, 2016 at 19:45

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1806800 Over a year ago

Thanks a ton! This worked and suddenly only getting one row.

paparazzo Over a year ago

@user1806800 Contradicts your statement "I've run it in SMSS and I get the one result"

Joel Coehoorn Over a year ago

@Paparazzi I think the original problem was the ID value was in the dbupdateidlist list twice. Thus it was running the sql statement twice without clearing the datatable in between.

paparazzo Over a year ago

@JoelCoehoorn I asked the OP to test in SSMS and OP commented to me 1 row was returned

Joel Coehoorn Over a year ago

@Paparazzi Exactly. Notice this line from the question: For Each str As String In dbupdateidlist. There were two values in dbupdateidlist. So each row is returned by the query once, but the query ran twice. So the real fix in my answer is either the use of .First() to only get one entry, or assigning directly to the Datasource, which would clear items from any previous run.

|

paparazzo · Accepted Answer · 2016-04-29 19:16:13Z

0

There is no way readerObj.Read is failing to move to the next record

get the output of this and run it is SSMS

"select upTableId, title, classification, description, " +  
"releasedate, severity, articlenumber, url from updatedetails where updateid = '" + str + "'"

answered Apr 29, 2016 at 19:16

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

8 Comments

user1806800 Over a year ago

You may have misunderstood my question, it's returning two rows when only one exists. I've run it in SMSS and I get the one result, but if it's run by the reader I get the same row twice in the DataGridView.

paparazzo Over a year ago

@user1806800 That image is NOT proof that loop returned two rows. Trace it in debug mode and no way it loops twice if query returns one row. Knock, knock - if you call that twice than that is the results you would get.

Joel Coehoorn Over a year ago

@user1806800 I'd doublecheck the assumption that only one row exists

paparazzo Over a year ago

Knock, knock. You state "looping through through SqlDataReader returns the same row twice". That is false. The loop is not return the same row twice. If it is being called twice then you are calling it twice. The answer is yes you are doing something wrong - that loop is doing exactly was it is supposed to do.

user1806800 Over a year ago

Then what is? Again you're just saying I'm doing something wrong not offering any advice or possible solutions. Obviously, I came here knowing I'm doing something wrong. If I'm asking the question incorrectly the suggest an alternative or don't answer, this is just wasting our time.

|

Collectives™ on Stack Overflow

Looping through SqlDataReader returns same row twice

2 Answers 2

6 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related