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

Recommended Answers

All 2 Replies

Hi,

Readers, always have to be closed...
After "End While".. write this :

Reader.Close()

Regards
Veena

Oh thank you!
I can't believe I missed that! I guess sometimes we all need a second pair of eyes. :P

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.