Can’t retrieve data from Access DB query to my VB program

So this is my code so far… I have 2 DateTimePickers in which the user selects the first day of the week and the last day of the week, once selected these two I want to calculate the hours worked on each day and add them so I can display the amount of hours worked of the interval of days selected.

My Work table contains an idEmployee, Date, ArrivalTime, DepartureTime, and a calculated column(Hrs_worked) that displays the amount of hours worked on that day.

When I run the program a message box pops an shows this “Conversion from type ‘DBNull’ to type ‘String’ is not valid” or “Mismatch of data in the expression of criteria” Do I need to change the datetimepickers values to string values?

Im working on Visual Basic Express 2010 with an Access Database

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

    Dim provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    Dim dataFile As String = "E:SIDB.accdb"
    Dim connString As String = provider & dataFile
    Dim myConnection As OleDbConnection
    Dim cmd As OleDbCommand

    Dim qHrsWorked As String = "SELECT SUM(Hrs_worked) FROM Work WHERE IdEmployee = " & idEmptxt.Text & " AND Date >= " & firstDayDTP.Value & " AND Date <= " & lastDayDTP.Value & ""

    myConnection = New OleDbConnection(connString)
    Try
        myConnection.Open()
        cmd = New OleDbCommand(qHrsWorked, myConnection)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()

        While reader.Read
            hrsWorkedtxt.Text = reader.Item(0)

        End While
        reader.Close()
        cmd.Dispose()
        myConnection.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try


End Sub

Answer

In SQL you must use a formatted string expression for the date values:

Dim qHrsWorked As String = "SELECT SUM(Hrs_worked) FROM Work WHERE IdEmployee = " & idEmptxt.Text & " AND Date >= #" & firstDayDTP.Value.ToString("yyyy'/'MM'/'dd") & "# AND Date <= #" & lastDayDTP.Value.ToString("yyyy'/'MM'/'dd") & "#"

Advertisement

Answer