RSS |
14.2.4- Using SQL Commands to Insert and Delete Data
Created
by Brendan Doss.
|
| << 14.2.3- Deleting Records Using a Recordset | Chapter14 | 14.2.5- Using the User's Choices to Customize the Page >> |
Using SQL Commands to Insert and Delete Data
The Recordset object, being arguably the central object in ADO, is the one that features the special methods like AddNew, Update and Delete that enable us to insert and delete records easily. But as you'd expect, there's more than one way. In this section we'll demonstrate that we can use SQL commands to do the job too.
The INSERT and DELETE Commands
We've already seen the SQL SELECT and UPDATE commands in action, and now we'll look at two more: INSERT and DELETE. We'll demonstrate these together, since they obviously have complementary abilities. The INSERT and DELETE statements perform the same functions as the AddNew and Delete methods of the Recordset object that we've just discussed.
In SQL, the INSERT command takes the following structure:
INSERT INTO <table> (<fields>) VALUES (<values>)
Here, <fields> is a list consisting of none, some, or all of the fields on the table, and <values> is a list of the values that are to be assigned to those fields. Be careful to get the <fields> and their corresponding <values> in the same order! Here's an example:
INSERT INTO Movies (MovieId, Title) VALUES (331, 'Psycho')
Bear in mind that the MovieId field is intended to be a unique identifier (although in this database it is not actually defined as a primary key) in the Movies table so each value entered should be unique. We can only enter the value '331' here because we know for a fact that this value doesn't currently exist for this field. A much better way of maintaining a unique field, as you will see in Chapter 15 , is to let the database generate the value.
If you omit a field, then a NULL (unknown) value will be inserted into that field. Beware that some fields are specifically unable to accept a NULL value (such as a primary key field) – and if you don't specify a value for such a field then your command will be rejected. The existence of 'non-NULL' fields within your database will depend on how your database was designed.
The SQL DELETE command is even easier to use. It takes the following format:
DELETE FROM (<table>) WHERE (<condition>)
You can only use DELETE to delete one or more whole records from the database. In particular, if you want to remove values from a number of the fields in a record then DELETE is the wrong thing to use – you need to assign specific 'empty' values to those fields instead.
We can use the DELETE command to delete multiple values at the same time, so be careful when specifying criteria. Make sure that your criteria are specific enough – otherwise you may find yourself deleting records that you really wanted to keep. If we were to DELETE the above record from the table we could use:
DELETE FROM AllMovies WHERE Title LIKE 'Psycho'
This would delete the record that we created with the INSERT command above (and it would also delete any other records in the AllMovies table whose Title value was equal to Psycho!). To give a brief demonstration, let's look at an example that inserts a record, displays it and then deletes it.
Try It Out – Running the SQL INSERT and DELETE statements
This example uses a single ADO Command object to execute three different SQL commands at different times: first an INSERT, then a SELECT (which returns a recordset), and finally a DELETE.
1. We're using the Movies database and the strConnect connection string again, so have the DataStore.asp SSI file and either Movie2000.mdb or Movie2000.mdf at the ready!
2. Start up your favorite ASP editor and create the following code (which we'll call SQLInsertDelete.asp):
<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>Using SQL and the Command Object</TITLE>
</HEAD>
<BODY>
<%
Dim objRS, objComm, intNoOfRecords
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandText = "INSERT INTO Movies(MovieId, Title) VALUES (331, 'Psycho')"
objComm.CommandType = adCmdText
objComm.Execute intNoOfRecords
Response.Write "The INSERT command has been executed; " & _
"Number of records inserted = " & intNoOfRecords & "<HR>"
objComm.CommandText = "SELECT * FROM Movies WHERE Title LIKE 'Psycho'"
Set objRS = objComm.Execute
Response.Write "The SELECT command has been executed, " & _
"and has selected the following records: <BR>"
While Not objRS.EOF
Response.Write "MovieID = " & objRS("MovieID") & _
"; Title = " & objRS("Title") & "<BR>"
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
objComm.CommandText = "DELETE FROM Movies WHERE Title LIKE 'Psycho'"
objComm.Execute intNoOfRecords
Response.Write "<HR>The DELETE command has been executed, " & _
"Number of records deleted = " & intNoOfRecords & "<BR>"
Set objComm = Nothing
%>
</BODY>
</HTML>
3. Save SQLInsertDelete.asp into your \inetpub\wwwroot\BegASPFiles folder. Now view it in your browser.
|
|
How It Works
We'll ignore the lines that set up the database, as they'll be very familiar by now. The first line of interest is where we set up the first of our three commands – the INSERT – which inserts an entry for Psycho into the Movie2000 database:
objComm.ActiveConnection = strConnect
objComm.CommandText = "INSERT INTO Movies(MovieId, Title) VALUES (331, 'Psycho')"
objComm.CommandType = adCmdText
We also use the CommandType property to indicate that the command is a SQL statement. We then execute our INSERT command, and specify a variable to capture the value of the RercordsAffected parameter, which will inform us of how many records were affected by the action. We can use that captured information in our report of the command's execution:
objComm.Execute intNoOfRecords
Response.Write "The INSERT command has been executed; " & _
"Number of records inserted = " & intNoOfRecords & "<HR>"
Now we set up the second command – the SELECT command, which will select only our new record from the data store:
objComm.CommandText = "SELECT * FROM Movies WHERE Title LIKE 'Psycho'"
Note that we don't need to set the ActiveConnection and CommandType properties – they will retain the values we set earlier on in the page.
The SELECT statement looks for any movies with the title 'Psycho' and displays all of the information in the associated record. We then execute the command. Of course, because it's a SELECT it returns a recordset, which we capture:
Set objRS = objComm.Execute
Now we'll quickly report the contents of the recordset, just to show that the new record really does exist in the data store:
Response.Write "The SELECT command has been executed, " & _
"and has selected the following records: <BR>"
While Not objRS.EOF
Response.Write "MovieID = " & objRS("MovieID") & _
"; Title = " & objRS("Title") & "<BR>"
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
After we've written this report we've finished with the objRS recordset, so we remove it from memory.
Lastly, we re-assign the CommandText property to the third of out SQL commands – the DELETE command, which deletes every record whose title is 'Psycho'. Again, we don't need to re-assign the values of the ActiveConnection and CommandType properties. We execute the command and return the number of records affected to demonstrate the success of the operation:
objComm.CommandText = "DELETE FROM Movies WHERE Title LIKE 'Psycho'"
objComm.Execute intNoOfRecords
Response.Write "<HR>The DELETE command has been executed, " & _
"Number of records deleted = " & intNoOfRecords & "<BR>"
| << 14.2.3- Deleting Records Using a Recordset | Chapter14 | 14.2.5- Using the User's Choices to Customize the Page >> |
