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
I am not sure i understand properly, but i guess u dont want the student be able to clock in twice without clock out. You have multiple options. The simpliest, you can use a status field in your db. When student clock in then save "status"=1 (student onsite), and at clockout "status"=0 (student not onsite). At every clock in and clock out you check the status of student. If 1 then can't clock in again as she/he onsite already. Or you save in the log when they tap the fob it was an entrance or leave and when you query the table with student you order the table by last insert time so the last record will come up where you see the last status of the log.
Edit: Add an extra bool type column in your database to students table like "studOnsite" and set the default value 0. When a student scan a card and you get the student details from the table here :
.CommandText = "SELECT * from students where `studtags`='" & studtag.Text & "';"
You will get back the "studOnsite" column aswell. After you can simply check the value like :
if (table.Rows(0).Item("studOnsite") then
'student already onsite so she/he is leaving
greet.Text = firstxt.Text & "Good bye!"
'update the student table and change "studOnsite" to false
'dim query as string=string.format("UPDATE students SET studOnsite=false WHERE studtags='{0}'",studtag.Text)
else
'student wasn't onsite so she/he is just arrived
greet.Text = firstxt.Text & "Welcome back!"
'update the student table and change "studOnsite" to true as student is onsite now
'dim query as string=string.format("UPDATE students SET studOnsite=true WHERE studtags='{0}'",studtag.Text)
end if