Published / updated29 Aug 2026

GridView, Data Binding and CRUD

Before you start

You need: server controls (Article 03), state (Article 04) and validation (Article 06).

You also need SQL — and it is taught later in the numbering. This article reads and writes the database, so it assumes SELECT, INSERT, UPDATE and DELETE with a WHERE clause, and parameters rather than string concatenation. Two ways to handle that:

Recommended: read Track 06 Articles 01–04 first, then come back. It is about two hours and everything afterwards is easier.
Or: carry on and read the SQL here as given. The Web Forms lessons stand on their own; you will simply be taking the queries on trust.

The Sahasra syllabus lists "SQL Server CRUD basics" as a prerequisite for this track. The track numbering puts SQL Server after Web Forms because the two legacy tracks sit together — the dependency is real either way.

Time: about 50 minutes, plus the practice.

Learning objective

Build and maintain a database-backed CRUD page using GridView, and diagnose binding, paging, and command failures.

Topics

DataBind and where it belongs in the lifecycle
GridView columns, Eval, and Bind
DropDownList binding and selection
Row commands and DataKeys
Paging and sorting
CRUD through stored procedures
SqlDataSource and why to avoid it
Diagnosing binding failures

Binding basics

Private Sub LoadStudents()
    Dim schoolId As Integer = CInt(Session("SchoolId"))

    grdStudents.DataSource = _repository.Search(schoolId, txtSearch.Text)
    grdStudents.DataBind()
End Sub

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

Setting DataSource does nothing on its own. DataBind() is what builds the rows. Forgetting it produces an empty grid with no error — the first thing to check when a grid is blank.

The IsPostBack guard matters as much here as anywhere: rebinding on every postback destroys edit state, resets the selected row, and re-queries the database on every click.

GridView columns

<asp:GridView ID="grdStudents" runat="server"
    AutoGenerateColumns="False"
    DataKeyNames="PublicId"
    AllowPaging="True" PageSize="20"
    OnRowCommand="grdStudents_RowCommand"
    OnPageIndexChanging="grdStudents_PageIndexChanging"
    EmptyDataText="No students match this search."
    CssClass="table">

    <Columns>
        <asp:BoundField DataField="RollNumber" HeaderText="Roll Number" />
        <asp:BoundField DataField="Name" HeaderText="Name" />

        <asp:TemplateField HeaderText="Class">
            <ItemTemplate>
                <%# Eval("ClassName") %> - <%# Eval("Section") %>
            </ItemTemplate>
        </asp:TemplateField>

        <asp:BoundField DataField="JoiningDate" HeaderText="Joined"
                        DataFormatString="{0:dd MMM yyyy}" />

        <asp:TemplateField HeaderText="">
            <ItemTemplate>
                <asp:LinkButton runat="server" Text="Edit"
                    CommandName="EditStudent"
                    CommandArgument='<%# Eval("PublicId") %>'
                    CausesValidation="false" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

AutoGenerateColumns="False" is not optional in real code. Left True, the grid renders every column the query returns — so adding a PasswordHash column to a SELECT * silently publishes it to the page.

EmptyDataText handles the no-rows case. Without it the grid renders nothing at all, and users read that as a broken page rather than an empty result.

Eval versus Bind

<%# Eval("Name") %>              <!-- read-only, one way -->
<%# Bind("Name") %>              <!-- two-way, for edit templates -->
<%# Eval("TotalFees", "{0:N2}") %>  <!-- with a format string -->

Eval is read-only and works anywhere. Bind is two-way and only works inside a data-bound control's edit or insert template with a data source that supports updating. Using Bind in a plain ItemTemplate throws at render time.

Eval uses reflection and returns Object. It also throws on a NULL if you call a method on the result — Eval("Department").ToString() fails when Department is NULL. Use the format overload, which handles NULL as an empty string.

DropDownList binding

Private Sub LoadClassDropDown()
    ddlClass.DataSource = _repository.GetClassNames(CInt(Session("SchoolId")))
    ddlClass.DataTextField = "ClassName"
    ddlClass.DataValueField = "ClassName"
    ddlClass.DataBind()

    ddlClass.Items.Insert(0, New ListItem("-- All classes --", String.Empty))
End Sub

Insert the placeholder after DataBind(). Binding clears the items collection, so an item added before it disappears.

Setting the selection:

Dim item As ListItem = ddlClass.Items.FindByValue(student.ClassName)

If item IsNot Nothing Then
    ddlClass.ClearSelection()
    item.Selected = True
End If

ddlClass.SelectedValue = student.ClassName throws ArgumentOutOfRangeException when the value is not in the list — which happens whenever a student holds a class that has since been removed. FindByValue with a null check does not throw, and is the safe form for legacy data.

ClearSelection() first is necessary because a DropDownList permits only one selected item, and setting a second without clearing throws.

Row commands and DataKeys

<asp:LinkButton runat="server" Text="Delete"
    CommandName="DeleteStudent"
    CommandArgument='<%# Container.DataItemIndex %>'
    OnClientClick="return confirm('Remove this student?');"
    CausesValidation="false" />

Protected Sub grdStudents_RowCommand(sender As Object, e As GridViewCommandEventArgs)
    If e.CommandName <> "DeleteStudent" Then
        Return
    End If

    Dim rowIndex As Integer = CInt(e.CommandArgument)
    Dim publicId As Guid = CType(grdStudents.DataKeys(rowIndex).Value, Guid)
    Dim schoolId As Integer = CInt(Session("SchoolId"))

    _repository.Deactivate(schoolId, publicId)

    LoadStudents()
End Sub

DataKeyNames="PublicId" stores the key per row in ViewState, so it is available on postback without putting it in the markup. This is the correct way to carry a record identifier through a grid.

Two security points. CommandArgument arrives from the browser and can be edited, so it must never be the record id itself when that id grants access — use the row index and look the key up from DataKeys, as above. And _repository.Deactivate takes schoolId from session, so a tampered row index cannot reach another school's record.

OnClientClick="return confirm(...)" gives a confirmation dialog. It is a convenience only — it is trivially bypassed, so the server-side ownership check is what actually protects the record.

Paging and sorting

Protected Sub grdStudents_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) _
    Handles grdStudents.PageIndexChanging

    grdStudents.PageIndex = e.NewPageIndex
    LoadStudents()
End Sub

Rebinding inside the handler is mandatory. Setting PageIndex without calling LoadStudents() leaves the grid showing the old page.

Built-in paging fetches every row and displays a slice. On 400 students that is acceptable; on 40,000 it is not, and the page will eventually time out. Real paging happens in SQL:

SELECT   Id, PublicId, Name, RollNumber, ClassName, Section
FROM     Student
WHERE    SchoolId = @SchoolId AND Status <> 1
ORDER BY ClassName, Section, Name
OFFSET   @Skip ROWS FETCH NEXT @PageSize ROWS ONLY;

Sorting needs the direction stored, because the grid does not remember it:

Private Property SortDirectionState As SortDirection
    Get
        If ViewState("SortDirection") Is Nothing Then
            Return SortDirection.Ascending
        End If

        Return CType(ViewState("SortDirection"), SortDirection)
    End Get
    Set(value As SortDirection)
        ViewState("SortDirection") = value
    End Set
End Property

Never concatenate e.SortExpression into SQL. It comes from the posted form and is user-controlled — that is a direct injection route. Map it through a whitelist:

Private Function ResolveSortColumn(expression As String) As String
    Select Case expression
        Case "Name"       : Return "Name"
        Case "RollNumber" : Return "RollNumber"
        Case "ClassName"  : Return "ClassName, Section"
        Case Else         : Return "Name"
    End Select
End Function

CRUD through stored procedures

Public Sub SaveStudent(student As Student)
    Using connection As New SqlConnection(_connectionString)
        Using command As New SqlCommand("usp_SaveStudent", connection)
            command.CommandType = CommandType.StoredProcedure

            command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = student.SchoolId
            command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = student.PublicId
            command.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = student.Name
            command.Parameters.Add("@RollNumber", SqlDbType.NVarChar, 20).Value = student.RollNumber
            command.Parameters.Add("@ClassName", SqlDbType.NVarChar, 10).Value = student.ClassName

            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

            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

Four things this gets right: parameters everywhere, CommandType.StoredProcedure set, explicit types and sizes, and DBNull.Value for the nullable column. Omitting the CommandType line sends the procedure name as a literal statement and produces "incorrect syntax near 'usp_SaveStudent'".

The save handler:

Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
    If Not Page.IsValid Then
        Return
    End If

    Try
        SaveStudent(BuildStudentFromForm())

        Response.Redirect("~/Students/List.aspx?saved=1", False)
        Return

    Catch ex As SqlException When ex.Number = 2627
        lblError.Text = "This roll number is already used by another student."

    Catch ex As SqlException
        _logger.Error("Save failed", ex)
        lblError.Text = "Could not save. Please try again."
    End Try
End Sub

The redirect after a successful save is what stops a refresh re-submitting the form and creating a duplicate. Response.Redirect(url, False) followed by Return avoids the ThreadAbortException that the Catch blocks would otherwise log as a failure.

SqlDataSource

<!-- Common in legacy code. Avoid adding more of it. -->
<asp:SqlDataSource ID="sdsStudents" runat="server"
    ConnectionString="<%$ ConnectionStrings:SchoolDb %>"
    SelectCommand="SELECT * FROM Student WHERE SchoolId = @SchoolId">
    <SelectParameters>
        <asp:SessionParameter Name="SchoolId" SessionField="SchoolId" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

SqlDataSource puts SQL in the markup and binds a grid with no code. It demonstrates well and maintains badly: the query cannot be unit tested, business rules have nowhere to live, and the SQL is invisible to anyone searching the code-behind.

Do not convert working SqlDataSource pages as an incidental change — that is a redesign. Do not add new ones.

Diagnosing binding failures

Symptom	Cause
Grid empty, no error	`DataBind()` not called, or the query returned nothing
Grid shows unexpected columns	`AutoGenerateColumns="True"`
Grid resets after every click	Bound in `Page_Load` with no `IsPostBack` guard
Row command never fires	`CommandName` mismatch, or the handler is not wired
`DataKeys` empty on postback	`DataKeyNames` not set, or ViewState disabled on the grid
Paging shows the old page	`LoadStudents()` not called after setting `PageIndex`
Dropdown placeholder missing	Inserted before `DataBind()`
`ArgumentOutOfRangeException` on `SelectedValue`	The value is not in the list — use `FindByValue`
`NullReferenceException` in `RowDataBound`	`FindControl` returned `Nothing`, or the row is a header
Page enormous and slow	Grid ViewState — check with `Trace="true"`

RowDataBound runs for headers, footers, and pagers as well as data rows. Always filter first:

Protected Sub grdStudents_RowDataBound(sender As Object, e As GridViewRowEventArgs)
    If e.Row.RowType <> DataControlRowType.DataRow Then
        Return
    End If

    Dim status As Label = TryCast(e.Row.FindControl("lblStatus"), Label)

    If status Is Nothing Then
        Return
    End If

    status.Text = GetStatusText(DataBinder.Eval(e.Row.DataItem, "Status"))
End Sub

Omitting that filter is the standard cause of a null-reference on the header row, which surfaces as an error before any data appears.

Errors you will hit

What you see	Cause	Fix
Grid is empty but the query returns rows in SSMS	Bound inside `If Not IsPostBack` and never rebound after a change	Rebind after every insert, update and delete
Grid resets to page 1 after editing	Rebound without restoring `PageIndex`	Set it back after rebinding
`Index was out of range` on a row command	Row index used after the grid was rebound	Read the key from `DataKeys`, not the index
Edit saves the wrong row	Relied on the row index instead of the primary key	Use `DataKeyNames`
`Must declare the scalar variable '@SchoolId'`	Parameter never added to the command	Add every parameter the SQL names
A search box breaks on a name with an apostrophe	SQL built by concatenation	Use parameters — this is also the injection hole
A user sees another school's rows	`SchoolId` missing from the `WHERE` clause	Filter every query, and take `SchoolId` from the session, not the page

The last two rows are the same bug wearing different clothes. Concatenated SQL and a missing tenant filter both let a user reach data that is not theirs, and neither produces an error.

Common mistakes

Setting DataSource and forgetting DataBind()
Binding in Page_Load with no IsPostBack check
AutoGenerateColumns="True" over a SELECT *, exposing columns
No EmptyDataText, so no rows looks like a broken page
Inserting a dropdown placeholder before DataBind()
SelectedValue = instead of FindByValue with a null check
Putting a record id in CommandArgument and trusting it
Omitting the tenant filter, so a tampered index reaches another school's row
Concatenating e.SortExpression into SQL
No RowType filter in RowDataBound
Forgetting CommandType.StoredProcedure
Returning to the page after a save instead of redirecting

Practice

Build the course exercise — bind a GridView and then complete the CRUD page. Create a student list with search, paging, and Edit and Delete row commands, backed by stored procedures. Requirements: AutoGenerateColumns="False", DataKeyNames="PublicId", EmptyDataText set, every command handler taking SchoolId from session, a confirmation on delete, and a redirect after every successful save.

Then verify the failure modes deliberately:

Comment out DataBind(). Confirm the grid is empty with no error.
Remove the IsPostBack guard, search, then page. Watch the filter reset.
Set AutoGenerateColumns="True" over a SELECT *. List what is now on the page that should not be.
Edit the CommandArgument in DevTools to another row index and submit. Confirm the session-based SchoolId filter still protects the record.
Press F5 after a save without a redirect. Confirm the duplicate, then add the redirect.

Then run the course debugging exercise — trace a stored procedure error. Remove CommandType.StoredProcedure, then misspell a parameter name, then omit a required one, and record the exact SqlException message for each.

You can now

Bind a GridView and rebind it correctly after every change
Use DataKeyNames so commands act on the right row
Write parameterised commands, and say why concatenation is unsafe
Keep paging and sorting working across postbacks
Filter every query by SchoolId taken from the session
Diagnose an empty or resetting grid from the symptom alone

Review questions

Why is AutoGenerateColumns="False" a security setting, not just a formatting one?
Why look a record key up from DataKeys rather than trusting CommandArgument?
Why must a dropdown placeholder be inserted after DataBind()?
Why filter on RowType at the start of RowDataBound?

Next: Legacy student records module

Before you start​

Learning objective​

Topics​

Binding basics​

GridView columns​

Eval versus Bind​

DropDownList binding​

Row commands and DataKeys​

Paging and sorting​

CRUD through stored procedures​

SqlDataSource​

Diagnosing binding failures​

Errors you will hit​

Common mistakes​

Practice​

You can now​

Review questions​