asp net application: Operator is not valid for type DBNull and type Date

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,

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

<me@.privacy.net> wrote in message
news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> 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
<me@.privacy.net> wrote in message
news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> 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".
--
John Saunders
johnwsaundersiii at hotmail
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"><%= thisfield %></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 & ">" & sTotals &
"</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 & ">" & atotals &
"</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"><%= (sTotal)
%>
<%
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"><%= sTotal1
%>
<%
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>
<%
Response.Write("The report ran in " & Session("rpttime") & " second(s)")
%>
</td>
</tr>
</table
"John Saunders" <johnwsaundersiii@.notcoldmail.com> wrote in message
news:ubatuDNTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> <me@.privacy.net> wrote in message
> news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> > 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".
> --
> John Saunders
> johnwsaundersiii at hotmail
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.

--
John Saunders
johnwsaundersiii at hotmail

<me@.privacy.net> wrote in message
news:uWaWXcNTEHA.368@.TK2MSFTNGP10.phx.gbl...
> 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"> color="#000000"><%= thisfield %></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 & ">" & sTotals &
> "</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 & ">" & atotals &
> "</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"><%=
(sTotal)
> %>
> <%
> 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"><%= sTotal1
> %>
> <%
> 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>
> <%
> Response.Write("The report ran in " & Session("rpttime") & "
second(s)")
> %>
> </td>
> </tr>
> </table>
>
>
>
> "John Saunders" <johnwsaundersiii@.notcoldmail.com> wrote in message
> news:ubatuDNTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > <me@.privacy.net> wrote in message
> > news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> > > 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".
> > --
> > John Saunders
> > johnwsaundersiii at hotmail
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.

<me@.privacy.net> wrote in message
news:uWaWXcNTEHA.368@.TK2MSFTNGP10.phx.gbl...
> 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"> color="#000000"><%= thisfield %></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 & ">" & sTotals &
> "</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 & ">" & atotals &
> "</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"><%=
(sTotal)
> %>
> <%
> 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"><%= sTotal1
> %>
> <%
> 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>
> <%
> Response.Write("The report ran in " & Session("rpttime") & "
second(s)")
> %>
> </td>
> </tr>
> </table>
>
>
>
> "John Saunders" <johnwsaundersiii@.notcoldmail.com> wrote in message
> news:ubatuDNTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > <me@.privacy.net> wrote in message
> > news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> > > 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".
> > --
> > John Saunders
> > johnwsaundersiii at hotmail
<<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.

>
> <me@.privacy.net> wrote in message
> news:uWaWXcNTEHA.368@.TK2MSFTNGP10.phx.gbl...
> > 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"> > color="#000000"><%= thisfield %></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 & ">" & sTotals &
> > "</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 & ">" & atotals &
> > "</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"><%=
> (sTotal)
> > %>
> > <%
> > 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"><%=
sTotal1
> > %>
> > <%
> > 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>
> > <%
> > Response.Write("The report ran in " & Session("rpttime") & "
> second(s)")
> > %>
> > </td>
> > </tr>
> > </table>
> > "John Saunders" <johnwsaundersiii@.notcoldmail.com> wrote in message
> > news:ubatuDNTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > > <me@.privacy.net> wrote in message
> > > news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> > > > 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".
> > > --
> > > John Saunders
> > > johnwsaundersiii at hotmail
> >
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...

<me@.privacy.net> wrote in message
news:umsWzCWTEHA.3140@.tk2msftngp13.phx.gbl...
> <<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
>
>
> > <me@.privacy.net> wrote in message
> > news:uWaWXcNTEHA.368@.TK2MSFTNGP10.phx.gbl...
> > > 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"> > > color="#000000"><%= thisfield %></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 & ">" & sTotals &
> > > "</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 & ">" & atotals &
> > > "</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"><%=
> > (sTotal)
> > > %>
> > > <%
> > > 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"><%=
> sTotal1
> > > %>
> > > <%
> > > 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>
> > > <%
> > > Response.Write("The report ran in " & Session("rpttime") & "
> > second(s)")
> > > %>
> > > </td>
> > > </tr>
> > > </table>
> > > > > > > > > "John Saunders" <johnwsaundersiii@.notcoldmail.com> wrote in message
> > > news:ubatuDNTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > > > <me@.privacy.net> wrote in message
> > > > news:Owkao5MTEHA.2408@.tk2msftngp13.phx.gbl...
> > > > > 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".
> > > > --
> > > > John Saunders
> > > > johnwsaundersiii at hotmail
> > > > > >

Saturday, March 24, 2012

Operator is not valid for type DBNull and type Date

0 comments:

Post a Comment

asp net application

Blog Archive

About Me