Looping through Break Reports
Looping through Break Reports
Looping through Break Reports
Looping through Break Reports
Looping through Break Reports
Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports
Looping through Break Reports Looping through Break Reports
Looping through Break Reports
Go Back  Xtreme Visual Basic Talk > > > Looping through Break Reports


Reply
 
Thread Tools Display Modes
  #1  
Old 05-31-2013, 10:37 PM
theduckfan theduckfan is offline
Newcomer
 
Join Date: May 2013
Posts: 1
Default Looping through Break Reports


Hi Guys,

I have a program that will create a HTML break report based on a sales person id selected by the user from a combo box.

The Sales Person ID's are loaded into a combo box from an Access Database Table.

When I select an ID from the combo box, all sales data will be loaded into a DataGridView. I can then generate a HTML based Break Report for the individual ID.

What i would like to do is upon clicking "Generate Break Report" the program will loop through all the Sales Person ID's, create individual Break Reports which I will then be able to access by clicking "Next" and "Previous" links on the HTML page.

The code I use to create the Break Report is:
Code:
Option Explicit On
Option Strict On
Imports System.Data.OleDb
Imports System.Globalization.DateTimeFormatInfo
Module mBreakReport

    Public Function g_HTMLReport(ByVal sCurrentSaleReader As String) As String
        Dim sSql As String
        Dim sConnection As String
        Dim sTableRow As String

        Dim iRowCounter As Integer
        Dim iTotal As Integer

        Dim oConn_Report As OleDbConnection 'To instantiate Connection obj.
        Dim oCmd_Report As OleDbCommand 'To Instantiate a Command obj.
        Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.

        Dim sPageText As String
        Try
            ' –- Step1: Open Output File stream (This instantiates a 
            ' -- StreamWriter object used for outputing a Text file)

            ' –- Step2: Get Sql String 

            sSql = "SELECT SALE_ID, PRODUCT_ID, SALE_DATE, DESCRIPTION, SALES_PERSON_ID, CATEGORY_ID, UNIT_PRICE, QTY, GIVEN, SURNAME FROM QSALES_REPORT WHERE SALES_PERSON_ID='" + sCurrentSaleReader + "' ORDER BY SALES_PERSON_ID;"

            ' –- Step3: Build connection string to connect to 
            ' -- an OleDB Data provider
            sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
            sConnection = sConnection & "User ID=Admin;"
            sConnection = sConnection & "Data Source="
            sConnection = sConnection & Application.StartupPath & _
            "\ComputerShop2013_STUDENTS.mdb"

            ' –- Step4: establish connection to the student database 
            ' -- using the connection string
            oConn_Report = New OleDbConnection(sConnection)
            oConn_Report.Open()

            ' –- Step5: create a database command object using the connection 
            ' -- object and set the SQL statement it will execute
            oCmd_Report = oConn_Report.CreateCommand()
            oCmd_Report.CommandText = sSql

            ' –- Step6: execute SQL command and place results into a datareader 
            oDataReader = oCmd_Report.ExecuteReader()

            'sCurrentSaleReader = CStr(oDataReader.Item("SALES_PERSON_ID"))

         
            'Do page hEADING
            sPageText = g_sHTMLHeader("Sales Person ID " & sCurrentSaleReader) & vbCrLf

            If Not oDataReader.Read() Then
                '**** There is nothing to read
                sPageText = sPageText & "</table> <br>" & vbCrLf
                sPageText = sPageText & "<font color=""red""><b>No data found.</b></font>" & vbCrLf
            Else
                iTotal = 0
                iRowCounter = 0
                Dim sPreviousSaleID As String = ""

                Dim bFirstTime As Boolean = True
                ' startNewTable
                sPageText = StartNewHTMLTable(sPageText, sCurrentSaleReader)


                ' startNewTable
                sPageText = StartNewHTMLTable(sPageText, sCurrentSaleReader)
                Do
                    'sCurrentSale = CStr(oDataReader.Item("SALES_PERSON_ID"))
                    Dim bDoBreak As Boolean = False
                    If sCurrentSaleReader <> sPreviousSaleID Then
                        bDoBreak = True
                    End If
                    If bFirstTime Then bDoBreak = False
                    bFirstTime = False
                    '**** Note: break must be done before line is put into HTML 
                    If bDoBreak Then
                        ' showBreakData

                        iRowCounter = 0
                        sPageText = showBreakData(sPageText, sCurrentSaleReader, iRowCounter, 7)
                        ' startNewTable
                        sPageText = StartNewHTMLTable(sPageText, sCurrentSaleReader)
                    End If
                    '**** Add a line of data to the table
                    'iRowCounter = iRowCounter + 1
                    iRowCounter = CInt(iRowCounter + CDbl(oDataReader.Item("UNIT_PRICE")))
                    iTotal = iTotal + 1
                    sTableRow = "<tr>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("SALE_ID")) & "</td>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("PRODUCT_ID")) & "</td>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("SALE_DATE")) & "</td>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("DESCRIPTION")) & "</td>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("CATEGORY_ID")) & "</td>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("UNIT_PRICE")) & "</td>"
                    sTableRow = sTableRow & "<td>" & CStr(oDataReader.Item("QTY")) & "</td>"
                    sTableRow = sTableRow & "</tr>" & vbCrLf
                    sPageText = sPageText & sTableRow

                    sPreviousSaleID = sCurrentSaleReader
                Loop While oDataReader.Read()
                '**** Now show the final report
                ' showBreakData
                sPageText = showBreakData(sPageText, sCurrentSaleReader, iRowCounter, 7)
            End If


            sPageText = sPageText & "<br>Number in table: " & CStr(iTotal) & "<br>" & vbCrLf

            sPageText = sPageText & "<table><tr><td>Next Sales Person ID >>>> </td><td></td><td><<<< Previous Sales Person ID</td></tr></table>"

            sPageText = sPageText & g_sHTMLFooter() & vbCrLf

        Catch

        End Try
        Return sPageText

    End Function

    Public Function g_sHTMLHeader(ByVal sTitleNameA As String) As String
        Dim sHeading As String

        sHeading = "<html><head><title>" & sTitleNameA & "</title></head><body>"
        sHeading = sHeading & vbCrLf & "<font color=""red""><h2>" & sTitleNameA & "</h2></font><hr>"

        Return sHeading
    End Function

    Public Function g_sHTMLFooter() As String
        Dim sTemp As String = "</body>" & vbCrLf & "</html>"
        Return sTemp
    End Function

    Public Function showBreakData(ByVal sPageA As String, ByVal sSubjCodeA As String, _
    ByVal iRecordCountA As Integer, _
    ByVal iNumberOfCols As Integer) As String
        Dim sPage As String = sPageA & "<tr><td valign=""center"" colspan=""" & iNumberOfCols & _
        """><b><font size=""3"" color=""blue"">" & _
        "</font> Sub total: " & FormatCurrency(iRecordCountA) & _
        "</b></td></tr></table><p>" & vbCrLf
        Return sPage
    End Function

    Public Function StartNewHTMLTable(ByVal sPageA As String, ByVal sCurrentSaleReader As String) As String
        Dim sPage As String = "<table border=""1""><tr><th>Sales Person ID: " & sCurrentSaleReader & "</tr></tr><tr><th>Sale ID</th><th>Product ID</th><th>Sale Date</th><th>Description</th><th>Category</th><th>Unit Price</th><th>Quantity</th>" & _
        "<th> </th>"
        Return sPage
    End Function
End Module
Code used to call the module:

Code:
 Private Sub btnBreakReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBreakReport.Click
        Dim sCommand As String
        Dim sParam As String
        Dim sPage As String
        Dim oSW_HTMLOutputFile As StreamWriter

        Try
            sPage = g_HTMLReport(sCurrentSaleReader)
            oSW_HTMLOutputFile = New StreamWriter(Application.StartupPath & _
                                                "\SalesBreakReport.html")
            If Not (oSW_HTMLOutputFile Is Nothing) Then  ' Is the File Open?
                oSW_HTMLOutputFile.Write(sPage)
                oSW_HTMLOutputFile.Close()
            End If

            ' automatically load internet explorer to show the file created.
            sCommand = "IExplore.exe"
            sParam = """" & Application.StartupPath & "\SalesBreakReport.html"""
            System.Diagnostics.Process.Start(sCommand, sParam)
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf)
        End Try
    End Sub
I think i understand what needs to be done

-Loop through ID's
-Generate Individual Reports
-Load 1st report, with links to next and previous reports.

Just not sure how and where to put the links/ loop code.

Thanks in advanced.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Looping through Break Reports
Looping through Break Reports
Looping through Break Reports Looping through Break Reports
Looping through Break Reports
Looping through Break Reports
Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports Looping through Break Reports
Looping through Break Reports
Looping through Break Reports
 
Looping through Break Reports
Looping through Break Reports
 
-->