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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Scott M. (s-mar_at_nospam.nospam)
Date: 06/09/04 

Date: Tue, 8 Jun 2004 23:36:38 -0400

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"><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" <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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Operator is not valid for type DBNull and type Date
    ... fully convert it to ASP .NET. ... You are still using all the classic ASP ... problem was saving to Excel. ... Lots of the reports we create, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Excel automation in vb hangs asp page.
    ... server from a windows 2000 server. ... The application has some reports which are run in word, csv, html, excel ... After the deployment all the asp pages are coming up including the reports ...
    (microsoft.public.inetserver.iis)
  • Re: Excel automation in vb hangs asp page.
    ... > server from a windows 2000 server. ... > After the deployment all the asp pages are coming up including the reports ... > except for the excel one. ...
    (microsoft.public.inetserver.iis)
  • Viewing excel files through Internet Explorer
    ... You can build your reports as a asp page with table ... it will automatically open the page as an excel doc. ... On other machines, ...
    (microsoft.public.excel.misc)
  • Re: why>?
    ... easier to do in a spreadsheet than it is to do in Excel. ... I can produce portable reports in PDF form quite easily.. ... Spreadsheets are poor platforms for writing complex ... databases don't NEED to store a different formula for each row. ...
    (microsoft.public.excel)