Database Search Question

I've got a code using SQLClient that loops though all users in a Users table inside each loop i want to preform another search in the UserOrders table, thus doing a count of all orders.

I get this error

HTML Code:

---

There is already an open DataReader associated with this Command which must be closed first.

---

Here is my code

vb.net Code:

UserLists.Items.Clear()
            Dim connection As New SqlClient.SqlConnection(My.Settings.ConnectString)
                Dim command As New SqlClient.SqlCommand("SELECT * FROM Users ORDER BY PersonName", connection)
            connection.Open()
 
            Dim reader As SqlClient.SqlDataReader = command.ExecuteReader
            Dim Ops As String = ""
            While reader.Read
 
                Dim ID As Integer = reader("ID")
                Dim command2 As New SqlClient.SqlCommand("SELECT Count(ID) FROM UserOrders WHERE UserID = @ID", connection)
                command2.Parameters.AddWithValue("@ID", ID)
                Dim count = command2.ExecuteScalar
 
 
                Dim listitem As New ListViewItem
                listitem.Text = reader("Username")
                listitem.SubItems.Add(reader("PersonName"))
                listitem.SubItems.Add(count)
                listitem.Tag = reader("ID")
                UserLists.Items.Add(listitem)
 
            End While
            connection.Close()

Another way of doing it would be to loop through each item in my listview but my questions then are.

For Each item in UsersListView

Next

Question, how do i get the value from the 3rd or 4th coloumn and how do i update a row?

A better way to do this write a SQL statment that already counts the orders for each Person

Roughly:

Code:

---

SELECT
Prs.Id
, Prs.Name
, Prs.Surname
, Prs.Gender
, Count(Ord.OrderID) as NumberOfOrders
FROM
 Person Prs
INNER JOIN
Orders Ord
ON
Ord.PersonId = Prs.Id
GROUP BY
Prs.Id
, Prs.Name
, Prs.Surname
, Prs.Gender

---

You get that error because you have this command

Code:

---

command2.ExecuteScalar

---

executing against the same connection as

Code:

---

command.ExecuteReader

---

The join that Dnereb gives you would solve your problem.