Saving a stopwatch to access database vb.net-CodePudding

I am creating a virtual time clock so staff can track their hours throughout the month. The user will push a button on screen which will start/stop the timer as they clock in/out. Sample code for this function is below:

Private CAstopwatch As New Stopwatch
Private Sub timerca_tick(sender As Object, e As EventArgs) Handles Timerca.Tick
    Dim elapsed As TimeSpan = CAstopwatch.Elapsed
    CATimer.Text = String.Format("{000:00}:{1:00}",
                                Math.Floor(elapsed.TotalHours),
                                elapsed.Minutes)

End Sub

Private Sub CAButton_Click(sender As Object, e As EventArgs) Handles CAButton.Click
    If CAButton.BackColor = Color.Silver Then
        CAButton.BackColor = Color.Red
        Timerca.Start()
        CAstopwatch.Start()
    ElseIf CAButton.BackColor = Color.Red Then
        CAButton.BackColor = Color.Silver
        Timerca.Stop()
        CAstopwatch.Stop()
    End If
End Sub

This code works fine, but in case of the application closing I would like to save the information to an access database so it can be restored if need be.

At the moment I am just testing this with a push of a button to save the data which executes the following code

Private Sub TestButton_Click(sender As Object, e As EventArgs) Handles TestButton.Click
    'connects application to database
    Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DataPath\Database.accdb"
    Dim SqlString As String = "update SaintStaff set StaffHours = @CAHours, RecordedTime = @Time, RecordedDate = @Date where StaffName = @Staffname "

    'updates record in  SaintStaff table.
    Using conn As New OleDbConnection(ConnString)
        conn.Open()
        Using cmd As New OleDbCommand(SqlString, conn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("@CAHours", OleDbType.VarChar).Value = CATimer.Text
            cmd.Parameters.Add("@Time", OleDbType.VarChar).Value = CurrentTime.Text
            cmd.Parameters.Add("@Date", OleDbType.VarChar).Value = CurrentDate.Text
            cmd.Parameters.Add("@Staffname", OleDbType.VarChar).Value = CAStaff.Text
            cmd.ExecuteNonQuery()
        End Using
        conn.Close()
    End Using
End Sub

I can record the date and time without issue but I get a 'Data type mismatch in criteria expression.' error when trying to record the timer text. As a test I created a new label and set it so CATimer.Text = Label.Text, so when the timer updates it updates the label too. If I change the above code to save the label.text instead of CATimer.text it works. Obviously it doesn't like something about recording the timer but I dont know what. Anyone have any ideas?

Also instead of this update command being processed on a button push, is there a way to have this happen automatically say once a minute?

Thank you

CodePudding user response：

Always store date and time as DateTime, not text, and no reason to split this information.

So, change data type of RecordedTime to DateTime, skip _RecordedDate, and replace the two lines with one:

cmd.Parameters.Add("@Time", OleDbType.Date).Value = DateTime.Now

CodePudding user response：

Turns out this was a dumb mistake, the database field was set as integer, and obviously wouldnt accept a .text field input.. changed field type to text and its resolved... stupid is as stupid does...