Using ADO.NET to Insert a New Record
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
The steps necessary to use an SQL
command can be summarized as
That at least sounds
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
table. This table has an auto-increment primary key whose value we'll retrieve after execution of the
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
How It Works
This example progresses in similar fashion to the ones in earlier chapters -
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.