Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

cast from type dbnull to type date is not valid

Posted on 2007-11-26
5
Medium Priority
?
736 Views
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?
0
Comment
Question by:mphillip85
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Chumad
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
0
 
LVL 3

Expert Comment

by:bcaff86
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.
0
 

Author Comment

by:mphillip85
ID: 20353251
On Error Resume Next
        Me.DS_AR_InvoiceDetails.Clear()
        Me.DA_AR_InvoiceDetails.SelectCommand.CommandText = "Select InvNo,ReadFromDate,ReadToDate,MeterNumber,PID FROM InvoiceDetails Order By InvNo;"
        Me.DA_AR_InvoiceDetails.Fill(Me.DS_AR_InvoiceDetails)

        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.DS_AR_PaymentsTemp.Clear()
            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") & ";"

            Me.DA_AR_PaymentsTemp.Fill(Me.DS_AR_PaymentsTemp)

            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

            Else
                MsgBox(myDetails_InvNo)
            End If

        Next
        Me.DA_AR_InvoiceDetails.Update(Me.DS_AR_InvoiceDetails)
        If Err.Number > 0 Then
            MsgBox(Err.Description)
        End If
0
 
LVL 3

Expert Comment

by:bcaff86
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.
0
 
LVL 8

Accepted Solution

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

Instead of:

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

do:

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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 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