RefreshFunctions

Module RefreshFunctions

    Public Function RefreshDataGridView()
        '--------------------REFRESHES DATAGRIDVIEW--------------------
        Dim ConnString, dbProvider As String
        Dim con As OleDb.OleDbConnection
        Dim da As OleDb.OleDbDataAdapter    'Connects to the database
        Dim DataTable As DataTableCollection
        Dim Source As New BindingSource

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source="
        ConnString = dbProvider & MainDatabaseLocation

        TechSupportMenu.TblSubmitted_Ticket_DetailsTableAdapter.Fill(TechSupportMenu.Ticket_System_Database_2002DataSet.tblSubmitted_Ticket_Details)
        con = New OleDb.OleDbConnection
        con.ConnectionString = ConnString
        ds = New DataSet
        DataTable = ds.Tables

        'da = New OleDb.OleDbDataAdapter("SELECT * FROM tblSubmitted_Ticket_Details", con) 'Selects all items from table
        Dim sql As String = "SELECT * FROM tblSubmitted_Ticket_Details"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "tblSubmitted_Ticket_Details")

        Dim view As New DataView(DataTable(0))
        Source.DataSource = view
        TechSupportMenu.grdTickets.DataSource = view

        'Sorts DataGridView by Date Ascending
        With TechSupportMenu.grdTickets
            TechSupportMenu.grdTickets.Sort(.Columns(6), System.ComponentModel.ListSortDirection.Descending)
        End With

        Return TechSupportMenu.grdTickets
    End Function

    Public Function RefreshlstMyReplies()
        'Variables to connect to database need to be declared here too, to prevent errors
        Dim recordCount As Integer           'and 'cause it's a function
        Dim XCounter As Integer = 0
        Dim sql As String

        sql = "SELECT tblTicket_Reply_Details.Reply_ID, tblTicket_Reply_Details.Employee_ID, tblTicket_Reply_Details.TechSupport_ID, " _
        & "tblTicket_Reply_Details.Reply_Description, tblTicket_Reply_Details.Reply_Date FROM tblTicket_Reply_Details WHERE tblTicket_Reply_Details.TechSupport_ID = '" & UserIDDatabase & "'"
        db.connect()
        ds = db.sqlDetails(sql)
        db.Close()

        sql = "SELECT TechSupport_FirstName, TechSupport_LastName FROM tblTechSupport_Details WHERE TechSupport_ID = '" & UserIDDatabase & "'"
        db.connect()
        ds2 = db.sqlNames(sql)
        db.Close()

        '#Region "Add items to listview loop"
        recordCount = ds.Tables("Details").Rows.Count
        TechSupportMenu.lstMyReplies.Items.Clear()  'Clears the items in the listview
        Do Until XCounter = recordCount
            TechSupportMenu.lstMyReplies.Items.Add(ds.Tables("Details").Rows(XCounter).Item(0))
            TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(1))
            TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(2))
            TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds2.Tables("Names").Rows(0).Item(0))  'Puts the value 'first name' from the 'Names' dataset containing the techsupport details values
            TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds2.Tables("Names").Rows(0).Item(1))  'Puts the value 'last name' from the 'Names' dataset containing the techsupport details values
            TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(3))
            TechSupportMenu.lstMyReplies.Items(XCounter).SubItems.Add(ds.Tables("Details").Rows(XCounter).Item(4))
            XCounter = XCounter + 1
        Loop
        '#End Region#

        Return TechSupportMenu.lstMyReplies
    End Function

End Module