[dba-SQLServer] Reporting in ASP.NET WITHOUT SSRS or Crystal

Stuart McLachlan stuart at lexacorp.com.pg
Thu Feb 9 19:38:13 CST 2012


I generate a lot of web pages from Access/SQL etc data.  I simply build the output page with 
strings.  I find it gives me much better control over the result.

Here's a typical function:

Function CreateWebPage(PageName) As Boolean
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strFile As String
Dim strtemp As String
Dim strPagename As String
Dim lngGalleryCount As Long
Dim ff As Long
Dim ff2 As Long

strFile = TempDir() & PageName

ff = FreeFile

Open strFile For Output As #ff
ff2 = FreeFile
Open BEDir() & "\HeaderTemplate.txt" For Input As #ff2
Do While Not EOF(ff2)
  Line Input #ff2, strtemp
  If InStr(strtemp, "HEADLINEGOESHERE") > 0 Then
     strtemp = Replace(strtemp, "HEADLINEGOESHERE", DLookup("WebPageheadline", "tblWebPages", "WebPageName = '" & PageName & "'"))
  End If
  If InStr(strtemp, "BYLINEGOESHERE") > 0 Then
     strtemp = Replace(strtemp, "BYLINEGOESHERE", DLookup("WebPageByline", "tblWebPages", "WebPageName = '" & PageName & "'"))
  End If
  Print #ff, strtemp
Loop
Close #ff2
If DCount("*", "qryWeblisting", "webpage = '" & PageName & "'") = 0 Then
    Print #ff, "<strong>THERE ARE CURRENTLY NO PROPERTIES IN THIS CATEGORY</strong>"
Else
    Set rs = CurrentDb.OpenRecordset("select * from qryWebListing where WebPage = '" & PageName & "';")
    Print #ff, "<table><col width = ""20%""><col width = ""60%""><col width = ""20%"">"
    lngGalleryCount = 0
    While Not rs.EOF
        Print #ff, "<tr>"
        If rs!Thumb > "" Then
            Createthumb rs!PropertyNumber, rs!Photo
            CreateWebPic rs!PropertyNumber, rs!Photo
            lngGalleryCount = lngGalleryCount + 1
            Print #ff, "<td valign = ""Top""><span class=title>" & rs!LocnName & "</SPAN><BR>"
            Print #ff, "<div class=""highslide - gallery"">"
            Print #ff, "<a id=""" & rs!PropertyNumber & """ href=""property_images/" & rs!WebPic & """ class=""highslide"" onclick=""return hs.expand(this, { slideshowGroup: " & lngGalleryCount & " } )"">"
            Print #ff, "<img src=""property_images/" & rs!Thumb & """ alt=""Highslide JS"" title=""Click to enlarge"" />"
            Print #ff, "<br><small>Click for Details</small></a>"
            Print #ff, "<div class=""highslide-caption"">" & rs!Description & "</div>"
'Get additional photos
            Set rs2 = CurrentDb.OpenRecordset("select * from qryWebPics where Primary = False and Property_PK = " & rs!Property_PK)
            If Not rs2.EOF Then
                Print #ff, "<div class=""hidden - Container"">"
                Do
                    CreateWebPic rs!PropertyNumber, rs2!Photo
                    Print #ff, "<a href=""property_images/" & rs2!WPName & """ class=""highslide"" onclick=""return hs.expand(this,{ thumbnailId: '" & rs!PropertyNumber & "', slideshowGroup: " & lngGalleryCount & " })""></a>"
                    Print #ff, "<div class=""highslide-caption"">" & rs!Description & "</div>"
                    rs2.MoveNext
                Loop Until rs2.EOF
                rs2.Close
                Print #ff, "</div>    "
             End If
             Print #ff, "</div>    "
        Else
            Print #ff, "<td valign = ""Top""><span class=title>" & rs!LocnName & _ &
                   "</SPAN><BR> <img src=""property_images/" & _
                  "NoImage.gif"" width=""128"" height=""96"" align=""Left"" border=""0"" hspace=""5"" ></td>"
        End If
        Print #ff, "<td valign = ""Top"" style= ""border-bottom: groove""> <STRONG><br><a Name =""" & rs!PropertyCategory & rs!PropertyType & Format$(rs!PropertyNumber, "0000") & """>" & rs!PropertyCategory & rs!PropertyType & Format$(rs!PropertyNumber, "0000") & "</a> - " & rs!Headline & "</STRONG><BR>" & rs!ShortDescr & "</td>"
        Print #ff, "<td valign = ""Top"" align = ""center""><br><strong>" & rs!DisplayPrice & "</strong><br><a href = ""brochures/SRE" & Format$(rs!PropertyNumber, "0000") & ".pdf""><img src=""images/Acrobat_1.png"" width=""48"" height=""48"" border=""0"" alt=""Acrobat_1.png - 3921 Bytes""><br><small>Download Brochure</small></a></td>"
        Print #ff, "</tr>"
        rs.MoveNext
    Wend
    rs.Close
    Print #ff, "</table>"
    'End Body
End If
'Footer Template
Open BEDir() & "\FooterTemplate.txt" For Input As #ff2
Do While Not EOF(2)
  Line Input #ff2, strtemp
  Print #ff, strtemp
Loop
Close #ff2
Close #ff
'Publish
FTPPut DLookup("BaseDir", "usysweb"), strFile
End Function




On 9 Feb 2012 at 19:14, Steve Erbach wrote:

> Dear Group,
> 
> Does anyone create ASP.NET/SQL Server applications that generate reports
> WITHOUT using SQL Server Reporting Services or Crystal Reports? I'm
> converting an Access application to an ASP.NET app with SQL Server back end
> and I don't think I'll be able to persuade the client to buy either.
> 
> Have you translated Access reports to the web WITHOUT SSRS or Crystal?
> 
> -- 
> Regards,
> 
> Steve Erbach
> Neenah, WI
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 




More information about the dba-SQLServer mailing list