Re: Operator is not valid for type 'DBNull' and type 'Date'
me_at_privacy.net
Date: 06/08/04
- Next message: fred: "style sheets"
- Previous message: Chris Marsh: "Email from ASPX project failing ..."
- In reply to: Scott M.: "Re: Operator is not valid for type 'DBNull' and type 'Date'"
- Next in thread: Scott M.: "Re: Operator is not valid for type 'DBNull' and type 'Date'"
- Reply: Scott M.: "Re: Operator is not valid for type 'DBNull' and type 'Date'"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Jun 2004 09:58:52 -0400
<<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
> > >
> > >
> >
> >
>
>
- Next message: fred: "style sheets"
- Previous message: Chris Marsh: "Email from ASPX project failing ..."
- In reply to: Scott M.: "Re: Operator is not valid for type 'DBNull' and type 'Date'"
- Next in thread: Scott M.: "Re: Operator is not valid for type 'DBNull' and type 'Date'"
- Reply: Scott M.: "Re: Operator is not valid for type 'DBNull' and type 'Date'"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
