cast from type dbnull to type date is not valid

Posted on 2007-11-26
Last Modified: 2008-02-01
I am just updating a single column/field in a record as I loop through a match but some update and others give the error of dbnull and date.  I am updatnig an integer column only.  what could be wrong?
Question by:mphillip85
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Expert Comment

ID: 20352556
This can happen when you have a recordset (or dataset) which contains a NULL value FROM the database that is being assigned to a variable inside your vb code...  Specifically, your vb variable is declared as a dateTime and you are trying to assign it to a value from the DB which is null. Find the code and do something like this around it:

If rs("fieldName") <> DBnull.value then
   'assign var here
   theVar = rs("fieldName")
End If

Expert Comment

ID: 20352575
Hard to troubleshoot without seeing your exact code...but usually this error is telling you that there is a NULL where it expects a date.  ONe way around this in your query/stored procedure is to use the SQL ISNULL function to wrap fields that may be null....

For example in stead of "Select id, mydate, myinteger" you can do "Select id, isnull(mydate,'1/1/1900'), isnull(myinteger,0)"  this will swap the value you provide in place of your nulls.

Author Comment

ID: 20353251
On Error Resume Next
        Me.DA_AR_InvoiceDetails.SelectCommand.CommandText = "Select InvNo,ReadFromDate,ReadToDate,MeterNumber,PID FROM InvoiceDetails Order By InvNo;"

        Dim myDetails As Integer = 0
        Dim myPayments As Integer = 0

        Dim myDetails_InvNo As Integer
        Dim myDetails_ReadFrom As Date
        Dim myDetails_ReadTo As Date
        Dim myDetails_MeterNo As String
        Dim myPID As Integer

        For myDetails = 0 To Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows.Count - 1
            Me.DA_AR_PaymentsTemp.SelectCommand.CommandText = "Select * FROM Payments WHERE InvNo = " & Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("InvNo") _
            & " And ReadFromDate ='" & Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadFromDate") _
            & "' And ReadToDate ='" & Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadToDate") _
            & "' And MeterNumber = " & Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("MeterNumber") & ";"


            myDetails_InvNo = Nothing
            myDetails_ReadFrom = Nothing
            myDetails_ReadTo = Nothing
            myDetails_MeterNo = Nothing

            myDetails_InvNo = Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("InvNo")
            myDetails_ReadFrom = Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadFromDate")
            myDetails_ReadTo = Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadToDate")
            myDetails_MeterNo = Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("MeterNumber")

            If Me.DS_AR_PaymentsTemp.Payments.Rows.Count = 1 Then

                myPID = Me.DS_AR_PaymentsTemp.Payments.Rows(0)("PID")
                Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("PID") = myPID

            End If

        If Err.Number > 0 Then
        End If

Expert Comment

ID: 20353305
I would guess that if you used the isnull function in this line it would fix it:

Change - Me.DA_AR_InvoiceDetails.SelectCommand.CommandText = "Select InvNo,ReadFromDate,ReadToDate,MeterNumber,PID FROM InvoiceDetails Order By InvNo;"

To -

 Me.DA_AR_InvoiceDetails.SelectCommand.CommandText = "Select InvNo,IsNull(ReadFromDate,'1/1/1900') as ReadFromDate,IsNull(ReadToDate,'1/1/1900') as ReadToDate,MeterNumber,PID FROM InvoiceDetails Order By InvNo;"

This assumes that 1/1/1900 won't match any legit records in the match you do further down the code.  if it would then just use another date that would be out of the range.

Accepted Solution

Chumad earned 500 total points
ID: 20353877
Do this in your code:

Instead of:

myDetails_ReadTo = Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadToDate")


if Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadToDate") <> DBNull.Value then
   myDetails_ReadTo = Me.DS_AR_InvoiceDetails.InvoiceDetails.Rows(myDetails)("ReadToDate")
end if

It's also usually safe to do this all the time you are getting data. Check for a NULL before any assignments you do.

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question