Using ADO.NET to Insert a New Record | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 6 - Creating and Inserting Records

byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Now that we've seen some basic SQL commands with which to insert new records, and taken a look at some of the rules that govern the scope of what we can do, we can start writing some code that uses ADO.NET's command objects to insert new records into our database.

The steps necessary to use an SQL INSERT command can be summarized as follows:

Open a connection to the database
Create a new command object
Define an SQL command
Execute the SQL command
Close the connection

That at least sounds fairly straightforward, so let's try it out.

Try It Out - Adding a New Record Using a Command Object

In this exercise, we will write code that adds a new category to the Northwind database's Categories table. This table has an auto-increment primary key whose value we'll retrieve after execution of the INSERT statement. In real-world code, it's often necessary to know the value generated by the database, in order to use it as a foreign key in related tables.

In a directory called ch06 under our webroot folder, create a new text file called Insert_With_Return.aspx.

Add the following code to the file (or download it from the Wrox web site). Because the activity of adding records to databases has a high potential for error, we've wrapped the data access code in a Try…Catch…Finally block:

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html>   <head>     <title>Adding a New Record</title>   </head> </html> <script language="VB" runat="server"> Sub Page_Load(Source as Object, E as EventArgs)   ' Connection setup   Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")   Dim objConnection As New SqlConnection(strConnection)   Dim strSQL As String = "INSERT INTO Categories (CategoryName,Description)" & _   " VALUES ('Guitars', 'Here you can find just the" & _                          " guitar you were looking for'); " & _                          "SELECT @@IDENTITY As 'Identity'"   Dim dbComm As New SqlCommand(strSQL, objConnection)   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()) End Sub </script>

When you browse to this page, and assuming that you haven't previously made changes to the Categories table, this is what you'll see:

If you have previously made changes to the Categories table, you may find that the ID of the new record is a number greater than 9. This is not a cause for concern.

How It Works

This example progresses in similar fashion to the ones in earlier chapters - especially those in Chapter 4 - until we get to the definition of the SQL statement:

      Dim strSQL As String = "INSERT INTO Categories (CategoryName,Description)" & _                             " VALUES ('Guitars', 'Here you can find just the" & _                             " guitar you were looking for');" & _                             "SELECT @@IDENTITY As 'Identity'"

What you're looking at here is actually two statements in one. First is the INSERT statement itself, which looks just like the other examples of INSERT statements you've seen so far, and provides values for the mandatory CategoryName field and non-mandatory Description field of the Categories table. CategoryID is an auto-increment primary key, so we don't provide a value for that.

The second part of the string is SELECT @@IDENTITY As 'Identity'. This command asks SQL Server to return the primary key value that it just created for our new record. We retrieve it by using the command object's ExecuteScalar() method, which returns only the first value of the first row of a result set (unlike ExecuteReader(), which returns the whole thing).

        Dim iID As Integer        Try          objConnection.Open()          iID = dbComm.ExecuteScalar()

Finally, we have to free the server's resources by closing the connection to the database, and checking for an exception that will be raised from the database should an error occur:

        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())

Note that if you refresh this page in your browser, another record will be added to the table. CategoryName and Description values are not checked for similarity with existing records, so the database will just generate another new primary key value, and assume that we know what we're doing.