none
vb.net count records from access

    Question

  • hello,

    i have a program for my helpdesk that allows staff to input tickets this uses .net and i would it to cound and display to a admin the amount of inprogress or under review ticket there are

    for example:

    when an admin logs into the application on the main screen it displays inprogress ticket and under review tickets

    then they can click on the label and it opens another form that displays the relevent tickets

    thank you if you can help

    the backend is an access database

    if this is any help below is the code for entering the ticket information

     Access.AddParam("@IssueTitle", Txt_Title.Text)
            Access.AddParam("@AssignedTo", COM_Technician.Text)
            Access.AddParam("@OpenDate", DTP_OpenDate.Value.Date)
            Access.AddParam("@Status", COM_Status.Text)
            Access.AddParam("@Priority", COM_Priority.Text)
            Access.AddParam("@Category", COM_Category.Text)
            Access.AddParam("@CategoryOther", TXT_CatOther.Text)
            Access.AddParam("@RequestedBy", TXT_RaisedBy.Text)
            Access.AddParam("@ADName", TXT_ADName.Text)
            Access.AddParam("@Description", TXT_Description.Text)
            Access.AddParam("@Comment", TXT_TechNotes.Text)
    
            'Insert parameters to database, Command
            Access.ExecQuery("INSERT INTO Tickets (Title, Technician, OpenDate, Status, Priority, Category, CatOther, Requestor, ADName, Description, Tech_Notes) " &
                             "VALUES (@IssueTitle, @AssignedTo, @OpenDate, @Status, @Priority, @Category, @CategoryOther, @RequestedBy, @ADName, @Description, @Comments); ")
    

    Monday, March 05, 2018 10:02 PM

Answers

  • Hi steven,

    As Paul said, you want to return one value to fill Textbox, so you need to make function return one value, I do some change in your DBControl, you can take a look.

    Public Class DBControl
        ' CREATE YOUR DB CONNECTION
        Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                             "Data Source=Database.accdb;")
    
        ' PREPARE DB COMMAND
        Public DBCmd As OleDbCommand
    
        ' DB DATA
        Public DBDA As OleDbDataAdapter
        Public DBDT As DataTable
    
        ' QUERY PARAMETERS
        Public Params As New List(Of OleDbParameter)
    
        ' QUERY STATISTICS
        Public RecordCount As Integer = 0
        Public Exception As String
    
    
        Public Function ExecQuery(Query As String) As Integer
            ' RESET QUERY STATS
            RecordCount = 0
            Exception = ""
    
            Try
                ' OPEN A CONNECTION
                DBCon.Open()
    
                ' CREATE DB COMMAND
                DBCmd = New OleDbCommand(Query, DBCon)
    
                ' LOAD PARAMS INTO DB COMMAND
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    
                ' CLEAR PARAMS LIST
                Params.Clear()
    
                ' EXECUTE COMMAND & FILL DATATABLE
                DBDT = New DataTable
                'DBDA = New OleDbDataAdapter(DBCmd)
                RecordCount = Convert.ToInt32(DBCmd.ExecuteScalar())
    
            Catch ex As Exception
                Exception = ex.Message
            End Try
    
            Return RecordCount
            ' CLOSE YOUR CONNECTION
            If DBCon.State = ConnectionState.Open Then DBCon.Close()
        End Function

    Best Regards,

    Cherry


    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.

    Friday, March 09, 2018 3:11 AM
    Moderator

All replies

  • Hello,

    I would recommend that you show (perhaps using images) the table schema and sample data. Lastly, what you have tried already for doing what you are asking in SQL meaning you should have attempting to write SQL SELECT statements in MS-Access prior to attempting to write code in the project. 

    We are here to assist yet you need to place an effort into this before asking how to do this :-)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, March 05, 2018 11:01 PM
    Moderator
  • So what is the question? If you are encountering a particular issue or error then please identify.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, March 05, 2018 11:06 PM
  • OK try follow me here:

    bellow is my back end that uses access with nothing special just three tablets.



    the table shown is the tablet that i want to count records from but i only want to count the record with "Under Review" in the status as these are the tickets that have no technician (Ignore number 2 as these are tests)

    i would like the count number to be placed into the text box you see above next to the button check status


    i have tries a select count into the textbox when the main admin form have the focus for it to refresh, but i am well over my head with this thing

    hope this better explains my needs as i cannot make it any better, as i know what i want but cannot put it into words better than this


    Tuesday, March 06, 2018 8:31 AM
  • What is "access.ExecQuery"? Is this pseudo code of some sort or is this a Class containing code the runs the query and returns a value?

    When do you want this count to display?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 06, 2018 12:52 PM
  • sorry at the top of every class i write

    private Access as new DBControl

    then that allows me to run code from the below clss makes it quicker. instead of writing the oledb code out each time i need to use it

    Imports System.Data.OleDb
    Public Class DBControl
        ' CREATE YOUR DB CONNECTION
        Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                             "Data Source=Database.accdb;")
    
        ' PREPARE DB COMMAND
        Public DBCmd As OleDbCommand
    
        ' DB DATA
        Public DBDA As OleDbDataAdapter
            Public DBDT As DataTable
    
            ' QUERY PARAMETERS
            Public Params As New List(Of OleDbParameter)
    
            ' QUERY STATISTICS
            Public RecordCount As Integer
            Public Exception As String
    
            Public Sub ExecQuery(Query As String)
                ' RESET QUERY STATS
                RecordCount = 0
                Exception = ""
    
                Try
                    ' OPEN A CONNECTION
                    DBCon.Open()
    
                    ' CREATE DB COMMAND
                    DBCmd = New OleDbCommand(Query, DBCon)
    
                    ' LOAD PARAMS INTO DB COMMAND
                    Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    
                    ' CLEAR PARAMS LIST
                    Params.Clear()
    
                    ' EXECUTE COMMAND & FILL DATATABLE
                    DBDT = New DataTable
                    DBDA = New OleDbDataAdapter(DBCmd)
                    RecordCount = DBDA.Fill(DBDT)
                Catch ex As Exception
                    Exception = ex.Message
                End Try
    
                ' CLOSE YOUR CONNECTION
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Sub
    
            ' INCLUDE QUERY & COMMAND PARAMETERS
            Public Sub AddParam(Name As String, Value As Object)
                Dim NewParam As New OleDbParameter(Name, Value)
                Params.Add(NewParam)
            End Sub
    End Class

    in plain text, I want to count the amount of record that are under review and place that in a textbox or label to show a admin that there is pending record that need attention.

    Tuesday, March 06, 2018 5:55 PM
  • So when do you want to populate the TextBox? When the Form loads? When you click a button?

    One thing you can do is set a debug breakpoint (Debug...Toggle Breakpoint or F9 key) on the line of code that returns the count value and see if it ever stops at that line.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 06, 2018 6:43 PM
  • Also, ExecQuery does not return a value. You would need to change it to a Function and return the RecordCount value:

    Public Function ExecQuery(Query As String) As Integer
    
         '...
         '...
    
         Return RecordCount
    
    End Function


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 06, 2018 6:48 PM
  • i want it to populate the textbox when a button is clicked and when the main for there the text box will be place is focused on

    any example code would be great or point me in the direction would be great olso

    Tuesday, March 06, 2018 6:59 PM
  • Hi steven,

    As Paul said, you want to return one value to fill Textbox, so you need to make function return one value, I do some change in your DBControl, you can take a look.

    Public Class DBControl
        ' CREATE YOUR DB CONNECTION
        Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                             "Data Source=Database.accdb;")
    
        ' PREPARE DB COMMAND
        Public DBCmd As OleDbCommand
    
        ' DB DATA
        Public DBDA As OleDbDataAdapter
        Public DBDT As DataTable
    
        ' QUERY PARAMETERS
        Public Params As New List(Of OleDbParameter)
    
        ' QUERY STATISTICS
        Public RecordCount As Integer = 0
        Public Exception As String
    
    
        Public Function ExecQuery(Query As String) As Integer
            ' RESET QUERY STATS
            RecordCount = 0
            Exception = ""
    
            Try
                ' OPEN A CONNECTION
                DBCon.Open()
    
                ' CREATE DB COMMAND
                DBCmd = New OleDbCommand(Query, DBCon)
    
                ' LOAD PARAMS INTO DB COMMAND
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
    
                ' CLEAR PARAMS LIST
                Params.Clear()
    
                ' EXECUTE COMMAND & FILL DATATABLE
                DBDT = New DataTable
                'DBDA = New OleDbDataAdapter(DBCmd)
                RecordCount = Convert.ToInt32(DBCmd.ExecuteScalar())
    
            Catch ex As Exception
                Exception = ex.Message
            End Try
    
            Return RecordCount
            ' CLOSE YOUR CONNECTION
            If DBCon.State = ConnectionState.Open Then DBCon.Close()
        End Function

    Best Regards,

    Cherry


    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.

    Friday, March 09, 2018 3:11 AM
    Moderator
  • side not but related to this topic:

    Is there a way for me to run the query each time a new record is added to the database, even if i do not make the record

    So for example when a new record is created by a staff member the query will run and update my number to show me the new count

    all instead of me clicking a button

    with what i have seen in research its not possible, but might be wrong

    Friday, March 09, 2018 3:19 PM