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

Question

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

The data mismatch is because you are not using SQL parameters. Period. The DbNull conversion error may result when no records match the query passed, which may be malformed due to not using SQL Parameters. Also your app is leaking resources. And use SQL Parameters. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jul 14, 2016 at 0:34

jac · Accepted Answer · 2016-07-13 23:16:34Z

1

I haven't written Access queries in years and do not have it to try now, but if I remember correctly DateTime values in the query need to be surrounded with hash marks (#).

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

answered Jul 13, 2016 at 23:16

jac

9,7542 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2016-07-14 08:49:11Z

0

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") & "#"

answered Jul 14, 2016 at 8:49

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

1 Comment

Edgar Serna Over a year ago

Thank you very very much

Mikegrann · Accepted Answer · 2016-07-13 21:54:10Z

0

I suspect this is because you use Textbox.Text for your datepickers. You can only use that property when the control has focus. Try replacing those with .Value ?

answered Jul 13, 2016 at 21:54

Mikegrann

1,0817 silver badges17 bronze badges

1 Comment

Edgar Serna Over a year ago

I changed to .Value and now the error message shows this "Syntax error (missing operator) in the expression 'IdEmployee = 1 AND Date >= 02/07/2016 05:06:37 pm AND Date >= 08/07/2016 05:06:37 pm'. "

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related