VB - sql query to reader or datatable then to text boxes

i have created a SQL query that returns a table:

i have, in Visual Basic (visual studio 2015) returned this data to a datatable using this code:

Dim cn As New SqlConnection
Dim conn As New SqlConnection("connection string here")

Dim cmd As New SqlCommand
Dim reader As SqlDataReader
Dim da2 As New SqlDataAdapter
cmd.CommandText = "select ........ "
cmd.CommandType = CommandType.Text
cmd.Connection = conn

reader = cmd.ExecuteReader()`

*here is where i am lost *

i need code to select the number of Issues ( column 2 ) based on the value in column 1 (family)

my issue is that some times, one of the families may not HAVE a record, so the dataset could have only families a, c, and d...the next time it could have families b, and d. so i cannot really use row(#). i need code that i can reference the family name and then return the number of issues, put that number into a string, and then place that value in a text box.

i'm good with assigning the string to a variable and then into the text box...its the in-between that i can't figure out!

Thank you!

Solution

I'd suggest to use a SqlDataAdapter to fill a DataTable, then use Linq-To-DataTable to get what you need. Also, always use the using-statement:

Dim table = New DataTable()
Using conn As New SqlConnection("connection string here")
    Using da = New SqlDataAdapter("select ........ ", conn)
        da.Fill(table)
    End Using
End Using

Dim matchingFamily = From row In table.AsEnumerable()
                     Where row.Field(Of String)("Family") = "family a"
If matchingFamily.Any() Then
    Dim familyIssueCount As Int32 = matchingFamily.First().Field(Of Int32)("Issues")
    ' ... '
End If

If it's possible that multiple rows contain this family you might use a different approach since First will pick an arbitrary row. One way is using a For Each to enumerate them or use matchingFamily.Sum(Function(r) r.Field(Of Int32)("Issues")) to sum the issues.

If you don't need all records in memory you could also select only the relevant records from the database with ...WHERE Family = @family.