none
How can I get a queried record count to a label? RRS feed

  • Question

  • Hello guys, I have read all kinds of information on this topic. And it seems I can't get my head to place the correct combination together. I have a class that's called SQLControl.vb that is dedicated to the connection and operation of my database. This class is not my own I didn't write it, it came from youtube from a user called VB TOOLBOX so credit goes to him. Anyway, the database application works great. I want to query a record count to a label. I have tried so many different things and I failed. One thing that I keep seeing is I need to use

    result = Convert.ToInt32(command.ExecuteScalar())

    in this situation I don't know how to use it. I'll post the Control class first then the section I need help on

    I'm horrible at posting things on forums so please bear with me.

    SQLControl.vb

    Imports System.Data.SqlClient
    
    Public Class SQLControl
        Private DBCon As New SqlConnection("Server=MYSERVER;Database=MYDB;Trusted_Connection=True;")
        Private DBCmd As SqlCommand
    
        ' DB DATA
        Public DBDA As SqlDataAdapter
        Public DBDT As DataTable
        Public DBBS As BindingSource
    
        ' QUERY PARAMETERS
        Public Params As New List(Of SqlParameter)
    
        ' QUERY STATISTICS
        Public RecordCount As Integer
        Public Exception As String
    
        Public Sub New()
        End Sub
    
        ' ALLOW CONNECTION STRING OVERRIDE
        Public Sub New(ConnectionString As String)
            DBCon = New SqlConnection(ConnectionString)
        End Sub
    
        ' EXECUTE QUERY SUB
        Public Sub ExecQuery(Query As String, Optional ReturnIdentity As Boolean = False)
            ' RESET QUERY STATS
            RecordCount = 0
            Exception = ""
    
            Try
                DBCon.Open()
    
                ' CREATE DB COMMAND
                DBCmd = New SqlCommand(Query, DBCon)
    
                ' LOAD PARAMS INTO DB COMMAND
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    
                ' CLEAR PARAM LIST
                Params.Clear()
    
                ' EXECUTE COMMAND & FILL DATASET
                DBDT = New DataTable
                DBDA = New SqlDataAdapter(DBCmd)
                DBBS = New BindingSource
                RecordCount = DBDA.Fill(DBDT)
                DBBS.DataSource = (DBDT)
    
                If ReturnIdentity = True Then
                    Dim ReturnQuery As String = "SELECT @@IDENTITY As LastID;"
                    ' @@IDENTITY - SESSION
                    ' SCOPE_IDENTITY() - SESSION & SCOPE
                    ' IDENT_CURRENT(tablename) - LAST IDENT IN TABLE, ANY SCOPE, ANY SESSION
                    DBCmd = New SqlCommand(ReturnQuery, DBCon)
                    DBDT = New DataTable
                    DBDA = New SqlDataAdapter(DBCmd)
                    DBBS = New BindingSource
                    RecordCount = DBDA.Fill(DBDT)
                    DBBS.DataSource = (DBDT)
                End If
            Catch ex As Exception
                ' CAPTURE ERROR
                Exception = "ExecQuery Error: " & vbNewLine & ex.Message
            Finally
                ' CLOSE CONNECTION
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Try
        End Sub
    
        ' ADD PARAMS
        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New SqlParameter(Name, Value)
            Params.Add(NewParam)
        End Sub
    
        ' ERROR CHECKING
        Public Function HasException(Optional Report As Boolean = False) As Boolean
            If String.IsNullOrEmpty(Exception) Then Return False
            If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
            Return True
        End Function
    
    End Class

    Now the section I need help with.

    Public Sub RecordCountfiftynine(Optional ByVal Query As String = "")
    
            If Query = "" Then
                SQL.ExecQuery("SELECT COUNT (*) FROM Everyone WHERE Gender = 'M' AND Current_Res = 1 AND (Trailer <> N'CHND');")
            Else
                SQL.ExecQuery(Query)
            End If
    
            ' ERROR HANDLING
            If SQL.HasException(True) Then Exit Sub
    
            TssCurrent59th.Text = "There are currently " & SOMETHING HERE & " residents at 59th."
    
        End Sub

    The query works great in SQL but I just can't figure this one out.



    • Edited by DannyH0813 Monday, March 30, 2020 11:51 PM
    Monday, March 30, 2020 11:48 PM

Answers

  • Hi DannyH0813,
    Based on your description, could you explain in detail what "I want to query a record count to a label" means?
    Do you want to display the record count on the label? If so, you can use Label.Text property to show the value of the record count. And what problem you encountered in the section you need help with?
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DannyH0813 Tuesday, March 31, 2020 7:40 PM
    Tuesday, March 31, 2020 9:43 AM
  • Well I think I have fixed it. I needed to make the DBCON Public in the SQLControl.vb class. And then I changed the recordcountfiftynine sub routine around. Now I get the results of what I was asking about.

    Here's the code

    Public Sub RecordCountfiftynine()
    
            Dim ConStr As String = SQL.DBCon.ConnectionString
            Dim Query As String = "SELECT COUNT (*) FROM Everyone WHERE Gender = 'M' AND Current_Res = 1 AND (Trailer <> N'CHND');"
            Dim Con As New System.Data.SqlClient.SqlConnection(ConStr)
            Con.Open()
            Dim Command As New System.Data.SqlClient.SqlCommand(Query, Con)
            Dim Count As Integer = Command.ExecuteScalar()
            Con.Close()
    
            TssCurrent59th.Text = "There are currently " & Count & " residents at 59th."
    
    
        End Sub
    Thanks Daniel for some direction.

    • Marked as answer by DannyH0813 Tuesday, March 31, 2020 7:40 PM
    Tuesday, March 31, 2020 7:40 PM

All replies

  • Hi DannyH0813,
    Based on your description, could you explain in detail what "I want to query a record count to a label" means?
    Do you want to display the record count on the label? If so, you can use Label.Text property to show the value of the record count. And what problem you encountered in the section you need help with?
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DannyH0813 Tuesday, March 31, 2020 7:40 PM
    Tuesday, March 31, 2020 9:43 AM
  • Well I think I have fixed it. I needed to make the DBCON Public in the SQLControl.vb class. And then I changed the recordcountfiftynine sub routine around. Now I get the results of what I was asking about.

    Here's the code

    Public Sub RecordCountfiftynine()
    
            Dim ConStr As String = SQL.DBCon.ConnectionString
            Dim Query As String = "SELECT COUNT (*) FROM Everyone WHERE Gender = 'M' AND Current_Res = 1 AND (Trailer <> N'CHND');"
            Dim Con As New System.Data.SqlClient.SqlConnection(ConStr)
            Con.Open()
            Dim Command As New System.Data.SqlClient.SqlCommand(Query, Con)
            Dim Count As Integer = Command.ExecuteScalar()
            Con.Close()
    
            TssCurrent59th.Text = "There are currently " & Count & " residents at 59th."
    
    
        End Sub
    Thanks Daniel for some direction.

    • Marked as answer by DannyH0813 Tuesday, March 31, 2020 7:40 PM
    Tuesday, March 31, 2020 7:40 PM