Hello,
I have been making an employee timeclock and have run into a bump in the road.
Here's what my setup is like when an employee (who is not an Admin) loggs on they get a simple screen telling them the current time current date and two button that allow them to Clock In or Clock out. Their time punches are saved in my MySQL database. So in order to figure the hours/minutes they worked I have to pull their clockin time from the Database and store it in a variable. I am unsure of how to go about this. Thanks in Advance!!
Here's my code:
'MySQL Connections
Dim str As String
Dim conn As New MySqlConnection
Dim MyCmd As New MySqlCommand("SELECT * FROM users", conn)
Dim clockIn As DateTime
Dim clockOut As DateTime
Private Sub btnClkIn_Click(sender As System.Object, e As System.EventArgs) Handles btnClkIn.Click
clockIn = DateTime.Now
Try
str = " host = localhost ; username = 'username'; password = 'password'; database= vb_timeclock; pooling = false;"
conn.ConnectionString = str
conn.Open()
MyCmd.CommandText = "INSERT INTO hours SET Employee = '" & LoginForm1.txtUserName.Text & "', Time = '" & lblTime.Text & "', Date = '" & lblDate.Text & "', In_Out = 'IN'"
MyCmd.Connection = conn
MyCmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show("Problem connecting to database: " & ex.Message)
End Try
End Sub
Private Sub btnClkOut_Click(sender As System.Object, e As System.EventArgs) Handles btnClkOut.Click
clockOut = DateTime.Now
Dim ts As TimeSpan = clockOut - clockIn
Dim hours As Integer = ts.Hours
Dim minutes As Integer = ts.Minutes
Try
str = " host = localhost ; username = 'username'; password = 'password'; database = vb_timeclock; pooling = false;"
conn.ConnectionString = str
Dim calHrs As New MySqlCommand("SELECT * FROM hours", conn)
Dim reader As MySqlDataReader
conn.Open()
reader = calHrs.ExecuteReader
'I get an error hear saying "There is already an open DataReader associated with this Connection which must be closed first.
While reader.Read
Dim TimeIn As String = CType(reader("Time"), String)
lblTimeIn.Text = TimeIn
End While
'Inserts ClockOut info into Database
MyCmd.CommandText = "INSERT INTO hours SET Employee = '" & LoginForm1.txtUserName.Text & "', Time = '" & lblTime.Text & "', Date = '" & lblDate.Text & "', In_Out = 'OUT', Daily_Hrs = '" & hours.ToString & "hr. " & minutes.ToString & "min.'"
MyCmd.Connection = conn
MyCmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MessageBox.Show("Problem connecting to database: " & ex.Message)
End Try
End Sub
