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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: (…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now