Published / updated29 Aug 2026

Legacy Staff Lookup Maintenance

Before you start

You need: all of Articles 01–07. This project is where reading, tracing and changing legacy code come together.

In Visual Studio: open the supplied solution, build it before touching anything, and confirm it runs. If it will not load, Article 01 covers the three usual causes.

Time: 4–6 hours. The write-up matters as much as the fix.

Goal

Demonstrate that you can take an unfamiliar VB.NET module, understand it, fix a defect, and prove the fix — without redesigning anything or introducing a regression.

Assignment

You are given a small VB.NET staff lookup screen for NexCoding Academy. Staff search by employee code or name and see designation, department, and joining date. Two defects are reported. Produce:

1. A written module document: entry point, event wiring, call chain, state read, and database objects touched.
2. A reproduction for each defect — exact steps, input, expected result, actual result.
3. A diagnosis for each, naming the line responsible and why it fails.
4. One controlled change per defect, kept as small as the fix allows.
5. A regression list — what else touches the code you changed, and how you checked it.
6. Evidence: before and after, plus the test steps someone else can repeat.

The reported defects

Defect A. Searching for a staff member whose name contains an apostrophe, such as D'Souza, shows "Incorrect syntax near 's'". Searching for other names works.

Defect B. Staff with no Department recorded do not appear in results at all. They exist in the database and appear in the admin export.

Worked example: the survey

Before touching anything, produce the module document.

Entry point and wiring

StaffLookup.aspx              markup: txtSearch, btnSearch, grdStaff
StaffLookup.aspx.vb           code-behind
StaffLookup.aspx.designer.vb  generated — do not edit

Protected WithEvents btnSearch As Button

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        LoadDepartments()
    End If
End Sub

Protected Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    LoadStaff(txtSearch.Text)
End Sub

Call chain

btnSearch_Click
  └─ LoadStaff(searchTerm)
       └─ StaffData.Search(schoolId, searchTerm)   ' Module, unqualified call
            └─ SqlCommand → Staff table

StaffData is a Module, so Search is called with no receiver. Go to definition finds it; a text search for StaffData. does not.

State read that is not a parameter

Session("SchoolId") supplies the tenant. It is read inside LoadStaff, not passed in — which matters, because it means the procedure cannot be tested without a session.

The code as found

Public Module StaffData

    Public Function Search(schoolId As Integer, searchTerm As String) As DataTable
        Dim table As New DataTable()

        Dim sql As String =
            "SELECT s.Id, s.EmployeeCode, s.Name, s.Designation, s.Department, s.JoiningDate " &
            "FROM   Staff s " &
            "WHERE  s.SchoolId = " & schoolId.ToString() & " " &
            "  AND  s.IsActive = 1 " &
            "  AND  s.Department <> '' " &
            "  AND  (s.Name LIKE '%" & searchTerm & "%' " &
            "        OR s.EmployeeCode LIKE '%" & searchTerm & "%')"

        Using connection As New SqlConnection(ConnectionString())
            Using adapter As New SqlDataAdapter(sql, connection)
                adapter.Fill(table)
            End Using
        End Using

        Return table
    End Function

End Module

Worked example: diagnosis

Defect A — apostrophe breaks the query

The WHERE clause is built by string concatenation. D'Souza closes the string literal early, and SQL Server parses the remainder as syntax. The reported message names the fragment after the apostrophe.

This is not only a display bug. The same line accepts any input as SQL, so it is an injection vulnerability on an internal screen — worth stating explicitly in your write-up, because it changes the priority of the fix.

Responsible line: the s.Name LIKE '%" & searchTerm & "%' concatenation.

Defect B — staff with no department are filtered out

"  AND  s.Department <> '' "

Department is nullable. In SQL, NULL <> '' evaluates to UNKNOWN, not TRUE, so those rows fail the condition and are excluded. The admin export uses a different query without this clause, which is why the data appears there.

The clause looks like it was intended to hide blank departments. Check the version history before removing it — if a past ticket asked for that, the fix is to handle NULL correctly, not to drop the filter.

Responsible line: the s.Department <> '' comparison against a nullable column.

Worked example: the controlled change

Public Module StaffData

    Public Function Search(schoolId As Integer, searchTerm As String) As DataTable
        Dim table As New DataTable()

        Const sql As String =
            "SELECT s.Id, s.EmployeeCode, s.Name, s.Designation, s.Department, s.JoiningDate " &
            "FROM   Staff s " &
            "WHERE  s.SchoolId = @SchoolId " &
            "  AND  s.IsActive = 1 " &
            "  AND  ISNULL(s.Department, '') <> '' " &
            "  AND  (s.Name LIKE @Search " &
            "        OR s.EmployeeCode LIKE @Search)"

        Using connection As New SqlConnection(ConnectionString())
            Using command As New SqlCommand(sql, connection)

                command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId
                command.Parameters.Add("@Search", SqlDbType.NVarChar, 102).Value =
                    "%" & searchTerm & "%"

                Using adapter As New SqlDataAdapter(command)
                    adapter.Fill(table)
                End Using

            End Using
        End Using

        Return table
    End Function

End Module

What changed, and what deliberately did not:

Change	Reason
Parameters replace concatenation	Fixes defect A and closes the injection hole
ISNULL(s.Department, '') <> ''	Fixes defect B while preserving the original intent
Explicit parameter type and size	The size accounts for the two wildcard characters
SqlDataAdapter(command)	Needed so the adapter uses the parameterised command
DataTable return kept	The GridView binds to it by column name — changing this is a redesign, not a fix
Module left as a Module	Converting it changes every call site across the application
Session("SchoolId") left in LoadStaff	Real improvement, but out of scope for this ticket

The last three rows are the point of the exercise. Each is a genuine improvement, and each belongs on a list you hand to your lead — not in this commit. A maintenance change that also refactors cannot be reviewed, and if the screen breaks nobody can tell which part did it.

If the ISNULL filter turns out to have no ticket behind it, raise that separately rather than deleting the clause. Removing a filter changes what users see, which is a behaviour change, not a defect fix.

Worked example: regression check

Everything calling StaffData.Search is affected. Find them with Find all references, not a text search.

What to check	Why
Staff lookup screen	The direct change
Any other caller of Search	Same Module function, possibly different expectations
Search with a % or _ in the term	These are LIKE wildcards and are still not escaped — record as a known limitation
Search returning zero rows	Confirm an empty grid, not an exception
Search with an empty term	Confirm the behaviour is unchanged from before
A staff member with a department	Must still appear — confirm the ISNULL change did not invert the filter
Long search term	Confirm no truncation at the declared parameter size

The wildcard row matters: parameters stop injection but do not change LIKE semantics. Searching for 50% still behaves as a wildcard. Say so in your write-up rather than leaving it undiscovered.

Submission template

Module and screen:
Entry point and event wiring:
Call chain (control → handler → method → SQL):
State read that is not a parameter:
Database objects touched:

Defect A
  Reproduction steps and input:
  Expected vs actual:
  Responsible line and explanation:
  Change made:

Defect B
  Reproduction steps and input:
  Expected vs actual:
  Responsible line and explanation:
  Change made:

Deliberately not changed (and why):
Known limitations remaining:
Regression list and how each was checked:
Evidence (before / after, repeatable test steps):

AI practice

Two AI exercises from this track's syllabus. Do both after the maintenance change is verified, and apply Track 18's discipline — every answer is a hypothesis until you have run it.

1. Ask AI to compare equivalent C# and VB.NET code. Paste your changed Sub and ask for the C# equivalent, then ask which constructs have no direct counterpart. Check the answer against the comparison table in Reading legacy code — Handles, WithEvents and On Error Resume Next are the ones most often described wrongly.
2. Use AI to explain unfamiliar legacy code, then verify it. Paste a module you did not write and ask for a step-by-step explanation plus anything that looks like a bug. Verify every claim against the code in front of you. Expect roughly half the suggested bugs to be real — separating those two halves is the exercise.

Explaining existing code is the lowest-risk use of AI, because the code is in front of you and a wrong explanation is catchable. Asking it to rewrite a legacy module you do not yet understand is the highest-risk use.

Track 18 — Reviewing AI-generated code — has the full checklist.

Self-assessment

Your submission is complete when someone who has never opened this application can, from your document alone, find the module, reproduce both defects, understand why each occurred, repeat your verification, and see exactly which improvements you consciously left out.

Two specific tests of quality:

• Does your write-up name what you chose not to change? A submission with no such list usually means the change was larger than it should have been.
• Can a reviewer tell from the diff alone which line fixes which defect? If not, the change is doing too much at once.

Track completion criteria

You can read basic-to-intermediate VB.NET, follow application flow through event handlers to the database, diagnose a defect from its symptom, and make a small change safely with evidence that it worked and did not break anything else.

Specifically, you can:

• State what Option Strict Off permits and why it matters
• Trace an event handler that was wired with AddHandler
• Tell Overrides from Shadows and predict which runs
• Convert On Error Resume Next to structured handling without changing behaviour
• Identify a Nothing reference and a failed SQL command from the exception alone
• Translate between VB.NET and C# without a converter
• Separate a fix from a refactor, and say why

The syllabus recommends Track 05 — ASP.NET Web Forms / ASPX Basics or Track 10 — ASP.NET Core Development next.