mphillip85
asked on
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?
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.
For example in stead of "Select id, mydate, myinteger" you can do "Select id, isnull(mydate,'1/1/1900'),
ASKER
On Error Resume Next
Me.DS_AR_InvoiceDetails.Cl ear()
Me.DA_AR_InvoiceDetails.Se lectComman d.CommandT ext = "Select InvNo,ReadFromDate,ReadToD ate,MeterN umber,PID FROM InvoiceDetails Order By InvNo;"
Me.DA_AR_InvoiceDetails.Fi ll(Me.DS_A R_InvoiceD etails)
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.In voiceDetai ls.Rows.Co unt - 1
Me.DS_AR_PaymentsTemp.Clea r()
Me.DA_AR_PaymentsTemp.Sele ctCommand. CommandTex t = "Select * FROM Payments WHERE InvNo = " & Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" InvNo") _
& " And ReadFromDate ='" & Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" ReadFromDa te") _
& "' And ReadToDate ='" & Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" ReadToDate ") _
& "' And MeterNumber = " & Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" MeterNumbe r") & ";"
Me.DA_AR_PaymentsTemp.Fill (Me.DS_AR_ PaymentsTe mp)
myDetails_InvNo = Nothing
myDetails_ReadFrom = Nothing
myDetails_ReadTo = Nothing
myDetails_MeterNo = Nothing
myDetails_InvNo = Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" InvNo")
myDetails_ReadFrom = Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" ReadFromDa te")
myDetails_ReadTo = Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" ReadToDate ")
myDetails_MeterNo = Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" MeterNumbe r")
If Me.DS_AR_PaymentsTemp.Paym ents.Rows. Count = 1 Then
myPID = Me.DS_AR_PaymentsTemp.Paym ents.Rows( 0)("PID")
Me.DS_AR_InvoiceDetails.In voiceDetai ls.Rows(my Details)(" PID") = myPID
Else
MsgBox(myDetails_InvNo)
End If
Next
Me.DA_AR_InvoiceDetails.Up date(Me.DS _AR_Invoic eDetails)
If Err.Number > 0 Then
MsgBox(Err.Description)
End If
Me.DS_AR_InvoiceDetails.Cl
Me.DA_AR_InvoiceDetails.Se
Me.DA_AR_InvoiceDetails.Fi
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.In
Me.DS_AR_PaymentsTemp.Clea
Me.DA_AR_PaymentsTemp.Sele
& " And ReadFromDate ='" & Me.DS_AR_InvoiceDetails.In
& "' And ReadToDate ='" & Me.DS_AR_InvoiceDetails.In
& "' And MeterNumber = " & Me.DS_AR_InvoiceDetails.In
Me.DA_AR_PaymentsTemp.Fill
myDetails_InvNo = Nothing
myDetails_ReadFrom = Nothing
myDetails_ReadTo = Nothing
myDetails_MeterNo = Nothing
myDetails_InvNo = Me.DS_AR_InvoiceDetails.In
myDetails_ReadFrom = Me.DS_AR_InvoiceDetails.In
myDetails_ReadTo = Me.DS_AR_InvoiceDetails.In
myDetails_MeterNo = Me.DS_AR_InvoiceDetails.In
If Me.DS_AR_PaymentsTemp.Paym
myPID = Me.DS_AR_PaymentsTemp.Paym
Me.DS_AR_InvoiceDetails.In
Else
MsgBox(myDetails_InvNo)
End If
Next
Me.DA_AR_InvoiceDetails.Up
If Err.Number > 0 Then
MsgBox(Err.Description)
End If
I would guess that if you used the isnull function in this line it would fix it:
Change - Me.DA_AR_InvoiceDetails.Se lectComman d.CommandT ext = "Select InvNo,ReadFromDate,ReadToD ate,MeterN umber,PID FROM InvoiceDetails Order By InvNo;"
To -
Me.DA_AR_InvoiceDetails.Se lectComman d.CommandT ext = "Select InvNo,IsNull(ReadFromDate, '1/1/1900' ) as ReadFromDate,IsNull(ReadTo Date,'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.
Change - Me.DA_AR_InvoiceDetails.Se
To -
Me.DA_AR_InvoiceDetails.Se
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If rs("fieldName") <> DBnull.value then
'assign var here
theVar = rs("fieldName")
End If