Solved

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

Posted on 2007-03-30
6
260 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now