How to Validate User Input With Values From a Database
Okay, nothing profound or earth-shattering, but I see this question asked so often in the forums that I figured I'd create a blog post about it. That way, the next time someone asks this question I can simply point them here. ;-)
I'm sure you're all familiar with the various ASP.NET validation controls. These allow you to validate user input for required entries (RequiredFieldValidator), against known values or data types (CompareValidator), against patterns (RegularExpressionValidator) and so on. But what if you need to validate using values stored in a database, where validation succeeds or fails based on the existence or (non-existence) of the entered value?
Here, we'll create a very simple page with only a textbox, a button, a label, a couple of validation controls, and a data source. (I'm using a SqlDataSource for simplicity; obviously, you can use any data source you have, such as ObjectDataSource, DataSets, or whatever.) This will be part of a softball league signup site. The user must enter a unique name for his or her team. If that name is already used by another team, the user is warned and asked to pick another name. If the name is unique, the application accepts it.
For this example, we're going to be using a real simple database -- only one table, containing the names of the existing teams. (Please excuse the extreme lameness of the team names I came up with.)
Here is the ASPX code for the page shown above:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Pick a Name for Your Teamtitle>
head>
<body>
<form id="form1" runat="server">
<div>
<h2>
Pick a Name for Your Teamh2>
Name:
<asp:TextBox ID="txtTeam" runat="server" />
<asp:Button ID="btnValidate" runat="server" Text="Go" />
<br />
<asp:RequiredFieldValidator ID="reqval_txtTeam" runat="server"
ErrorMessage="You must enter a team name."
ControlToValidate="txtTeam" Display="Dynamic" />
<asp:CustomValidator ID="custval_txtTeam" runat="server"
ErrorMessage="That name is already taken. Please pick another."
ControlToValidate="txtTeam" Display="Dynamic"
OnServerValidate="custval_txtTeam_ServerValidate" />
<asp:Label ID="lblResult" runat="server" EnableViewState="False" />
<asp:SqlDataSource ID="SqlDataSourceTeams" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [TeamName] FROM [Teams]">asp:SqlDataSource>
div>
form>
body>
html>
You'll notice there are two validation controls used to validate the textbox. One of them is a RequiredFieldValidator. We need this because we obviously can't test against non-existent input. The other is a CustomValidator. The CustomValidator lets us define our own server side validation logic, which is wired with the OnServerValidate event handler. In the server side validation function, we're going to search the database for the entered value. If it's there, validation will fail and the CustomValidator's ErrorMessage will be displayed. If it's a unique name, validation passes and the user is notified to that effect.
protected void custval_txtTeam_ServerValidate(object source, ServerValidateEventArgs args)
{
args.IsValid = true;
foreach (DataRowView drv in SqlDataSourceTeams.Select(DataSourceSelectArguments.Empty))
{
if (drv["TeamName"].ToString() == args.Value)
{
args.IsValid = false;
break;
}
}
if (args.IsValid)
lblResult.Text = "Congratulations! That name is not taken.";
}
The actual logic is pretty simple. The ServerValidateEventArgs object has a boolean property IsValid which gets or sets whether the validation passes. We set args.IsValid = true at the beginning. We then loop through the collection of data rows returned by the SqlDataSource. For each row, we check the value of the TeamName column. If the value from the row matches the entered value we are validating (specified by args.Value), then args.IsValid is set to false and the loop is terminated. In this case, validation fails. If no matches are found, the validation passes.
Obviously, you aren't limited to simple equality checks using this technique. You can use inequality operators, regular expressions, and so on to do complex validation against one or more database columns, or even across multiple tables.
Subscribe to this blog for more cool content like this!
