Imports System.Data.OleDb

Public Class DBControl

    Private DBCon As New OleDbConnection("PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source = db_Concert_Ticketing.mdb")

    Dim strID As String

    ' FOR SELECT
    Public DBDS As New DataSet
    Public DBDA As New OleDbDataAdapter
    Public DBDR As OleDbDataReader

    Public DBDT As New DataTable
    Public DBRow As DataRow

    ' FOR INSERT
    Public DBCmd As New OleDbCommand

    ' SET RESERVED
    Public Sub SetReserved(ByVal query As String, ByVal tableName As String)
        ' OPEN DB CONNECTION
        DBCon.Open()

        ' COMMAND
        DBCmd = New OleDbCommand(query, DBCon)

        DBDS = New DataSet
        DBDA = New OleDbDataAdapter(DBCmd)
        DBDA.Fill(DBDS, tableName)

        ' CLOSE DB CONNECTION
        DBCon.Close()

    End Sub

    ' QUERY SELECT
    Public Sub DBSelect(ByVal Query As String)

        ' OPEN DB CONNECTION
        DBCon.Close()
        If DBCon.State = ConnectionState.Closed Then
            DBCon.Open()
        End If

        ' CLEAR DATASET
        DBDS.Clear()

        ' EXECUTE QUERY
        DBDA = New OleDbDataAdapter(Query, DBCon)
        DBDA.Fill(DBDS, "tb_Registered_User")

        ' CLOSE THE DATABASE CONNECTION
        DBCon.Close()

    End Sub

    ' UPDATE DATABASE
    Public Sub UpdateDB(ByVal Query As String)

        ' OPEN DB CONNECTION
        DBCon.Open()

        DBCmd.Connection = DBCon
        DBCmd.CommandText = Query
        DBCmd.ExecuteNonQuery()

        ' CLOSE DB CONNECTION
        DBCon.Close()

    End Sub

    ' QUERY INSERT
    Public Sub DBInsert(ByVal Query As String)

        ' OPEN DB CONNECTION
        DBCon.Close()
        If DBCon.State = ConnectionState.Closed Then
            DBCon.Open()
        End If

        ' INSERT DATA TO TABLE
        DBCmd.Connection = DBCon
        DBCmd.CommandText = Query
        DBCmd.ExecuteNonQuery()

        ' CLOSE DB CONNECTION
        DBCon.Close()
    End Sub

    Public Function getLastID(ByVal DB_ID As String, ByVal TableName As String) As String

        Dim ID As Integer
        Dim Query As String = "SELECT MAX(" & DB_ID & ") AS LASTID FROM " & TableName & ""

        ' OPEN DB CONNECTION
        DBCon.Close()
        If DBCon.State = ConnectionState.Closed Then
            DBCon.Open()
        End If
        DBCmd.Connection = DBCon

        DBCmd.CommandText = Query

        ID = DBCmd.ExecuteScalar()
        If ID < 10 Then
            strID = "000" & ID
            Return strID
        ElseIf ID >= 10 And ID < 100 Then
            strID = "00" & ID
            Return strID
        ElseIf ID >= 100 And ID < 1000 Then
            strID = "0" & ID
            Return strID
        ElseIf ID >= 1000 And ID < 10000 Then
            strID = ID
            Return strID
        End If

        'CLOSE DB CONNECTION
        DBCon.Close()

        Return strID

    End Function

    ' GET THE LAST ID AND INCREMENT
    Public Function GetLastIDIncrement(ByVal DB_ID As String, ByVal TableName As String, ByVal Increment_Value As Integer) As String

        Dim strID As String
        Dim ID As Integer
        Dim Query As String = "SELECT MAX(" & DB_ID & ") AS LASTID FROM " & TableName & ""

        ' OPEN DB CONNECTION
        DBCon.Close()
        If DBCon.State = ConnectionState.Closed Then
            DBCon.Open()
        End If
        DBCmd.Connection = DBCon

        DBCmd.CommandText = Query

        ID = DBCmd.ExecuteScalar()
        ID = Val(ID + Increment_Value)
        If ID < 10 Then
            strID = "000" & ID
        ElseIf ID >= 10 And ID < 100 Then
            strID = "00" & ID
        ElseIf ID >= 100 And ID < 1000 Then
            strID = "0" & ID
        ElseIf ID >= 1000 And ID < 10000 Then
            strID = ID
        Else
            strID = ID
        End If

        'CLOSE DB CONNECTION
        DBCon.Close()

        Return strID

    End Function

End Class