Solved

Visual Basic.NET data output issue on an Accounting Report.

Posted on 2007-03-30
6
265 Views
Last Modified: 2010-05-18
We have an Accounting report generated from VB.NET that will list our Job Numbers starting with nines (93153 for example) before the other Job Numbers are listed sequentially no matter if the report is generated by Job Number, Name, Task Desc, Client or Billing Level.  We would like that only when sorting by Job Number are our Accounting reports being generated starting with Job Numbers starting with nines.  Below are the relevent codes that currently place Job Numbers starting with nines first on any sort.  Any assistance offered would be greatly appreciated.

SQLConn = New SqlConnection(DWMA.ConnectionString)
        Try
            DA = New SqlDataAdapter(strSQL, SQLConn)
            DS = New DataSet()
            DA.Fill(DS)
            dtReport = DS.Tables(0)
            dtReport = oCipher.SortReport(dtReport, sSortFld, sSortOrder)
            dtReport = oCipher.Sort9sOnTop(dtReport)
        Catch SQLExc As SqlException
            Response.Redirect("../Error.aspx?page=Accounting\ProjectsReport.aspx&problem=SQL Error")
        End Try
    End Sub

 Case Else
                lblReportType.Text &= "Project Number"
                strSQL &= " ORDER BY ProjNum " & sSortOrder & ", TaskNum " & sSortOrder
                sSortFld = "ProjNum"
        End Select
0
Comment
Question by:regsamp
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:paix120
ID: 18826301
The code you are showing seems incomplete, and i can't tell what the problem is.

Also, your question is a little bit confusing. Are you saying that you only want the report to show the account numbers that start with 9 first when you sort by job number, and not when you sort by other fields?

You may want to edit your question title to be more descriptive and get more help.
0
 

Author Comment

by:regsamp
ID: 18826319
"Are you saying that you only want the report to show the account numbers that start with 9 first when you sort by job number, and not when you sort by other fields?"  paix120 that is correct.
0
 

Author Comment

by:regsamp
ID: 18826450
FULL CODE

Imports System.Data
Imports System.Data.SqlClient

Public Class projectsreport
    Inherits System.Web.UI.Page
    Protected WithEvents lblDate As System.Web.UI.WebControls.Label
    Protected WithEvents lblTime As System.Web.UI.WebControls.Label
    Protected WithEvents lblTitle As System.Web.UI.WebControls.Label
    Protected WithEvents lblReportType As System.Web.UI.WebControls.Label
    Protected WithEvents lblBillLevel As System.Web.UI.WebControls.Label
    Protected WithEvents dgrdProjectReport As System.Web.UI.WebControls.DataGrid
    Private Const yourLength As Int32 = 70 'Constant may be placed here!
    Dim SQLConn As SqlConnection
    Dim SQLCmd As SqlCommand
    Protected DA As SqlDataAdapter
    Protected DS As DataSet
    Protected dtReport As DataTable
    Protected dRow As DataRow

    Protected oSvrFunc As New SvrFunctions
    Protected oCipher As New ProjTaskReader

    Protected sProjNum As String = ""
    Protected sProjName As String = ""
    Protected sTaskDesc As String = ""
    Protected sClient_Name As String = ""
    Protected sCriteria As String = ""
    Protected sActive As String = ""
    Protected sLastSortFld As String = ""
    Protected sSortFld As String = ""
    Protected sSortOrder As String = ""
    Protected sStatus As String = ""
    Protected sBill_Level As String = ""
    Protected sExp As String = ""

    Protected sRoot As String = ""
    Protected sAcctgPath As String = ""

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack Then
            Dim User As New SmithUser
            GetRoot()
            User.PullUserInfo(Page.User.Identity.Name)
            If User.AccessViewAccounting Or User.AccessGod Then
                lblDate.Text = "Date: " & Today.ToShortDateString
                lblTime.Text = "Time: " & Today.ToShortTimeString()
                lblBillLevel.Text = "Bill Level: All"

                sProjNum = oSvrFunc.GetRqFldVal(Me, "ProjNum")
                sProjName = oSvrFunc.GetRqFldVal(Me, "ProjName")
                sClient_Name = oSvrFunc.GetRqFldVal(Me, "Client_Name")
                sTaskDesc = oSvrFunc.GetRqFldVal(Me, "TaskDesc")
                sActive = oSvrFunc.GetRqFldVal(Me, "Active")
                sBill_Level = oSvrFunc.GetRqFldVal(Me, "Bill_Level")
                sExp = oSvrFunc.GetRqFldVal(Me, "Exp")
                sCriteria = oSvrFunc.GetRqFldVal(Me, "Criteria")
                sSortFld = oSvrFunc.GetRqFldVal(Me, "SortFld")
                sLastSortFld = oSvrFunc.GetRqFldVal(Me, "LastSortFld")
                sSortOrder = oSvrFunc.GetRqFldVal(Me, "SortOrder")
                LoadData()
            End If
        End If
    End Sub

    Private Sub GetRoot()
        sRoot = Request.ServerVariables("PATH_INFO")
        If InStr(sRoot, "Accounting/Projects/") > 0 Then
            sRoot = ""
            sAcctgPath = "../"
        Else
            sRoot = "Projects/"
            sAcctgPath = ""
        End If
    End Sub

    Private Sub LoadData()
        Dim Smith As New SmithGlobals
        Dim strSQL As String

        Dim sAND As String = " WHERE"

        If sSortFld = "" Then
            sSortFld = "ProjNum"
        End If

        If sSortFld = sLastSortFld Then
            If sSortOrder = "ASC" Then
                sSortOrder = "DESC"
            Else
                sSortOrder = "ASC"
            End If
        Else
            sSortOrder = "ASC"
        End If
        sLastSortFld = sSortFld

        lblReportType.Text = "Project Detail Report By "

        strSQL = "SELECT p.ProjectsID As ProjectsID, p.ProjNum As ProjNum, t.ProjNum As TaskNum, p.ProjName As ProjName, t.Description As Description," & _
            " p.Status As Status, t.Status As tStatus, p.Exp, p.Bill_Level As Bill_Level, t.Bill_Level As tBill_Level," & _
            " p.Prin As Prin, p.PM As PM, p.PSM As PSM, t.Prin As tPrin, t.PM As tPM, t.PSM As tPSM, Left(c.Client_Name, 45) As Client_Name, p.Exp AS Exp" & _
            " FROM ((Projects p LEFT JOIN Clients c ON p.ClientID = c.ClientID)" & _
            " LEFT JOIN Tasks t ON p.ProjectsID = t.ProjectID)"
       
        If sProjNum <> "" Then
            strSQL &= sAND & " (p.ProjNum LIKE '" & sProjNum & "%' OR t.ProjNum LIKE '" & sProjNum & "%')"
            sAND = " AND"
        End If

        If sProjName <> "" Then
            strSQL &= sAND & " p.ProjName LIKE '" & oSvrFunc.FixSQLStr(sProjName) & "%'"
            sAND = " AND"
        End If

        If sClient_Name <> "" Then
            strSQL &= sAND & " c.Client_Name LIKE '" & oSvrFunc.FixSQLStr(sClient_Name) & "%'"
            sAND = " AND"
        End If

        If sTaskDesc <> "" Then
            strSQL &= sAND & " (p.ProjName LIKE '%" & oSvrFunc.FixSQLStr(sTaskDesc) & "%'" & _
                " OR t.Description LIKE '%" & oSvrFunc.FixSQLStr(sTaskDesc) & "%')"
            sAND = " AND"
        End If

        If sBill_Level <> "" Then
            strSQL &= sAND & " ((p.Bill_Level Is Null AND t.Bill_Level = " & sBill_Level & ") OR"
            strSQL &= " (t.Bill_Level Is Null AND p.Bill_Level = " & sBill_Level & ") OR"
            strSQL &= " (p.Bill_Level = " & sBill_Level & " AND t.Bill_Level = " & sBill_Level & "))"
            sAND = " AND"
        End If

        If sExp <> "" Then
            strSQL &= sAND & " p.Exp = " & sExp
            sAND = " AND"
        End If

        Select Case sActive
            Case "A"
                strSQL &= sAND & " (t.Status = 'A')"
            Case "I"
                strSQL &= sAND & " (t.Status = 'I')"
        End Select

        Select Case sSortFld
            Case "ProjName"
                lblReportType.Text &= "Project Name"
                strSQL &= " ORDER BY p.ProjName " & sSortOrder
            Case "prin"
                lblReportType.Text &= "Principal"
                strSQL &= " ORDER BY p.Prin " & sSortOrder
            Case "pm"
                lblReportType.Text &= "PM"
                strSQL &= " ORDER BY p.PM " & sSortOrder
            Case "pls"
                lblReportType.Text &= "PLS"
                strSQL &= " ORDER BY p.PSM " & sSortOrder
            Case "Client_Name"
                lblReportType.Text &= "Client Name"
                strSQL &= " ORDER BY c.Client_Name " & sSortOrder
            Case "Bill_Level"
                lblReportType.Text &= "Billing Level"
                strSQL &= " ORDER BY p.Bill_Level " & sSortOrder
            Case "Status"
                lblReportType.Text &= "Status"
                strSQL &= " ORDER BY p.Status " & sSortOrder & ", t.Status " & sSortOrder
            Case "Exp"
                lblReportType.Text &= "Exp"
                strSQL &= " ORDER BY p.Exp " & sSortOrder
            Case Else
                lblReportType.Text &= "Project Number"
                strSQL &= " ORDER BY ProjNum " & sSortOrder & ", TaskNum " & sSortOrder
                sSortFld = "ProjNum"
        End Select
        Select Case sStatus
            Case "A"
                lblReportType.Text &= " (Actives)"
            Case "I"
                lblReportType.Text &= " (Inactives)"
            Case Else
                lblReportType.Text &= " (All)"
        End Select

        SQLConn = New SqlConnection(Smith.ConnectionString)
        Try
            DA = New SqlDataAdapter(strSQL, SQLConn)
            DS = New DataSet()
            DA.Fill(DS)
            dtReport = DS.Tables(0)
            dtReport = oCipher.SortReport(dtReport, sSortFld, sSortOrder)
            dtReport = oCipher.Sort9sOnTop(dtReport)
        Catch SQLExc As SqlException
            Response.Redirect("../Error.aspx?page=Accounting\ProjectsReport.aspx&problem=SQL Error")
        End Try
    End Sub

    Function GetProjNum(ByVal dRow As DataRow) As String
        Dim sProjNum As String, sTaskNum As String

        GetProjNum = ""
        If Not (dRow("TaskNum") Is DBNull.Value) Then
            sTaskNum = dRow("TaskNum").ToString()
            If sTaskNum = "" Then
                GetProjNum = sTaskNum
            Else
                sProjNum = dRow("ProjNum").ToString()
                GetProjNum = sProjNum
            End If
        ElseIf Not (dRow("ProjNum") Is DBNull.Value) Then
            sProjNum = dRow("ProjNum").ToString()
            GetProjNum = sProjNum
        End If
    End Function

    Function GetProjDesc(ByVal dRow As DataRow)
        Dim sProjDesc As String = ""

        If Not (dRow("ProjName") Is DBNull.Value) Then
            sProjDesc = Trim(dRow("ProjName").ToString()) & " "
        End If
        If Not (dRow("ProjTaskDesc") Is DBNull.Value) Then
            sProjDesc &= Trim(dRow("ProjTaskDesc").ToString()) & " "
        End If
        If Not (dRow("TaskDesc") Is DBNull.Value) Then
            sProjDesc &= Trim(dRow("TaskDesc").ToString())
        End If
        If Len(sProjDesc) > 75 Then
            sProjDesc = Left(sProjDesc, 75)
        End If
        GetProjDesc = sProjDesc
    End Function

    Function GetStatus(ByVal dRow As DataRow) As String
        Dim sStatus As String = ""

        If Not (dRow("TaskStatus") Is DBNull.Value) Then
            sStatus = dRow("TaskStatus").ToString()
        End If
        If sStatus = "" Then
            If Not (dRow("ProjStatus") Is DBNull.Value) Then
                sStatus = dRow("ProjStatus").ToString()
            End If
        End If

        If sStatus = "A" Then
            sStatus = "<font color='green'>A</font>"
        ElseIf sStatus = "I" Then
            sStatus = "<font color='red'>I</font>"
        End If
        GetStatus = sStatus
    End Function

    Protected Function GetFld(ByVal dRow As DataRow, ByVal sFld As String, ByVal sType As String) As String
        Dim dDate As DateTime, sVal As String

        If dRow(sFld) Is DBNull.Value Then
            GetFld = ""
            Exit Function
        End If
        If sType = "D" Then
            dDate = CDate(dRow(sFld))
            GetFld = dDate.ToString("d")
        Else
            sVal = dRow(sFld).ToString()
            If sFld = "Client_Name" Then
                If Len(sVal) > 50 Then
                    sVal = Left(sVal, 50)
                End If
            End If
            GetFld = sVal
        End If
    End Function

    Protected Function GetProjName(ByVal dRow As DataRow)
        Dim sName As String
        If Not dRow("ProjName") Is DBNull.Value Then
            sName = dRow("ProjName").ToString().Trim
        End If
        If Not dRow("TaskDesc") Is DBNull.Value Then
            sName &= dRow("TaskDesc").ToString().Trim
        End If
        If Len(sName) > 44 Then
            sName = Left(sName, 44)
        End If
        GetProjName = sName
    End Function
End Class
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Accepted Solution

by:
paix120 earned 500 total points
ID: 18826628
OK Well I don't have time to go through all of the code, but it seems like the relevant parts are:

    If sSortFld = "" Then
            sSortFld = "ProjNum"
        End If

        If sSortFld = sLastSortFld Then
            If sSortOrder = "ASC" Then
                sSortOrder = "DESC"
            Else
                sSortOrder = "ASC"
            End If
        Else
            sSortOrder = "ASC"
        End If
        sLastSortFld = sSortFld

AND -----------------------------------------------------------

 Select Case sSortFld
            Case "ProjName"
                lblReportType.Text &= "Project Name"
                strSQL &= " ORDER BY p.ProjName " & sSortOrder
            Case "prin"
                lblReportType.Text &= "Principal"
                strSQL &= " ORDER BY p.Prin " & sSortOrder
            Case "pm"
                lblReportType.Text &= "PM"
                strSQL &= " ORDER BY p.PM " & sSortOrder
            Case "pls"
                lblReportType.Text &= "PLS"
                strSQL &= " ORDER BY p.PSM " & sSortOrder
            Case "Client_Name"
                lblReportType.Text &= "Client Name"
                strSQL &= " ORDER BY c.Client_Name " & sSortOrder
            Case "Bill_Level"
                lblReportType.Text &= "Billing Level"
                strSQL &= " ORDER BY p.Bill_Level " & sSortOrder
            Case "Status"
                lblReportType.Text &= "Status"
                strSQL &= " ORDER BY p.Status " & sSortOrder & ", t.Status " & sSortOrder
            Case "Exp"
                lblReportType.Text &= "Exp"
                strSQL &= " ORDER BY p.Exp " & sSortOrder
            Case Else
                lblReportType.Text &= "Project Number"
                strSQL &= " ORDER BY ProjNum " & sSortOrder & ", TaskNum " & sSortOrder
                sSortFld = "ProjNum"
        End Select

---------------------------------------------------

From what you're saying, it sounds like Sort Order is always DESC. This isn't really a proper fix, but for a quick fix, you could just replace the variable sSortOrder with "DESC" for the report types that you want sorted with 9 first, and "ASC" in all other queries in the section above.

What you need is someone to step through the code and determine when sSortOrder is "Desc" and change the IF statement to handle the new decision you are describing.

Sorry I can't help more...
0
 

Author Comment

by:regsamp
ID: 18832242
Anyone else, please..
0
 
LVL 3

Expert Comment

by:paix120
ID: 18833121
You accepted my solution (thanks!), so I'm not sure anyone else will reply... may want to contact E-E customer serv. to see if you can undo it, then just split the point to include my reply if you want to after you've received an answer. Experts will now see you've accepted an answer and they can't get points, so asking for "anyone else" may prove futile until you can "re-open" the quetsion.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question