ASP.NET Validation Controls

Chapter 6 - Creating and Inserting Records
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

The example in the last section demonstrated how to insert new records into a database simply by hardwiring an SQL INSERT statement into our code. In our ASP.NET applications, we'll often want to retrieve information from a form within the ASP.NET page, and insert those values into the database. That's what we're going to look at now.

As soon as we start allowing our users to enter data, we increase the risks of errors being introduced into the database. And if they fail to provide a value for a mandatory field, the data won't get into the database at all. The standard way of dealing with this and related problems is to add some client-side script code that informs users about their mistakes before posting data to the server, so that such mistakes can be dealt with smoothly and efficiently. ASP.NET offers convenient a solution to facilitate checking of text controls in a form: the validation controls.

ASP.NET validation controls can be placed anywhere you like in any page that requires them. By using attributes to set their properties, we can inform them about the controls they have to check. The following table lists the five validation controls available.

Syntax

Description

<asp: RequiredFieldValidator>

Checks that a specific input control contains a value.

<asp:CompareValidator>

Compares the contents of two input controls. A typical use is for 'password' and 'confirm password' text fields.

<asp:RangeValidator>

Checks that the specific input control has a value in the range of permitted values.

<asp:RegularExpressionValidator>

Checks the value of the specific input control against the regular expression provided.

<asp:CustomValidator>

Performs a custom check on an input control.

In addition to the examples in the following pages, contains further demonstrations of the ASP.NET validation controls at work.

Try It Out - Using the RequiredFieldValidator Control

start example

In this exercise, we're going to create an ASP.NET page that can add records to the Categories table of the Northwind database. Because the Categories table has a mandatory field (CategoryName), we're going to use a RequiredFieldValidator control.

  1. Create a new text file in the ch06 folder, and name it Required_Field.aspx.

  2. Add the following code to the file:

     <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html>   <head>     <title>Validating a Field</title>   </head>   <body>     <form  method="post" runat="server">       <table               style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px"              cellSpacing="0" cellPadding="0" width="300" border="0">         <tr>           <td style="WIDTH: 115px">             <asp:Label  runat="server">CategoryName</asp:Label>           </td>           <td>             <asp:TextBox  runat="server" width="193" />           </td>         </tr>         <tr>           <td style="WIDTH: 115px">             <asp:Label  runat="server">Description</asp:Label>           </td>           <td>             <asp:TextBox  runat="server" width="193" />           </td>         </tr>         <tr>           <td style="WIDTH: 115px" colSpan="2">             <asp:Button  runat="server"                  OnClick="btnInsert_Click" width="298" text="INSERT!" />           </td>         </tr>       </table>       <asp:RequiredFieldValidator  runat="server"           style="Z-INDEX: 102; LEFT: 316px; POSITION: absolute; TOP: 14px"           ErrorMessage="Please insert the new category name"           ControlToValidate="txtCategoryName" />     </form>   </body> </html> <script language="VB" runat="server"> Dim objConnection As SqlConnection Sub Page_Load(Source as Object, E as EventArgs)   ' Create a new connection object pointing to the database   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   objConnection = New SqlConnection(strConnection) End Sub Sub btnInsert_Click(Sender As Object, E As EventArgs)   If Page.IsValid Then     Dim strSQL As String="INSERT INTO Categories (CategoryName,Description)" & _                          " VALUES (@CategoryName, @Description);" & _                          " SELECT @@IDENTITY AS 'Identity' "     Dim dbComm As New SqlCommand(strSQL, objConnection)     dbComm.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15)     dbComm.Parameters.Add("@Description", SqlDbType.NText)     dbComm.Parameters("@CategoryName").Value = txtCategoryName.Text     dbComm.Parameters("@Description").Value = txtDescription.Text     Dim iID as Integer     Try       objConnection.Open()       iID = dbComm.ExecuteScalar()     Catch ex As Exception       Response.Write(ex.Message)       Response.End     Finally       If objConnection.State = ConnectionState.Open Then         objConnection.Close()       End If     End Try     Response.Write("The ID of the new record is: " & iID.ToString())     Response.End   End If End Sub </script> 

  3. Now let's try to execute this page by pressing the Insert! button without first inserting a value into the CategoryName text field. This is what you'll see:

    click to expand

end example

How It Works

Let's start by examining the HTML code, where the major innovation is the inclusion of the RequiredFieldValidator control. As you can see, we use the ControlToValidate attribute to link this control with the control to be validated, and specify an ErrorMessage attribute that represents the message to be displayed in the ASP.NET page should it be posted without a value in the mandatory input control:

          <asp:RequiredFieldValidator  runat="server"              style="Z-INDEX: 102; LEFT: 316px; POSITION: absolute; TOP: 14px"              ErrorMessage="Please insert the new category name"              ControlToValidate="txtCategoryName" /> 

Now we can examine what happens when the user clicks the INSERT! button. First of all, we have to check that all of the "validator" controls have validated their respective input controls successfully. We obtain this information using the IsValid property, exposed by the global Page class. When this property is True, every control has been validated successfully:

    Sub btnInsert_Click(Sender As Object, E As EventArgs)      If Page.IsValid Then 

Once we know that the page is valid, we can retrieve the values from the input controls and insert them into the database. For this, as usual, we have to define a new command object, and set its properties to use the connection object that was created in the page's Page_Load() event handler. On this occasion, however, we've used a different form of syntax in the INSERT statement:

        Dim strSQL As String="INSERT INTO Categories (CategoryName,Description) " & _                             " VALUES (@CategoryName, @Description);" & _                        " SELECT @@IDENTITY AS 'Identity' " 

In the parentheses following the VALUES keyword, instead of specifying values that have to be inserted in the database, we use two placeholders: @CategoryName and @Description. Now, we could have used concatenation in order to construct our SQL command, but by doing things this way, we get ourselves at least two advantages:

  • The code is easier to write and to manage

  • We can avoid having to worry about characters such as quotation marks

For instance, if we were to insert a description in one of the text fields in our form, we might inadvertently use some undesirable characters, such as quotation marks. When we use string concatenation to get this information into the database, we find ourselves writing code like this:

    Dim strSQL As String = "INSERT INTO Categories (CategoryName, Description) " & _                           "VALUES ('" & txtCategory.Text & " ', ' " & _                           txtDescription.Text & " ')" 

Now imagine that a user types a string such as "That's my category" into the txtDescription field. The strSQL string we create will effectively contain the following INSERT command:

    Dim strSQL As String = "INSERT INTO Categories (CategoryName, Description) " & _                           "VALUES ('category', 'That's my category')" 

Examining the string carefully, you can see that executing this command would generate a syntax error as a result of the odd numbers of single quotation marks.

Returning to the code of the example at hand, the command object needs to know which placeholders to use during execution of the INSERT command. To this end, we have to use its Parameters collection, adding a separate entry for each placeholder. Additionally, we have to specify the data type (and, for character types, the maximum length) of each field.

        Dim dbComm As New SqlCommand(strSQL, objConnection)        dbComm.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15)        dbComm.Parameters.Add("@Description", SqlDbType.NText)        dbComm.Parameters("@CategoryName").Value = txtCategoryName.Text        dbComm.Parameters("@Description").Value = txtDescription.Text 

For more information about using parameters in your database queries, there's a longer discussion on the subject in , when we talk about using stored procedures in SQL Server databases.

Finally, we use the command object's ExecuteScalar() method to execute the INSERT statement, just as we did in the previous example.

        Dim iID as Integer        Try          objConnection.Open()          iID = dbComm.ExecuteScalar()        Catch ex As Exception          Response.Write(ex.Message)          Response.End        Finally          If objConnection.State = ConnectionState.Open Then            objConnection.Close()          End If        End Try        Response.Write("The ID of the new record is: " & iID.ToString())        Response.End 

If you now try the application again, this time providing values for both fields, you should find that the database is updated just as it was in our first example.

Parameters Using the OLE DB Provider

Before concluding this section, it's worth taking one of our occasional detours to examine the differences between the SQL Server and OLE DB .NET data providers, for this is one situation in which they're quite significant. In particular, the way that we declare the VALUES to be provided to the INSERT statement must change, as you can see from the listing below:

    <%@ Import namespace="System.Data" %>    <%@ Import namespace="System.Data.OleDb" %>    <!-- HTML section as before -->    <script language="VB" runat="server">    Dim objConnection As OleDbConnection    Sub Page_Load(Source As Object, E As EventArgs)        objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " &_        "data source=C:\BegASPNETdb\Datastores\NWind.mdb")    End Sub    Sub btnInsert_Click(Sender As Object, E As EventArgs)      If Page.IsValid Then        Dim strSQL As String = "INSERT INTO Categories " & _                               "(CategoryName, Description) VALUES (?, ?)"        Dim dbComm As New OleDbCommand(strSQL, objConnection)        dbComm.Parameters.Add("CategoryName", OleDbType.VarChar, 32, "CategoryName")        dbComm.Parameters.Add("Description", OleDbType.VarChar, 128, "Description")        dbComm.Parameters("CategoryName").Value = txtCategoryName.Text        dbComm.Parameters("Description").Value = txtDescription.Text        Try          objConnection.Open()          dbComm.ExecuteNonQuery()        Catch ex As Exception          Response.Write(ex.Message)          Response.End()        Finally          If objConnection.State = ConnectionState.Open Then            objConnection.Close()          End If        End Try        Response.Write("A new record has been added")        Response.End()      End If    End Sub    </script> 

Here, we specify a question mark for each parameter that we want to pass to the INSERT command, and then we create each parameter by specifying the name, the data type, the number of characters, and the source column name. Apart from a slight simplification with regard to acquiring a return value from the SQL command, the functionality of this example is identical to that of the SQL Server example.



Beginning ASP. NET 2.0 and Databases
Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)
ISBN: 0471781347
EAN: 2147483647
Year: 2004
Pages: 263

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net