Retrieving All Tables in MS ACCESS

[William_14]

Hello everyone!!!! Good Day ... I am having trouble on how to retrieve 2 fields of every tables in my Database .... I have 3 Tables saved in my MS Access, namely: RegularPassenger, DisablePassenger, and VIPPassenger ... and every tables has 2 fields each, which are SeatsNo and Filled .... the scenario is that I wanted to let the user see if their selected seat/s are already RESERVED which indicates a colored button if its already been reserved ..... the codes i have done so far is that I can only retrieve one Table .... the codes are:

Imports System
Imports System.Data.OleDb

Public Class SeatsAlignment
    Dim con As New System.Data.OleDb.OleDbConnection
    Dim cmd As New System.Data.OleDb.OleDbCommand
    Dim CntRw As Integer

    Private Function CheckReservation(ByVal StNo As String) As Boolean
        If con.State = ConnectionState.Open Then con.Close()
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\laptop\Documents\Trial\Sample.accdb")
        con.Open()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Select Count(*) From RegularPassenger Where SeatNo=@StNo And Filled=@StFld"
        cmd.Parameters.Add("@StNo", OleDb.OleDbType.VarChar, 10).Value = StNo
        cmd.Parameters.Add("@StFld", OleDb.OleDbType.VarChar, 10).Value = "RESERVED"
        cmd.Connection = con
        CntRw = cmd.ExecuteScalar()
        cmd.Parameters.Clear()
        cmd.Dispose()
        con.Close()
        con.Dispose()

        Return IIf(CntRw > 0, True, False)
    End Function

    Private Sub SeatsAlignment_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        RPS.Enabled = False
        DPS.Enabled = False
        VPS.Enabled = False
        TextBox1.Enabled = False
        TextBox2.Enabled = False


        Dim Buttons As New List(Of Button) From {A3, A4, A5, A6, A7, B3, B4, B5, B6, B7, _
            C3, C4, C5, C6, C7, D3, D4, D5, D6, D7, E3, E4, E5, E6, E7, F3, F4, F5, F6, F7}
        For Each btn As Button In Buttons
            If Me.CheckReservation(btn.Name) Then
                btn.BackColor = Color.Firebrick
                btn.Enabled = False
            End If
        Next
    End Sub

    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        If MsgBox("Are you sure you want to EXIT?", vbYesNo + vbQuestion, _
            "Philippine Sweets Airline") = vbYes Then
            Me.Close()
        End If
    End Sub
End Class

And I dont know what the exact code to put on the line 14 to retrieve all 3 Tables .... I really need some help, I consumed my time all night yesterday to have some research about this condition, but it makes me more confused ... hope someone already figure this out or knows how .... I appreciate any help ... Thank YOu so much ... God Bless

[Santanu Das]

This is not a good job to create a table for every status. Add a field of the status, which describe the seat is for Regular, disabled or VIP's. and check the condition.
Here I make a modification in the table 'Reservation' with field name SeatStatus, FieldType Text (10). Where I entered the Status for the SeatNos i.e. "REGULAR", "DISABLED" and "VIP"
Now we could try to check whether the seatNo beongs to which status. And tried to Create and Group the Buttons as per their Status. We are trying to Create GroupBoxes and attaching the Buttons as per their status.
I start here with a Form, a Button and a Panel to hold the Button
It looks like this

After loading all Groupboxes and Seat Buttons it would be look like

You have to change all required fields like Database Name, Table Name, Field Name etc.

'Importing the system references

Imports System
Imports System.Data.OleDb
Imports System.ComponentModel

Public Class Form1
    'Declaration of varibles
    Dim BError As Boolean = False
    Dim NwBtn As New Button
    Dim NwGrb As New GroupBox


    Dim con As New System.Data.OleDb.OleDbConnection
    Dim cmd As New System.Data.OleDb.OleDbCommand
    Dim dr As OleDbDataReader

    Dim CntRw As Integer


    Private Sub PickSeatNos()
        'Here I have tried to draw GroupBaox for each Status of the seats
        'and the buttons in their corresponding groupboxes.

        Dim i As Integer = 0
        Dim j As Integer = 0

        Dim bx As Integer = 10
        Dim by As Integer = -30

        Dim sts As String = ""


        'Try to open the Connection.
        If con.State = ConnectionState.Open Then con.Close()

        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Philippine Sweet Airline Seats Reservation.accdb")
        con.Open()

        'Assigning the Command Text to the Command Object.
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Select * From SeatReserve"

        cmd.Connection = con

        'Open the DataReader
        dr = cmd.ExecuteReader()

        'Check if the Reader has any Rows or not.
        'If it has any rows make a Loop to read through the rows.
        If dr.HasRows Then
            Do While dr.Read()

                If Not sts.Contains(dr.Item("SeatStatus")) Then

                    sts = dr.Item("SeatStatus")

                    'Making a new GroupBox and Set its Location and sizes
                    Dim grb As New GroupBox
                    grb.Left = j + 10
                    grb.Width = 100
                    grb.Height = 100
                    grb.Top = 10
                    grb.Name = sts
                    grb.Text = sts & " Persons"
                    grb.Parent = Me
                    grb.Show()

                    bx = 10
                    by = 20
                    NwGrb = grb
                End If

                'Making New Button into the corresponding Groupbox
                'and set it's location, and text
                'Attach a Sub Proceedure to its Click events
                'The Sub Procedure has that all functionality
                'which would be executed when a Button would be clicked.
                Dim btn As New Button
                AddHandler btn.Click, AddressOf Me.Button_Click

                btn.Height = 45
                btn.Width = 60

                btn.BackColor = SystemColors.Control
                btn.FlatStyle = FlatStyle.System

                btn.Name = dr.Item("SeatNo")

                'Checking here if button is reserved or not
                If Not String.IsNullOrEmpty(dr.Item("Filled").ToString) Then
                    btn.Text = dr.Item("SeatNo") & vbCrLf & StrConv(dr.Item("Filled"), VbStrConv.ProperCase)
                Else
                    btn.Text = dr.Item("SeatNo")
                End If


                btn.Parent = NwGrb
                btn.Left = bx
                btn.Top = by
                btn.Show()


                bx += 65

                'Re-setting the width of the GroupBox after
                'every attachment of Button.
                NwGrb.Width = bx + 5
                j = NwGrb.Left + NwGrb.Width

                'In every row we have three buttons
                'So we have to reset the locational values
                'after every three buttons
                i += 1
                If i = 3 Then
                    bx = 10
                    by += 50
                    i = 0
                End If

                'Re-setting the height of the GroupBox after
                'every attachment of 3 consecutive Buttons Row.
                NwGrb.Height = by + 10

            Loop
        End If
        dr.Close()
        cmd.Dispose()

        con.Close()
        con.Dispose()

        'Setting the location of the ENTER Button and size of the Form
        Me.Height = NwGrb.Top + NwGrb.Height + 80
        Me.Width = NwGrb.Left + NwGrb.Width + 25

        ButtonEnter.Left = (Panel1.Width - ButtonEnter.Width) / 2

        NwGrb = Nothing

    End Sub



    Private Function CheckReservation(ByVal StNo As String) As Boolean

        If con.State = ConnectionState.Open Then con.Close()


        'Set the ConnectionString and Open it
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Philippine Sweet Airline Seats Reservation.accdb")
        con.Open()

        'Preparing the Command Object to retrive the row numbers
        'if the condition is satisfied.
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Select Count(*) From SeatReserve Where SeatNo=@StNo And Filled=@StFld"
        cmd.Parameters.Add("@StNo", OleDb.OleDbType.VarChar, 10).Value = StNo
        cmd.Parameters.Add("@StFld", OleDb.OleDbType.VarChar, 10).Value = "RESERVED"
        cmd.Connection = con
        CntRw = cmd.ExecuteScalar()
        cmd.Parameters.Clear()
        cmd.Dispose()
        con.Close()
        con.Dispose()

        'If Row No is greater than 0 then pass True else pass false
        Return IIf(CntRw > 0, True, False)

    End Function

    Private Sub CancelReservation(ByVal StNo As String)
        'Cancelling the Reservation

        'Checking Connection is opened or not
        ' If opened Close the Connection.
        If con.State = ConnectionState.Open Then con.Close()


        'Set the ConnectionString and Open it
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Philippine Sweet Airline Seats Reservation.accdb")
        con.Open()

        'Preparing the Command Object to cancel the reservation
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Update SeatReserve Set Filled=@StFld Where SeatNo=@StNo"
        cmd.Parameters.Add("@StFld", OleDb.OleDbType.VarChar, 10).Value = ""
        cmd.Parameters("@StFld").Direction = ParameterDirection.Input
        cmd.Parameters.Add("@StNo", OleDb.OleDbType.VarChar, 10).Value = StNo
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        cmd.Dispose()

        con.Close()
        con.Dispose()



    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'Tried to pickup the seat nos on the form
        Me.PickSeatNos()


        ButtonEnter.Enabled = False
        BError = False

    End Sub

    Private Sub Button_Click(sender As Object, e As EventArgs)

        'variable to hold the Clicked Button
        Dim BSender As Button = CType(sender, Control)

        'Variable to hold the Clicked Button's Container
        Dim BBox As GroupBox = BSender.Parent


        'Chacking if the variable holds any GroupBox or not
        If Not (NwGrb Is Nothing) Then

            'If it holds make a loop through the GroupBoxes
            For Each grb As GroupBox In Me.Controls.OfType(Of GroupBox)()

                'Check the two variables hold the same GroupBox or not
                If (NwGrb Is BBox) Then
                    'If they hold the same make a loop through
                    'the Buttons of that GroupBox
                    For Each btn As Button In grb.Controls.OfType(Of Button)()
                        If BError = False Then

                            If (btn Is BSender) Then
                                'Check the Button is Reserved or not
                                'and Prompt to cancel the reservation or not
                                If Me.CheckReservation(btn.Name) Then
                                    Dim xstr As String = String.Format("The seat {0} is already reserved.{1}Do you sure to cancel the reservation?{2}If so, Click 'Ok'.", btn.Name, vbCrLf, vbCrLf)
                                    Select Case MessageBox.Show(xstr, _
                                                                "Reservation.....", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                                        Case Windows.Forms.DialogResult.OK
                                            Me.CancelReservation(btn.Name)
                                            ButtonEnter.Enabled = False

                                            MsgBox("Reservation is canceled.")

                                            btn.Text = btn.Name
                                            Exit Sub

                                        Case Else

                                            Exit Sub

                                    End Select

                                End If


                                NwBtn = BSender
                                NwGrb = BSender.Parent
                                BError = True

                                ButtonEnter.Enabled = True
                                Exit Sub

                            End If


                        Else

                            If BSender Is NwBtn Then
                                BError = False

                                NwBtn = Nothing
                                NwGrb = Nothing
                                ButtonEnter.Enabled = False
                                Exit Sub

                            Else

                                MsgBox("You can only select one seat for every Process", vbInformation, _
                                  "Philippine Sweet Airline")
                                NwBtn.Focus()
                                Exit Sub

                            End If

                        End If
                    Next

                Else

                    'If they hold different GroupBox 
                    'make a message and discard the loop and exit from proceedure
                    MsgBox("You can only select one seat for every Process", vbInformation, _
                           "Philippine Sweet Airline")
                    NwBtn.Focus()
                    Exit Sub

                End If
            Next

        Else

            'If it does not hold any groupbox
            'assign the values and Exit from the proceedure

            NwBtn = BSender
            NwGrb = BSender.Parent
            BError = True

            ButtonEnter.Enabled = True
            Exit Sub
        End If

    End Sub

    Private Sub ButtonEnter_Click(sender As System.Object, e As System.EventArgs) Handles ButtonEnter.Click
        'Checking Connection is opened or not
        ' If opened Close the Connection.
        If con.State = ConnectionState.Open Then con.Close()


        'Set the ConnectionString and Open it
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Philippine Sweet Airline Seats Reservation.accdb")
        con.Open()

        'Preparing the Command Object to reserve the seat no
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "Update SeatReserve Set Filled=@StFld Where SeatNo=@StNo"
        cmd.Parameters.Add("@StFld", OleDb.OleDbType.VarChar, 10).Value = "RESERVED"
        cmd.Parameters("@StFld").Direction = ParameterDirection.Input
        cmd.Parameters.Add("@StNo", OleDb.OleDbType.VarChar, 10).Value = NwBtn.Name
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        cmd.Dispose()

        con.Close()
        con.Dispose()




        ButtonEnter.Enabled = False

        'After reservation alter the text of the reserved seat
        NwBtn.Text &= vbCrLf & "Reserved"


        'Prompt the user whether it runs or not
        Dim xStr As String = String.Format("You Select the Seat Number {0}.{1}Do You want to continue?", NwBtn.Name, vbCrLf)
        Select Case MessageBox.Show(xStr, "Philippine Sweet Airline", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
            Case Windows.Forms.DialogResult.Yes
                BError = False
                Return
            Case Windows.Forms.DialogResult.No
                Me.Close()
        End Select
    End Sub


End Class

Hope, it would be help you.

[William_14]

Hello Sir Santanu Das, I am really really Thankful for the Help sir .... I do really appreciate it, so much ... but I came to a conclusion sir that instead of having 3 Tables, I just made it into 1 Table Sir so that it would be easy for me to make the codes to view all the reserved seats ... and besides, your codes sir are so amazing that I quite couldn't understand .... i mean it makes me more resourceful like research more if how did that codes are there, what makes it like that .... I'm really Thankful for the Help sir ... Thank You so much .... God Bless ^_^

[Santanu Das]

Lot of thanks to J. C. & oussama for your apriciation.

@William_14
Here I found a problem when it executes Button_Click event. The CheckReservation Function does not working because I call it at wrong place.
So I modify the Botton_Click event. It would be worked properly.

 Private Sub Button_Click(sender As Object, e As EventArgs)

        'variable to hold the Clicked Button
        Dim BSender As Button = CType(sender, Control)

        'Variable to hold the Clicked Button's Container
        Dim BBox As GroupBox = BSender.Parent


        'Chacking if the variable holds any GroupBox or not
        If Not (NwGrb Is Nothing) Then

            'If it holds make a loop through the GroupBoxes
            For Each grb As GroupBox In Me.Controls.OfType(Of GroupBox)()

                'Check the two variables hold the same GroupBox or not
                If (NwGrb Is BBox) Then
                    'If they hold the same make a loop through
                    'the Buttons of that GroupBox
                    For Each btn As Button In grb.Controls.OfType(Of Button)()
                        If BError = False Then

                            'Initialize the variables
                            MsgBox(BSender.Text)
                            NwBtn = BSender
                            NwGrb = BSender.Parent
                            BError = True

                            ButtonEnter.Enabled = True
                            Exit Sub

                        Else

                            If BSender Is NwBtn Then
                                BError = False

                                NwBtn = Nothing
                                NwGrb = Nothing
                                ButtonEnter.Enabled = False
                                Exit Sub

                            Else

                                'If you select another button in the same groupbox then
                                'make a message and discard the new selection.
                                MsgBox("You can only select one seat for every Process", vbInformation, _
                                  "Philippine Sweet Airline")
                                NwBtn.Focus()
                                Exit Sub

                            End If

                        End If
                    Next
                Else

                    'If they hold different GroupBox 
                    'make a message and discard the loop and exit from proceedure
                    MsgBox("You can only select one seat for every Process", vbInformation, _
                           "Philippine Sweet Airline")
                    NwBtn.Focus()
                    Exit Sub

                End If
            Next

        Else

            'If it does not hold any groupbox
            'assign the values and Exit from the proceedure


            'Check here if the seat is reserved or not
            If Me.CheckReservation(BSender.Name) Then
                Dim xstr As String = String.Format("The seat {0} is already reserved.{1}Do you sure to cancel the reservation?{2}If so, Click 'Ok'.", BSender.Name, vbCrLf, vbCrLf)
                Select Case MessageBox.Show(xstr, _
                                            "Reservation.....", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
                    Case Windows.Forms.DialogResult.OK
                        Me.CancelReservation(BSender.Name)
                        ButtonEnter.Enabled = False

                        MsgBox("Reservation is canceled.")

                        BSender.Text = BSender.Name
                        Exit Sub

                    Case Else

                        Exit Sub

                End Select

            End If


            'initialize the variables
            NwBtn = BSender
            NwGrb = BSender.Parent
            BError = True

            ButtonEnter.Enabled = True
            Exit Sub
        End If


    End Sub