CODEWITHSUNDEEP
sqlvb.netms-accessoledbcommand
Programming Dreamer
Programming Dreamer

Reputation: 235

Something wrong with my code?

Is there something wrong with my code? I'm having a " Conversion from type 'DBNull' to type 'String' is not valid" error.

Function called (GetNoofHoursOfTImeIn):

Public Shared Function GetNoofHoursofTImeIn(ByVal search As String, ByVal dfrom As   DateTime, ByVal dto As DateTime) As DataTable
Dim dtoffenseinfo As New DataTable

If Not DBConnection.State = ConnectionState.Open Then       
    DBConnection.Open()
End If

Using cmd As New OleDbCommand
    cmd.Connection = DBConnection
    cmd.CommandText = _
            "SELECT SUM(No_of_Hour) AS THour FROM EmployeeAttendance " & _
            "WHERE EmployeeID = ? " & _
                "AND WorkingDate BETWEEN ? AND ?"
    cmd.Parameters.AddWithValue("?", search)
    cmd.Parameters.AddWithValue("?", dfrom.Date)
    cmd.Parameters.AddWithValue("?", dto.Date)
    Using adapter As New OleDbDataAdapter(cmd)
        adapter.Fill(dtoffenseinfo)
    End Using
End Using

DBConnection.Close()
Return dtoffenseinfo
End Function

Code behind:

 txtPresentDays.Text = ClsAttendance.GetNoofHoursofTImeIn(EmployeeID.Text, MyDateString, MyDateString2).Rows(0).Item("THour")

I know this error is common but until now I don't get quite the idea how or why I'm getting this.

Thanks for any enlightenment!

Upvotes: 0

Views: 67

Answers (1)

Guffa
Guffa

Reputation: 700242

You get a null value when there are zero records that match the condition. You can use the iif function to turn that value into a zero:

SELECT IIF(SUM(No_of_Hour) Is Null, 0, SUM(No_of_Hour)) ...

Upvotes: 1

Related Questions