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.
Try It Out - Using the RequiredFieldValidator Control
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.
How It WorksLet'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:
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
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 ProviderBefore 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. |