Operator is not valid for type 'DBNull' and type 'Date'

[Guest]

Source Error:

Line 173: sData(rownumber - 1, lcnt) = WhatCol.Value
Line 174: End IF
Line 175: If (sData(rownumber, lcnt) = sData(rownumber - 1,
lcnt)) AND (WhatCol.Type <> 5 AND WhatCol.Type <> 6) Then
Line 176: If rownumber = 3 Then
Line 177: thisfield = WhatCol.Value


In that, the Whatcol.Value is a null value, and I am trying to return a date
field, which is calculated MAX(j5_org.invoice_date). The item hasn't
shipped, so it doesn't return a shipped date.

How can I get this code resolved to where it doesn't blow up on this?

This code runs in traditional ASP, but does not run in ASP.NET, because of
the error above.

Thanks,

SC

 

[Scott M.]

If Not IsDBNull(WhatCol.Value) then
all your code
End If

 

[John Saunders]

Source Error:

Line 173: sData(rownumber - 1, lcnt) = WhatCol.Value
Line 174: End IF
Line 175: If (sData(rownumber, lcnt) = sData(rownumber - 1,
lcnt)) AND (WhatCol.Type <> 5 AND WhatCol.Type <> 6) Then
Line 176: If rownumber = 3 Then
Line 177: thisfield = WhatCol.Value


In that, the Whatcol.Value is a null value, and I am trying to return a date
field, which is calculated MAX(j5_org.invoice_date). The item hasn't
shipped, so it doesn't return a shipped date.

How can I get this code resolved to where it doesn't blow up on this?

This code runs in traditional ASP, but does not run in ASP.NET, because of
the error above.

Could you show us where you're getting WhatCol from?

A lot of things run in VBScript that don't run in VB.NET. That's because
VBScript allowed developers to get away with murder. If you really want to
improve your coding, turn Options Strict On. You'll find many places where
VB.NET was trying to "guess the right thing".

 

[Guest]

The source data is from a SQL query that I'm running to display a web page.
Some sample results are:


BUSINESS AREA1 REPORTGROUP1 06-02-2004 50257.0000
BUSINESS AREA1 REPORTGROUP2 06-03-2004 30604.0000
BUSINESS AREA1 REPORTGROUP3 05-28-2004 2475.0000
BUSINESS AREA1 REPORTGROUP4 06-03-2004 7763.0000
BUSINESS AREA1 REPORTGROUP5 NULL 3.0000
BUSINESS AREA2 REPORTGROUP1 06-04-2004
219628.0000
BUSINESS AREA2 REPORTGROUP2 06-04-2004
126966.0000
BUSINESS AREA2 REPORTGROUP3 06-03-2004 2599.0000
BUSINESS AREA2 REPORTGROUP4 06-04-2004
692.0000
BUSINESS AREA2 REPORTGROUP5 06-04-2004
21805.0000


What it's doing is printing out the first column, then second column, and
then it dies, when it hits the date column that's null.

Like you said, VBScript would let you get away with some stuff, but I have
no idea why this null in a return dataset would cause this thing to give me
an error in dotnet. Traditional ASP didn't have a problem with it. I'm not
totaling on the field or anything, either.




The code to do the whole routine is here:



<tr>
<td><table border="9" align="center"
style="font-family: Arial; font-size: 9pt; border: medium none"
bordercolor="#000000">
<%
End IF
%>

<tr>
<%
'Put Headings On The Table of Field Names
'redim sData(rs.recordcount,175)
rownumber = 3
colnumber = 0
BeenThere = 0
lcnt = 0
numcols = 0
actualcols = 0
NrGroups = 0
For Each WhatCol In rstemp.Fields
If Len(WhatCol.Name) <> 1 Then
thisfield = WhatCol.Name
GroupField(lcnt) = 0
numcols = numcols + 1
%>

<td bgcolor="#FFFFFF" align="center"><p align="center"><font
color="#000000"><b><%= thisfield %></b></font></td>
<%
Else
'All 1 Character Field Names are Grouping Fields
'This sets up the grouping with hierachy determined from left to
right in SQL
GroupField(lcnt) = 1
NrGroups = NrGroups + 1
End IF
actualcols = actualcols + 1
lcnt = lcnt + 1
Next
TBorder = "border:medium none "
%>

</tr>
<%
'Now lets grab all the records
rownumber = 3
bgcolor = "white"
Do Until rstemp.EOF
%>

<tr>
<%
lcnt = 0
'Prepare Row for Printing
For Each WhatCol In rstemp.Fields
'Load data to array
sData(rownumber, lcnt) = WhatCol.Value
'If Data Type is currency, real or float
If WhatCol.Type = 6 Or WhatCol.Type = 5 Then
GrandTotal(lcnt + 1, 0) = GrandTotal(lcnt + 1, 0) +
WhatCol.Value
End IF
'Suppress Duplicates for Printing
'Set up the first row for Grouping
If rownumber = 3 Then
sData(rownumber - 1, lcnt) = WhatCol.Value
End IF
If (sData(rownumber, lcnt) = sData(rownumber - 1, lcnt)) AND
(WhatCol.Type <> 5 AND WhatCol.Type <> 6) Then
If rownumber = 3 Then
thisfield = WhatCol.Value
GroupField(lcnt) = GroupField(lcnt) + 1
Else
'Added the linecount < NrGroups times 2 which not
suppress duplicates on any field except Group Fields
'If you need to change it back insert (thisfield =
showblank) in place of If Statement.
If lcnt < (NrGroups * 2) + 1 Then
thisfield = showblank
GroupField(lcnt) = GroupField(lcnt) + 1
Else
thisfield = WhatCol.Value
End IF
End IF
Else
thisfield = sData(rownumber, lcnt)
GroupField(lcnt) = 0
End IF
'Total Rows Field Length is 1 Character
If Len(WhatCol.Name) <> 1 Then
If WhatCol.Type = 6 Or WhatCol.Type = 5 Then
thisfield = FormatNumber(CDbl(thisfield), 0)
End IF
%>

<td bgcolor="<%= bgcolor %>" valign="top" align="right"
style="border: medium none"><%= thisfield %>
<%
End IF
lcnt = lcnt + 1
Next
rstemp.MoveNext()
'set or reset data
ralign = "right"
'Change colors for every other line
If chgcolor = 0 Then
bgcolor = "#FFFFCC"
chgcolor = 1
Else
bgcolor = "White"
chgcolor = 0
End IF
'End Totals and Grouping
lcnt = 0
If Not rstemp.EOF Then
For Each WhatCol In rstemp.Fields
If Len(WhatCol.Name) = 1 Then
If WhatCol.Value.ToString() <> CStr(sData(rownumber,
lcnt)) Then
PrintTotal(lcnt) = lcnt
GroupField(lcnt) = 1
Else
'if rownumber<>3 then
rowCnt(lcnt) = rowCnt(lcnt) + 1
'end if
GroupField(lcnt) = 0
End IF
lcnt = lcnt + 1
End IF
Next
End IF
If rstemp.EOF Then
lcnt = 0
For Each WhatCol In rstemp.Fields
If Len(WhatCol.Name) = 1 Then
If sData(rownumber - 1, lcnt) = sData(rownumber, lcnt)
AND CStr(sData(rownumber, lcnt)) <> "" Then
PrintTotal(lcnt) = lcnt
GroupField(lcnt) = 1
'rowcnt(lcnt) = rowcnt(lcnt)+1
Else
PrintTotal(lcnt) = NrGroups + 1
'if rownumber<>3 then
rowCnt(lcnt) = 0
'end if
GroupField(lcnt) = 0
End IF
'lcnt = lcnt + 1
End IF
lcnt = lcnt + 1
Next
End IF
lcnt = 0
iGroup = 0
For tcnt = 0 To NrGroups
pcnt = NrGroups - tcnt
If CInt(PrintTotal(pcnt)) < NrGroups AND (CInt(GroupField(pcnt))
= 1 AND CInt(rowCnt(pcnt)) > 0) Then
GroupField(pcnt) = 0
'rowcnt(pcnt) = 0
If CInt(rowCnt(pcnt)) > 0 Then
Response.Write("</tr><tr>")
End IF
For Each WhatCol In rstemp.Fields
If Len(WhatCol.Name) <> 1 Then
If CInt(rowCnt(pcnt)) < 1 AND (WhatCol.Type = 5 AND
WhatCol.Type = 6) Then
GrpTotal(lcnt, PrintTotal(pcnt)) = 0
End IF
If Len(WhatCol.Name) <> 1 AND (WhatCol.Type <> 5 AND
WhatCol.Type <> 6) Then
If lcnt = CInt((NrGroups) + PrintTotal(pcnt))
Then
sTotals = CStr(sData(rownumber, lcnt))
'& "-" & rowcnt(pcnt) & WHATCOL.TYPE
Else
sTotals = showblank
End IF
If CInt(rowCnt(pcnt)) > 0 Then
Response.Write("<TD style=" & TBorder & "
ALIGN=" & ralign & " BGCOLOR=" & bgcolor & "><STRONG>" & sTotals &
"</FONT></STRONG></TD>")
End IF
End IF
If Len(WhatCol.Name) <> 1 AND (WhatCol.Type = 5 Or
WhatCol.Type = 6) Then
For colcount = 0 To rowCnt(pcnt)
atotals = sData(rownumber - colcount, lcnt)
+ atotals
Next
atotals = FormatNumber(CDbl(atotals), 0)
If CInt(rowCnt(pcnt)) > 0 Then
Response.Write("<TD style=" & TBorder & "
ALIGN=" & ralign & " BGCOLOR=" & bgcolor & "><STRONG>" & atotals &
"</FONT></STRONG></TD>")
End IF
GrpTotal(lcnt, PrintTotal(pcnt)) = 0
atotals = 0
End IF
End IF
lcnt = lcnt + 1
Next
If CInt(rowCnt(pcnt)) > 0 Then
Response.Write("</tr><tr>")
BeenThere = BeenThere + 1
End IF
rowCnt(pcnt) = 0
'Change colors for every other line
If chgcolor = 0 Then
bgcolor = "#FFFFCC"
chgcolor = 1
Else
bgcolor = "White"
chgcolor = 0
End IF
End IF
lcnt = 0
Next
rownumber = rownumber + 1
Loop
%>

</td>
</tr>
<tr>
<%
'Grand Total Routine
lcnt = 0
col1 = 0
If NrGroups > 0 Then
'If any 1 character fields where defined, Grand Totals will appear
For Each WhatCol In rstemp.Fields
If WhatCol.Type = 6 Or WhatCol.Type = 5 Then
sTotal = FormatNumber(CDbl(GrandTotal(lcnt + 1, 0)), 0)
%>

<td bgcolor="<%= bgcolor %>" valign="top" align="right"
style="border-left: medium none; border-right: medium none;
border-top: medium double; border-bottom: medium none"><strong><%= (sTotal)
%></strong>
<%
sTotal = 0
Else
If Len(WhatCol.Name) <> 1 Then
If col1 = 0 Then
sTotal1 = "Grand Total"
col1 = 1
Else
sTotal1 = showblank
End IF
Else
sTotal1 = showblank
End IF
If Len(WhatCol.Name) <> 1 Then
%>
</td>
<td bgcolor="<%= bgcolor %>" valign="top" align="right"
style="border-left: medium none; border-right: medium none;
border-top: medium double; border-bottom: medium none"><strong><%= sTotal1
%></strong>
<%
End IF
End IF
lcnt = lcnt + 1
Next
%>
</td>
</tr>
<%
End IF
Session.Add("rptend", Timer())
Session.Add("rpttime", FormatNumber(CDbl(Session("rptend")) -
CDbl(Session("rptstart")), 1))
rstemp.Close()
rstemp = Nothing
conntemp.Close()
conntemp = Nothing
%>

</table>
<p><small><%
Response.Write("The report ran in " & Session("rpttime") & " second(s)")
%>
</small></td>
</tr>
</table>

 

[John Saunders]

I'll take a closer look at your code later, but the reason it doesn't let
you get away with it is that DBNull.Value is not the same thing as
String.Empty. It's also not the same thing as Nothing. In fact, DBNull is a
datatype with a single value, DBNull.Value. It can't be converted to or from
anything. It doesn't mean "blank", it means "I don't know". It's up to your
code to decide what "I don't know" means.

 

[Scott M.]

You really should leave this as a classic ASP page until you are ready to
fully convert it to ASP .NET. You are still using all the classic ASP
methodologies in an ASP .NET architecture. You'll find that using a
DataGrid and binding it to the data returned from your query is much easier
than all the inline coding that you are doing.

And, from my earlier post, if you check a field for DBNull (either by
comparing against DBNull.Value or using IsDBNull(field)) before you attempt
to use it, you're problem will be solved.

 

[Guest]

<<You really should leave this as a classic ASP page until you are ready to
fully convert it to ASP .NET. You are still using all the classic ASP
methodologies in an ASP .NET architecture. You'll find that using a
DataGrid and binding it to the data returned from your query is much easier
than all the inline coding that you are doing.>>

We had started doing exactly what you're describing. However, we
encountered a problem for which I have been unable to find a solution. That
problem was saving to Excel. Lots of the reports we create, our users will
save them to excel and work with them, in terms of developing forecasts,
etc. With the controls on screen, we found that we had a little better than
a 50% failure rate in either opening the file in excel, or if the report was
a large one, it'd prompt you a number of times for a user id & password and
wouldn't ever produce the excel sheet. The problem in the opening the file
in excel was due directly to the controls being on the screen. Since we
have a number of variables that go in to producing the reports, it was
impractical to try to convert a 2nd page that'd load the report in a format
that COULD be saved and opened in Excel.

I posted the problem I had with both of them numerous times in one of the
framework.aspnet groups, and was never able to find a satisfactory
resolution. At that point, we decided to just take our traditional asp
application, and convert it to dotnet, when we found that a number of the
reports we generated all would save & open in Excel, when we created them
with the converted report writer.

<<And, from my earlier post, if you check a field for DBNull (either by
comparing against DBNull.Value or using IsDBNull(field)) before you attempt
to use it, you're problem will be solved.>>

I've tried this, but evidently, I did not have the check in the right place.

I had:

If Not IsDBNull(WhatCol.Value) then
If (sData(rownumber, lcnt) = sData(rownumber - 1, lcnt)) AND
(WhatCol.Type <> 5 AND WhatCol.Type <> 6) Then
If rownumber = 3 Then
thisfield = WhatCol.Value
GroupField(lcnt) = GroupField(lcnt) + 1
Else
'Added the linecount < NrGroups times 2 which not
suppress duplicates on any field except Group Fields
'If you need to change it back insert (thisfield =
showblank) in place of If Statement.
If lcnt < (NrGroups * 2) + 1 Then
thisfield = showblank
GroupField(lcnt) = GroupField(lcnt) + 1
Else
thisfield = WhatCol.Value
End IF
End IF
Else
thisfield = sData(rownumber, lcnt)
GroupField(lcnt) = 0
End IF
Else
thisfield = sData(rownumber,lcnt)
GroupField(lcnt) = 0
End if

It still gave me the dbnull error.

SC

 

[Scott M.]

Well, my advice would be to re-write this code using .NET strategies and
classes, rather that just making your classic ASP code "work" inside of
..NET.

It's hard to tell exactly what the code you posted is doing because it is
incomplete. Perhaps if you posted the whole sub-routine....