CODEWITHSUNDEEP
sqlvb.netdatatabledatareader
Fredderf81
Fredderf81

Reputation: 29

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

i have created a SQL query that returns a table:

FAMILY | ISSUES
family a | 11
family b | 5
family c | 17
family d | 28

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!

Upvotes: 0

Views: 2165

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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.

Upvotes: 2

Gordon Bell
Gordon Bell

Reputation: 13633

Private Sub GetFamilyIssues(ByVal FamilyName As String)
    Dim cn As New SqlConnection("connection string here")
    cn.Open()

    Dim sql As String = "select * from FamilyIssues where FAMILY = @Family"

    Dim cmd As New SqlCommand(sql, cn)
    cmd.Parameters.AddWithValue("@Family", FamilyName)

    Dim reader As SqlDataReader = cmd.ExecuteReader

    txtIssues.Text = ""
    If reader.HasRows Then
        txtIssues.Text = reader("ISSUES").ToString()
    End If

    cn.Close()
End Sub

Upvotes: -1

Related Questions