Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

cast from type dbnull to type date is not valid

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
mphillip85
Asked:
mphillip85
  • 2
  • 2
1 Solution
 
ChumadCommented:
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
 
bcaff86Commented:
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
 
mphillip85Author Commented:
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
 
bcaff86Commented:
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
 
ChumadCommented:
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 problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now