-
Feb 17th, 2018, 09:50 AM
#1
Thread Starter
Junior Member
Making an error message for the repeating in or out if loops in the database table
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.
Code:
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
-
Feb 17th, 2018, 08:33 PM
#2
Re: Making an error message for the repeating in or out if loops in the database tabl
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.
-
Feb 18th, 2018, 03:57 AM
#3
Thread Starter
Junior Member
Re: Making an error message for the repeating in or out if loops in the database tabl
Originally Posted by 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.
@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
-
Feb 18th, 2018, 04:10 AM
#4
Re: Making an error message for the repeating in or out if loops in the database tabl
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.
-
Feb 18th, 2018, 04:27 AM
#5
Thread Starter
Junior Member
Re: Making an error message for the repeating in or out if loops in the database tabl
Okay I got it. Thank you so much sir..I will follow your instructions
-
Feb 18th, 2018, 04:36 AM
#6
Re: Making an error message for the repeating in or out if loops in the database tabl
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.
-
Feb 18th, 2018, 04:47 AM
#7
Thread Starter
Junior Member
Re: Making an error message for the repeating in or out if loops in the database tabl
Ok sir I'm happy because there's someone inspired me.. sir
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|