Published / updated29 Aug 2026

Database Connectivity

Before you start

You need: collections and exceptions (Article 05).

Helpful but not required: basic SQL. If SELECT ... WHERE is new to you, read Track 06 Articles 01–04 first — or read the SQL here as given and come back.

Time: about 50 minutes, plus the practice.

Learning objective

Trace a VB.NET data-access routine from connection string to result, and diagnose why a database call fails.

Topics

• Connection strings and where legacy applications keep them
• SqlConnection, SqlCommand, and Using
• Parameters, and why AddWithValue is a trap
• SqlDataReader for forward-only reads
• SqlDataAdapter and DataSet as legacy patterns
• Stored procedures
• Transactions
• Diagnosing a failed SQL command

The shape of a data-access call

Every ADO.NET call follows the same five steps: open a connection, build a command, add parameters, execute, release. Legacy code obscures this by spreading the steps across a page, but the shape is always there.

Public Function GetStudentByRollNumber(schoolId As Integer, rollNumber As String) As Student
    Const sql As String =
        "SELECT Id, PublicId, SchoolId, Name, RollNumber, ClassName, Section, Status " &
        "FROM   Student " &
        "WHERE  SchoolId = @SchoolId AND RollNumber = @RollNumber"

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

            command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId
            command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = rollNumber

            connection.Open()

            Using reader As SqlDataReader = command.ExecuteReader()
                If Not reader.Read() Then
                    Return Nothing
                End If

                Return MapStudent(reader)
            End Using

        End Using
    End Using
End Function

Using on all three objects is the point. A reader left open holds the connection, and a connection never returned to the pool is a connection nobody else can use.

Connection strings

Server=.\SQLEXPRESS;Database=NexCodingSchool;Integrated Security=True;TrustServerCertificate=True;
Server=nca-sql;Database=NexCodingSchool;User Id=app_user;Password=...;TrustServerCertificate=True;

Integrated Security=True uses the Windows account the process runs under. That is why an application can work on a developer machine and fail on a server: the service account is different and has no database permission.

In a .NET Framework application the string usually lives in Web.config or App.config:

<connectionStrings>
  <add name="SchoolDb"
       connectionString="Server=.;Database=NexCodingSchool;Integrated Security=True;"
       providerName="System.Data.SqlClient" />
</connectionStrings>

Private ReadOnly _connectionString As String =
    ConfigurationManager.ConnectionStrings("SchoolDb").ConnectionString

Two things to check in any legacy application:

• Hard-coded strings. Dim cs As String = "Server=..." inside a procedure means the credential is in source control and in every compiled copy. Note it; do not silently move it, because several places may hard-code slightly different strings.
• Which config wins. A Web.config transform or a machine-level config can override what you see in the file you opened. Print the resolved string at run time before concluding anything.

Parameters

Never build SQL by concatenation.

' Never do this — SQL injection, and it breaks on any apostrophe
Dim sql As String = "SELECT * FROM Student WHERE Name = '" & searchName & "'"

A student named O'Brien breaks that query. A hostile value does considerably worse. Parameters fix both problems at once, because the value never becomes part of the SQL text.

AddWithValue versus Add

' Convenient, and the source of hard-to-find performance and correctness bugs
command.Parameters.AddWithValue("@RollNumber", rollNumber)

' Explicit — states the type and size
command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = rollNumber

AddWithValue infers the type from the .NET value. A String becomes NVarChar sized to the value's length, so "NCA-2024-0012" is inferred as NVarChar(13). A different call with a longer value produces NVarChar(20), and SQL Server caches a separate execution plan for each. Worse, when the column is VARCHAR and the parameter is NVARCHAR, SQL Server converts the column rather than the parameter and stops using the index — a query that was instant becomes a table scan.

AddWithValue is fine in a small internal tool. In a query that runs often, use Add with an explicit type and size.

Nothing versus DBNull

' Wrong — a Nothing parameter is treated as "not supplied"
command.Parameters.AddWithValue("@Address", student.Address)

' Correct
Dim addressParameter As SqlParameter =
    command.Parameters.Add("@Address", SqlDbType.NVarChar, 250)

If String.IsNullOrEmpty(student.Address) Then
    addressParameter.Value = DBNull.Value
Else
    addressParameter.Value = student.Address
End If

Database NULL is DBNull.Value, not Nothing. Passing Nothing makes ADO.NET behave as if the parameter were never added, and the command fails with "must declare the scalar variable" — a confusing message for what is really a null-handling bug.

The same applies when reading:

If reader.IsDBNull(columnIndex) Then
    student.Address = Nothing
Else
    student.Address = reader.GetString(columnIndex)
End If

reader.GetString on a NULL column throws SqlNullValueException. Legacy code that never checks works only while the column happens to be populated.

Reading results

Private Function MapStudent(reader As SqlDataReader) As Student
    Dim student As New Student()

    student.Id = reader.GetInt32(reader.GetOrdinal("Id"))
    student.PublicId = reader.GetGuid(reader.GetOrdinal("PublicId"))
    student.SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId"))
    student.Name = reader.GetString(reader.GetOrdinal("Name"))
    student.RollNumber = reader.GetString(reader.GetOrdinal("RollNumber"))
    student.Status = CType(reader.GetByte(reader.GetOrdinal("Status")), StudentStatus)

    Return student
End Function

GetOrdinal("Name") is safer than a hard-coded reader.GetString(3), which breaks the moment someone reorders the SELECT list. When mapping many rows, call GetOrdinal once before the loop rather than per row.

ExecuteScalar returns the first column of the first row, and returns Nothing when there are no rows:

Dim result As Object = command.ExecuteScalar()
Dim outstanding As Decimal = 0D

If result IsNot Nothing AndAlso result IsNot DBNull.Value Then
    outstanding = CDec(result)
End If

ExecuteNonQuery returns the number of affected rows — useful for confirming an UPDATE actually matched something:

Dim affected As Integer = command.ExecuteNonQuery()

If affected = 0 Then
    Throw New InvalidOperationException("No student was updated. The record may have been removed.")
End If

DataSet and DataAdapter

The disconnected model was the default in early .NET and dominates Web Forms code, usually because a DataSet binds directly to a GridView.

Dim table As New DataTable()

Using connection As New SqlConnection(_connectionString)
    Using adapter As New SqlDataAdapter(sql, connection)
        adapter.SelectCommand.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId
        adapter.Fill(table)
    End Using
End Using

For Each row As DataRow In table.Rows
    Dim name As String = row("Name").ToString()
Next

Fill opens and closes the connection itself, so an explicit Open() is unnecessary.

The cost is that everything is untyped Object. row("Nmae") compiles and throws at run time. CInt(row("Marks")) fails on DBNull. Check first:

Dim marks As Integer = 0

If Not IsDBNull(row("MarksObtained")) Then
    marks = CInt(row("MarksObtained"))
End If

Do not convert working DataSet code to objects as an incidental change — a GridView bound to a DataTable depends on the column names. Treat that as its own planned piece of work.

Stored procedures

Using command As New SqlCommand("usp_GetStudentFeeSummary", connection)
    command.CommandType = CommandType.StoredProcedure
    command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId
    command.Parameters.Add("@StudentId", SqlDbType.Int).Value = studentId

    Dim outstanding As SqlParameter =
        command.Parameters.Add("@Outstanding", SqlDbType.Decimal)
    outstanding.Direction = ParameterDirection.Output
    outstanding.Precision = 18
    outstanding.Scale = 2

    connection.Open()
    command.ExecuteNonQuery()

    Dim due As Decimal = CDec(outstanding.Value)
End Using

Forgetting CommandType = CommandType.StoredProcedure is a frequent error: ADO.NET sends the procedure name as a literal SQL statement, and SQL Server reports incorrect syntax near the name.

Output parameter values are only populated after the reader is closed. Reading outstanding.Value while a SqlDataReader is still open returns Nothing.

Transactions

Using connection As New SqlConnection(_connectionString)
    connection.Open()

    Using transaction As SqlTransaction = connection.BeginTransaction()
        Try
            Using command As New SqlCommand(insertPaymentSql, connection, transaction)
                command.Parameters.Add("@FeeAccountId", SqlDbType.Int).Value = feeAccountId
                command.Parameters.Add("@Amount", SqlDbType.Decimal).Value = amount
                command.ExecuteNonQuery()
            End Using

            Using command As New SqlCommand(updateBalanceSql, connection, transaction)
                command.Parameters.Add("@FeeAccountId", SqlDbType.Int).Value = feeAccountId
                command.Parameters.Add("@Amount", SqlDbType.Decimal).Value = amount
                command.ExecuteNonQuery()
            End Using

            transaction.Commit()

        Catch
            transaction.Rollback()
            Throw
        End Try
    End Using
End Using

Every command inside a transaction must be given that transaction. Omitting it produces "SqlCommand.Transaction property has not been initialized" at run time.

Recording a payment without updating the balance leaves the fee account wrong, so the two statements belong in one transaction. That is exactly the class of defect a maintenance change can introduce.

Diagnosing a failed database call

Work in this order.

1. Read the actual message. SqlException.Message and SqlException.Number name the problem.

Number	Meaning	Usual cause
2 / 53	Server not found	Wrong server name, SQL Browser off, firewall
18456	Login failed	Wrong credentials, or service account has no login
4060	Cannot open database	Database name wrong, or no permission to it
208	Invalid object name	Table missing, or wrong schema or database
207	Invalid column name	Renamed column, or a typo in the SQL
2627 / 2601	Unique constraint violated	Duplicate roll number
547	Constraint conflict	Foreign key — the related row does not exist
-2	Timeout	Slow query, blocking, or missing index

2. Confirm the connection string the process actually used. Log connection.ConnectionString with the password masked, or read it in the debugger. Assume nothing from the config file.

3. Run the same SQL in SSMS, as the same login the application uses. If it works there and fails in the application, the difference is the login, the database context, or the parameters.

4. Inspect the parameters at the breakpoint. Check for Nothing where DBNull.Value was needed, and for values silently truncated by a declared size.

5. Check whether the connection is even open. "Invalid operation. The connection is closed" means Open() was missed, or an earlier exception closed it.

Errors you will hit

Message	Cause	Fix
SqlException 18456: Login failed for user	Wrong credentials, or the login does not exist	Check the connection string
SqlException 2 or 53: server not found	Wrong instance name, service stopped, firewall	.\SQLEXPRESS for a named instance
SqlException 4060: Cannot open database	Wrong database name, or no permission	Check the Initial Catalog
SqlException -2: Timeout expired	Slow query, blocking, or a leaked connection	See the leak note below
System.IndexOutOfRangeException: <column> from a reader	Column name does not match the query	Check the SELECT list
System.InvalidCastException reading a column	DBNull read into a non-nullable type	Test IsDBNull first
Timeout expired ... obtaining a connection from the pool	Connections never closed	Wrap every one in Using

The last row is not a slow query. It is a leak — every connection is checked out and never returned, and the message names the wrong culprit.

Common mistakes

• Concatenating values into SQL instead of using parameters
• AddWithValue on a hot query, causing plan bloat or an index-defeating type mismatch
• Passing Nothing where DBNull.Value is required
• Calling GetString on a nullable column without IsDBNull
• Reading columns by hard-coded index
• No Using, so connections and readers leak until the pool is exhausted
• Forgetting CommandType.StoredProcedure
• Reading an output parameter before the reader is closed
• Omitting the transaction argument on a command inside a transaction
• Assuming an UPDATE succeeded without checking the affected row count

Practice

Take a data-access procedure from an existing VB.NET application. Confirm every command is parameterised, every disposable is inside a Using, and every nullable column is read through IsDBNull. Fix whichever of those is missing.

Then run the debugging drill: deliberately break the connection string — wrong server, then wrong database, then a valid server with no permission — and record the SqlException.Number for each. Repeat with a valid connection but a misspelled column name. You should end with the four error numbers memorised and a habit of reading the number before the message.

You can now

• Trace a VB.NET data-access routine from screen to database
• Read a connection string and say what each part does
• Use parameters instead of string concatenation, and say why
• Handle DBNull before it becomes an InvalidCastException
• Name the likely cause from a SQL error number
• Guarantee connections are released

Review questions

1. Why can AddWithValue cause a query to stop using an index?
2. What is the difference between Nothing and DBNull.Value in a parameter?
3. What does SqlException.Number 208 indicate, and 2627?
4. Why must every command inside a transaction be given the transaction object?

Next: Reading legacy code