Making an error message for the repeating in or out if loops in the database table

[sangguko19]

Good Day, to make things clear I will introduce my ongoing system which is "Gate attendance for student in our university", this system is consists of RFID reader RC522 and Arduino, the student should tap his/her RFID tag and the data retrieved shown at the monitor.. The system is our capstone project and it is done but the panel required us for another problem to be solved.

All functions is already done about the scanning,retrieving data,registering students , this will apply to the college students which is feel free to go out and in anytime depends on the schedule unlike highschool just one IN and OUT.

The two RFID reader is placed in Entrance and Exit of the University theres a system for out in the exit and In for the entrance.The data retrieved from in and out would save into the same table. How can I show the messagebox warning to the students if he taps IN without go OUT first? I know it something about the query. but I have no Idea. I need some help here for my Capstone.. Any Comments and suggestion is appreciated advance.. Here is my code about the retrieving the data based on the RFID tag number shown in the textbox changed event..by the way my table studlogs is the one handled the attendance.

Private Sub studtag_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles studtag.TextChanged

    Notenrolled.Close()
    greet.Text = ""
    PictureBox4.Visible = False

    If studtag.Text = "44F2F38B" Then

        Endday()

    End If


    If studtag.Text = recents.Text Then

        Alreadylogin.Show()
    ElseIf studtag.TextLength = 8 Then

        DataGridView1.Sort(DataGridView1.Columns(6), System.ComponentModel.ListSortDirection.Descending)
        con = New MySqlConnection
        con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
        Dim query As String

        query = "select * from dat.students"
        cmd = New MySqlCommand(query, con)

        Dim table As New DataTable

        Try
            con.Open()
            'Gets or sets an SQL statement or stored procedure used to select records in the database.
            With cmd
                .Connection = con
                .CommandText = "SELECT * from students where `studtags`='" & studtag.Text & "';"
            End With
            da.SelectCommand = cmd
            da.Fill(table)
            'it gets the data from specific column and fill it into Label

            idno.Text = table.Rows(0).Item(1)
            lastxt.Text = table.Rows(0).Item(2)
            firstxt.Text = table.Rows(0).Item(3)
            middletxt.Text = table.Rows(0).Item(4)
            dob.Text = table.Rows(0).Item(6)
            year.Text = table.Rows(0).Item(7)
            crsetxt.Text = table.Rows(0).Item(10)

            tagtxt.Text = studtag.Text
            timein.Text = times.Text

            dr = cmd.ExecuteReader()
            dr.Read()

            If dob.Text = bday.Text Then
                greet.Text = firstxt.Text + " Today is your Birthday. Greetings :D."
                PictureBox4.Visible = True

            End If

            Dim img() As Byte = CType(dr("studpic"), Byte())


            Using ms As New IO.MemoryStream(img)
                PictureBox1.Image = Image.FromStream(ms)

            End Using
            My.Computer.Audio.Play("C:\Users\BOR\Desktop\Parsu Gate\Parsu\audio\scanned.wav")
            insert()
            loadtable()
            studdailyhistory()


        Catch ex As Exception
            Notenrolled.Show()
            DataGridView1.Sort(DataGridView1.Columns(5), System.ComponentModel.ListSortDirection.Descending)
            If studtag.Text = "44F2F38B" Then
                Notenrolled.Close()
            End If


        Finally

            con.Dispose()
            con.Close()

        End Try

    End If
    recents.Text = tagtxt.Text



End Sub

Public Sub insert()

    con = New MySqlConnection
    con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    Dim reader As MySqlDataReader
    Dim mstream As New System.IO.MemoryStream()

    Dim arrImage() As Byte = mstream.GetBuffer()
    mstream.Close()

    Try

        con.Open()
        Dim query3 As String

        query3 = "insert into dat.studlogs (studtags,idno,lastxt,firstxt,middletxt,dob,log,timein,crse,studpic) values ('" & tagtxt.Text & "','" & idno.Text & "','" & lastxt.Text & "','" & firstxt.Text & "','" & middletxt.Text & "','" & dob.Text & "','" & log.Text & "','" & timein.Text & "','" & crsetxt.Text & "',@studpic)"
        cmd = New MySqlCommand(query3, con)
        cmd.Parameters.AddWithValue("@studpic", arrImage)
        reader = cmd.ExecuteReader


        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        con.Dispose()
    End Try

End Sub


Public Sub studdailyhistory()

    con = New MySqlConnection
    con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    Dim reader As MySqlDataReader
    Dim mstream As New System.IO.MemoryStream()

    Dim arrImage() As Byte = mstream.GetBuffer()
    mstream.Close()

    Try

        con.Open()
        Dim query3 As String

        query3 = "insert into dat.studdailyhistory (studtags,idno,lastxt,firstxt,middletxt,dob,log,timein,crse,dates,studpic) values ('" & tagtxt.Text & "','" & idno.Text & "','" & lastxt.Text & "','" & firstxt.Text & "','" & middletxt.Text & "','" & dob.Text & "','" & log.Text & "','" & timein.Text & "','" & crsetxt.Text & "','" & wholedate.Text & "',@studpic)"
        cmd = New MySqlCommand(query3, con)
        cmd.Parameters.AddWithValue("@studpic", arrImage)
        reader = cmd.ExecuteReader

        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        con.Dispose()
    End Try

End Sub

Public Sub loadtable()

    con = New MySqlConnection
    con.ConnectionString = "server=localhost;userid=root;password=1234;database=dat"
    Dim SDA As New MySqlDataAdapter
    Dim dbDataset As New DataTable
    Dim bSource As New BindingSource

    Try

        con.Open()
        Dim query3 As String

        query3 = "select idno as 'Student_ID',lastxt as 'LastName',firstxt as 'FirstName',middletxt as 'MiddleName',log as 'Status',timein as 'Timein',crse as 'Course' from dat.studlogs"
        cmd = New MySqlCommand(query3, con)
        SDA.SelectCommand = cmd
        SDA.Fill(dbDataset)
        bSource.DataSource = dbDataset
        DataGridView1.DataSource = bSource
        SDA.Update(dbDataset)

        DataGridView1.Sort(DataGridView1.Columns(5), System.ComponentModel.ListSortDirection.Descending)

        If dbDataset.Rows.Count > 0 Then
            logins.Text = dbDataset.Rows.Count.ToString()
        End If

        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        con.Dispose()
    End Try
End Sub

[jmcilhinney]

You use an If statement. It's really that simple. The state of the data should tell you whether the last action was an entrance or an exit. When the student scans at the entrance you query the database and IF there last action was an entrance then you display the message. Likewise, when they scan at the exit you display the message IF there last action was an exit.

How you represent the last action in the data is up to you. Maybe you have a Boolean column where True is entrance and False is exit. Maybe you have a date/time for the last entrance and exit so you simply have to check which is greater. Maybe you have something else. You designed it so you should know how to use it to determine what the last action was. IF the current action matches the last action, display the message.

[sangguko19]

You use an If statement. It's really that simple. The state of the data should tell you whether the last action was an entrance or an exit. When the student scans at the entrance you query the database and IF there last action was an entrance then you display the message. Likewise, when they scan at the exit you display the message IF there last action was an exit.

How you represent the last action in the data is up to you. Maybe you have a Boolean column where True is entrance and False is exit. Maybe you have a date/time for the last entrance and exit so you simply have to check which is greater. Maybe you have something else. You designed it so you should know how to use it to determine what the last action was. IF the current action matches the last action, display the message.

@jmcilhinney Thank you for your great suggestion, i have a column date/time but no boolean. And the college students here are always enter and exit repeatedly so the data in the table has many values containing the log out and login repeatedly but I already developed an attendance for highschool which is the student should login once and log out once in one day unlike this ongoing system which is time to time the students login logout. By the way about the displaying the message when the students tap their RFID card is already done but it only works if the students tap his RFID then repeat again and the warning is displayed "You already login please log out first" but when the first student tap the card and another next student tap too, and the first student tap again the warning not pop up. Can you give me a guide link websites where your suggestions defined.. like that boolean thing and other which help me to get through. Thank you jmcilhinney you are so active

[jmcilhinney]

You're not going to find a web site that details what I described and you shouldn't really need one. It's just another column in the database, like any other column. In your application, it's a Boolean like any other Boolean. I think that you're assuming that it's complicated when it's not. Add a column, save a value to that column when you insert a record, test whether the last value matches the current value before inserting and error if it does. There's nothing complex about that.

[sangguko19]

Okay I got it. Thank you so much sir..I will follow your instructions

[jmcilhinney]

I'll be more than happy to help further if you have issues along the way but you've definitely got enough to have a good go at it first.

[sangguko19]

Ok sir I'm happy because there's someone inspired me.. sir