CODEWITHSUNDEEP
sqlvb.netms-accessvba
Edgar Serna
Edgar Serna

Reputation: 73

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

Upvotes: 0

Views: 190

Answers (3)

Gustav
Gustav

Reputation: 55806

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

Upvotes: 0

jac
jac

Reputation: 9726

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

Upvotes: 1

Mikegrann
Mikegrann

Reputation: 1081

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 ?

Upvotes: 0

Related Questions