Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


cast from type dbnull to type date is not valid

Posted on 2007-11-26
Medium Priority
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 1500 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

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

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…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

705 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