“OleDb.Connection Database” Connecting to Ms Access

[William_14]

Hello everyone!!! I need some help on how to put a word "RESERVED" on the "Filled" Column of the Access if the user will select a certain button (for example I have 5 Buttons, namely: A1, A2, A3, A4, A5) and select the ENTER button, a word "RESERVED" will be written on the "Filled" Column of the access .... the scenario is this: I have 2 Columns in the MS Access, namely: Seats# & Filled .... on the Seats# Column, there are 5 rows, namely: A1, A2, A3, A4, A5 and on the "Filled" Column is Blank (theres no written on it) ... coz i wanted to put a word "RESERVED" on the specific row if a user will select a seat# A2 for example and then clicked the ENTER button and will have a word "RESERVED" on the "Filled" Column under the row of A2 .... What code is appropriate for the Condition? I will really appreciate any HELP ..... Thank You so MUCH!!!:'(

[Santanu Das]

You did not post your codes, what far you have done.
We can help you but not for to do your jobs.

[William_14]

o0ohhhhh yes ... I'm sir ... so far, the codes i have done are this:

Public Class Regular_Passenger
    Dim con As System.Data.OleDb.OleDbConnection
    Dim cmd As System.Data.OleDb.OleDbCommand
    Dim dr As System.Data.OleDb.OleDbDataReader
    Dim res As String


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

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

    Private Sub BACK_Click(sender As Object, e As EventArgs) Handles BACK.Click
        Me.Close()
        Form1.Show()
    End Sub

    Private Sub ENTER_Click(sender As Object, e As EventArgs) Handles ENTER.Click
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Philippine Sweet Airline Seats Reservation.accdb")
        con.Open()



    End Sub

    Private Sub A3_Click(sender As Object, e As EventArgs) Handles A3.Click, A4.Click, A5.Click, A6.Click, A7.Click, _
        B3.Click, B4.Click, B5.Click, B6.Click, B7.Click, C3.Click, C4.Click, C5.Click, C6.Click, C7.Click, D3.Click, _
        D4.Click, D5.Click, D6.Click, D7.Click, E3.Click, E4.Click, E5.Click, E6.Click, E7.Click, F3.Click, F4.Click, _
        F5.Click, F6.Click, F7.Click

        Dim BSender As Button = CType(sender, Control)
        If BSender.BackColor = Color.Red Then
            BSender.BackColor = Color.WhiteSmoke
            Return
        End If

        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} 'Buttons you want to check
        Dim BError As Boolean = False
        For i = 0 To Buttons.Count - 1
            If Buttons(i).BackColor = Color.Red Then
                BError = True
            End If
        Next
        If BError = False Then
            sender.BackColor = Color.Red
        Else
            MsgBox("You can only select one seat for every Process", vbInformation, _
                   "Philippine Sweet Airline")
        End If

    End Sub
End Class

and i was just having a Problem on how to put a word "RESERVED" on the "Filled" Column of the Access if the user will select a certain button (for example I have 5 Buttons, namely: A1, A2, A3, A4, A5) and select the ENTER button, a word "RESERVED" will be written on the "Filled" Column of the access .... i attach the design of my Program Sir ... i appreciate the help ... Thank You so Much!!!! :'(

[Santanu Das]

Clicking Enter Button you want to Update the field 'Filled' by 'RESERVED'.
It is simple. Assumming that all 'Seats#' (similer to the name of the Buttons) are already Inserted into the Table.

Private Sub Enter_Click(sender As System.Object, e As System.EventArgs) Handles Enter.Click

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

        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 btn.BackColor = Color.Red Then


                cmd.CommandType = CommandType.Text
                cmd.CommandText = "Update <TableNAme> Set Filled=@StFld Where Seats#=@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 = btn.Name

                cmd.Connection = con
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                cmd.Dispose()

                Exit For

            End If
        Next

        con.Close()
        con.Dispose()

    End Sub

It could be help you.

[William_14]

hello Sir Santanu Das, I tried the codes but it ended up with an error on the

cmd.CommandType = CommandType.Text

i attach a photo of the error Sir .... I studied the code Sir but i dont have any idea why it turn out an error ... what is the appropriate code sir? I would really appreciate the help sir ... Thank You so much:'(

[Santanu Das]

At the line, where you Declare the OleDbCommand Object, add the keyword New Before the object Type. Write Dim cmd As New System.Data.OleDb.OleDbCommand in place of 'Dim cmd As System.Data.OleDb.OleDbCommand'. It should be solved your probs.

[William_14]

Hello Sir Santanu Das, I again did the code as you suggested Sir .... but then theres a New error occured which is the:

                cmd.ExecuteNonQuery()

i attach a new Photo of the error Sir, I'm sorry sir .... I did some research on the ExecuteNonQuery() sir but I can't see where is the error ... what is the appropriate code sir? I would really appreciate the help sir ... Thank You so much:'(

[Santanu Das]

The TableName, Fields Name are imaginary. Don't copy them. write Your original Table Name, Field Name DataTypes and Size. If you didn't it may occur an error.
Please post your original Database Name, Table Name, Fields Name ,DataType and size. I try my best.

[William_14]

Hello Sir Santanu Das, i tried to change the Column name from Seats# to SeatsNo, but another error appeared which is "he Microsoft Office Access database engine cannot find the input table or query." on the same line .... but i saved the Microsoft Access on this Connection ....

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

Is there other way of connecting the microsoft access without changing the codes Sir? my table names are Regular Passenger, Disable Passenger, and VIP Passenger ... and their fields are SeatsNo and Filled .... I would really appreciate the help ... Thank You so much:'(

[Santanu Das]

Here I have attached the codifications

Imports System
Imports System.Data.OleDb

Public Class Form1
    Dim con As New System.Data.OleDb.OleDbConnection
    Dim cmd As New System.Data.OleDb.OleDbCommand


    Private Sub A3_Click(sender As Object, e As EventArgs) Handles A3.Click, A4.Click, A5.Click, A6.Click, A7.Click, B3.Click, B4.Click, B5.Click, B6.Click, B7.Click
        Dim BSender As Button = CType(sender, Control)
        If BSender.BackColor = Color.Red Then
            BSender.BackColor = Color.WhiteSmoke
            Return
        End If
        Dim Buttons As New List(Of Button) From {A3, A4, A5, A6, A7, B3, B4, B5, B6, B7} 'Buttons you want to check
        Dim BError As Boolean = False
        For i = 0 To Buttons.Count - 1
            If Buttons(i).BackColor = Color.Red Then
                BError = True
            End If
        Next
        If BError = False Then
            sender.BackColor = Color.Red
        Else
            MsgBox("You can only select one seat for every Process", vbInformation, _
                   "Philippine Sweet Airline")
        End If
    End Sub

    Private Sub ButtonEnter_Click(sender As System.Object, e As System.EventArgs) Handles ButtonEnter.Click
        con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Philippine Sweet Airline Seats Reservation.accdb")
        con.Open()
        Dim Buttons As New List(Of Button) From {A3, A4, A5, A6, A7, B3, B4, B5, B6, B7}
        For Each btn As Button In Buttons
            If btn.BackColor = Color.Red Then
                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 = btn.Name
                cmd.Connection = con
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                cmd.Dispose()
                Exit For
            End If
        Next
        con.Close()
        con.Dispose()
    End Sub


End Class

Here I create a Table named SeatReserve with two fields SeatNo and Filled. Data Type of both fields are Text Type in size 10.
Thanks, Good Night.

[William_14]

Hello Sir Santanu Das, Thank You so so much Sir, this problem is SOLVED .... I made a new Database & create a new Project, it worked sir .... Thank You so so much .... :'(
I really appreciate your help Sir ... Thank You so Much .... God Bless

[Santanu Das]

Always welcome!