Using MAX() in a SQL Subquery

database diagram showing table relationships

One pro bono project I’m working on is improving a school website where parents can sign up for classes, view students’ grades, etc. One of the problems with the website was that the list of user accounts, which includes all parents and teachers, includes parents from previous years who no longer have students there.

The query for pulling this information was very simple:

SELECT * FROM UserAccounts ORDER BY lastName ASC

This query pulled all users and ordered them only by last name. The data was being dumped into an ASP.NET GridView with column sorting, and there were only a couple hundred people there, so it wasn’t completely unmanageable. However, dealing with all the parents who no longer have kids there did make visual searches more difficult. The request was to bring all current parents to the top of the list so that other admins don’t have to search through pages of people who no longer attend.

A quick fix for this would be to sort the list based on last academic year attended. We’ll do this by employing the MAX() function.

In addition to the UserAccounts table, there are other tables called “students” (which includes all current and past students) and “schedule” (which holds student schedules). In the schedule table, a column called “s_year” that holds the academic year (in the format of “2017-18”). We can join these tables based on user account IDs in the UserAccount table, the parentID column in the students table, and the student id (s_id) column in the schedule table.

One more thing: Since this list also includes people who have never had students there, the revised query will have to take that into account.

database diagram showing table relationships

The below query will not only solve this problem, but will also sort by first and last name. Users with no academic year (a NULL value) will be at the bottom of the list.

SELECT DISTINCT ua.*, sc.s_year FROM UserAccounts ua 
LEFT OUTER JOIN students s ON s.parentID = ua.ID 
LEFT OUTER JOIN schedule sc ON sc.s_id = s.ID 
WHERE sc.s_year IS NULL 
OR sc.s_year = (
   SELECT MAX(sc2.s_year) FROM schedule sc2 WHERE sc2.s_id = s.ID
) 
ORDER BY sc.s_year DESC, ua.lastName, ua.firstName

The only remaining thing to do is to add the year column to the GridView and make sure that sorting is enabled.

EF Database First with ASP.NET MVC 5 and Oracle Database 12c

NuGet Package Manager

There is a great article called “EF Database First with ASP.NET MVC” by Tom FitzMacken that is part of Microsoft’s ASP.NET MVC tutorial.

I wanted to create a website using the steps outlined here, except instead of using a SQL Server database (as was presented in the article), I wanted to use an Oracle 12c database. Unfortunately, Oracle was not an option in the list of Data Sources as in the image below:

no Oracle option available
Oracle DB was not an option.

I had the basic Oracle setup on my development server, however, I found that I did not have Oracle Developer Tools for Visual Studio. I installed the 32-bit version and rebooted the server; I have not tested the 64-bit version for this particular project. Also, I changed the .NET Framework from 4.5 to 4.6. (From what I have read, 4.52 is the minimum that will work with the EF 6 / Oracle setup.) Lastly, I installed several NuGet packages:

  1. Official Oracle ODP.NET, Managed Driver
  2. Official Oracle ODP.NET, Managed Entity Framework Driver
  3. Oracle Data Provider for .NET (ODP.NET) Managed Driver
  4. Oracle Rdb Entity Framework Provider
NuGet Package Manager
NuGet Package Manager

I closed and reopened Visual Studio 2013, and reopened the solution I had created. Now, when adding a Model as in step 2 of the article, I had Oracle options in the Data Source list!

an Oracle option is available
Oracle is now an option!

After selecting the Oracle option, you can complete setting up the connection as below. TNS is available as an option; if you use this, the connection string to Oracle must bu set up in your TNSnames file.

Connection Properties dialog box
Select your Oracle schema.

After this is done, you can continue with the linked tutorial above!

The New City of Houston City-Wide Fee Schedule has been Published!

Back in 2013, I was on the winning team for the City of Houston’s Open Innovation Hackathon, and we produced a replacement for the previous fee schedule. The new fee schedule is now on the public City of Houston website!

Update: The new fee schedule, which was at http://www.houstontx.gov/finance/feeschedule/, is not available as of 2015-09-10.

Fixing the CrystalReportViewer “Next Page” Navigation Problem

Note: This is an update to my last post.

I discovered that the version of the SAP CrystalReportViewer for VS2010 (13.0.2000.0) that I am using appears to have a bug that prevents the user from proceeding past page 2 when using the Next Page button. Pages can still be directly accessed by typing in the page number, but clicking the Next Page button keeps you on page 2.

I read quite a few possible solutions to this, such as moving the code that loads the Crystal Report from Page_Load to Page_Init, but this did not work for me. Also, there is an update to 13.0.2000.0 that might fix this issue, but I am not in a position to update the version of the SAP Crystal Reports software at present.

Fortunately, I was able to code around this bug. First, I disabled the navigation buttons on the viewer itself. Next, I put my own navigation buttons above the viewer component on the ASP.NET Web Form. Lastly, I wrote the code that handles the click events for these buttons.

Here is the code for the Reports.aspx page:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Reports.aspx.vb" Inherits="Pages_Reports" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<!DOCTYPE html>

<html>
    <head runat="server">
        <title></title>
        <meta http-equiv="X-UA-Compatible" content="IE=9,chrome=1" />
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server" AsyncPostBackTimeout="0" EnableHistory="True">
            </asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:Label ID="Label1" runat="server" Text="Page navigation:   "></asp:Label>
                    <asp:Button runat="server" ID="btnFirst" 
                        Text="|<--" ToolTip="Go to first page"/>
                    <asp:Button runat="server" ID="btnPrev" 
                        Text="<<" ToolTip="Go to previous page"/>
                    <asp:Button runat="server" ID="btnNext" 
                        Text=">>" ToolTip="Go to next page"/>
                    <asp:Button runat="server" ID="btnLast" 
                        Text="-->|" ToolTip="Go to last page"/>
                    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"                     
                    HasCrystalLogo="False" GroupTreeStyle-ShowLines="True" 
                        Width="100%" Height="100%" 
                    EnableDatabaseLogonPrompt="False" ReuseParameterValuesOnRefresh="True" 
                        HasPageNavigationButtons="False" ShowAllPageIds="True" 
                        EnableParameterPrompt="False" />                    
                </ContentTemplate>
            </asp:UpdatePanel>
        </div>
        </form>
    </body>
</html>

…and the code file Reports.aspx.vb…

Imports CrystalDecisions.Shared
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Web

Partial Class Pages_Reports
    Inherits System.Web.UI.Page

    Protected strQueryString As String
    Protected intLastPage As Integer
    Protected intCurPage As Integer

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        strQueryString = Request.QueryString.ToString()
        Page.Title = Replace(Request.QueryString("rpt").ToString(), ".rpt", "")
        If Not Page.IsPostBack Then
            'Do nothing
        ElseIf Session(strQueryString) IsNot Nothing Then
            CrystalReportViewer1.ReportSource = Session(strQueryString)
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim strReportName As String = Request.QueryString("rpt")
            Dim strSingleDate As String = Request.QueryString("single")
            Dim strBeginDate As String = Request.QueryString("begin")
            Dim strEndDate As String = Request.QueryString("end")

            Dim bSingleDate As Boolean = False
            Dim bMultiDate As Boolean = False

            Dim arrSingleDate() As String = _
               Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
            Dim arrMultiDate() As String = _
               Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

            If Array.IndexOf(arrSingleDate, strReportName) <> -1 Then
                bSingleDate = True
                bMultiDate = False
            ElseIf Array.IndexOf(arrMultiDate, strReportName) <> -1 Then
                bSingleDate = False
                bMultiDate = True
            End If

            Dim connInfo As New ConnectionInfo
            Dim rptDoc As New ReportDocument

            'setup the connection
            connInfo = Functions.GetConnectionInfo()

            'load the Crystal Report
            rptDoc.Load(Server.MapPath( _
               Utilities.AppSettingsFunction.getValue("ReportFolder") _
               & strReportName))

            'apply logon information
            For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
                Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
                repTblLogonInfo.ConnectionInfo = connInfo
                tbl.ApplyLogOnInfo(repTblLogonInfo)
            Next

            'add required parameters
            If bSingleDate Then
                rptDoc.SetParameterValue("REPORT_DATE", strSingleDate & " 23:59:59")
            End If

            If bMultiDate Then
                rptDoc.SetParameterValue("BEGIN_DATE", strBeginDate & " 00:00:00")
                rptDoc.SetParameterValue("END_DATE", strEndDate & " 23:59:59")
            End If

            'Set, bind, and display Crystal Reports Viewer data source
            Session(strQueryString) = rptDoc
            CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.ShowLastPage()
            CrystalReportViewer1.ShowFirstPage()
            Session(strQueryString + "_pagenum") = 1
            Session(strQueryString + "_lastpagenum") = GetLastCRPageNumber()
        ElseIf Session(strQueryString) IsNot Nothing Then
            CrystalReportViewer1.ReportSource = Session(strQueryString)
        End If
        GetPageNums()
    End Sub


    Private Sub GetPageNums()
        intCurPage = Session(strQueryString + "_pagenum")
        intLastPage = Session(strQueryString + "_lastpagenum")
        ButtonsCheck()
    End Sub

    Private Sub SetCurPageNum(intPage As Integer)
        GetPageNums()
        Session(strQueryString + "_pagenum") = intPage
        ButtonsCheck()
    End Sub

    Protected Sub btnNext_Click(sender As Object, e As System.EventArgs) Handles btnNext.Click
        GetPageNums()
        If intCurPage < intLastPage Then
            SetCurPageNum(intCurPage + 1)
            CrystalReportViewer1.ShowNthPage(intCurPage + 1)
        Else
            CrystalReportViewer1.ShowNthPage(intCurPage)
        End If
    End Sub

    Protected Sub btnPrev_Click(sender As Object, e As System.EventArgs) Handles btnPrev.Click
        GetPageNums()
        If intCurPage > 1 Then
            SetCurPageNum(intCurPage - 1)
            CrystalReportViewer1.ShowNthPage(intCurPage - 1)
        Else
            CrystalReportViewer1.ShowNthPage(intCurPage)
        End If
    End Sub

    Private Function GetCRPageNumber() As Integer
        Dim vi As ViewInfo = CrystalReportViewer1.ViewInfo
        Return vi.PageNumber
    End Function

    Private Function GetLastCRPageNumber() As Integer
        Dim vi As ViewInfo = CrystalReportViewer1.ViewInfo
        Return vi.LastPageNumber
    End Function

    Protected Sub ButtonsCheck()
        If intCurPage = 1 Then
            btnFirst.Enabled = False
            btnPrev.Enabled = False
        Else
            btnFirst.Enabled = True
            btnPrev.Enabled = True
        End If

        If intCurPage = intLastPage Then
            btnLast.Enabled = False
            btnNext.Enabled = False
        Else
            btnLast.Enabled = True
            btnNext.Enabled = True
        End If
    End Sub

    Protected Sub CrystalReportViewer1_Navigate(source As Object, e As CrystalDecisions.Web.NavigateEventArgs) Handles CrystalReportViewer1.Navigate
        SetCurPageNum(e.NewPageNumber)
    End Sub

    Protected Sub btnFirst_Click(sender As Object, e As System.EventArgs) Handles btnFirst.Click
        CrystalReportViewer1.ShowFirstPage()
        GetPageNums()
    End Sub

    Protected Sub btnLast_Click(sender As Object, e As System.EventArgs) Handles btnLast.Click
        CrystalReportViewer1.ShowLastPage()
        GetPageNums()
    End Sub
End Class