ASP Lecture 7: ASP and Database (2)

Author：Eve Cole Update Time：2009-05-30 19:55:02

In the previous lecture, we learned how to establish a connection with the database and retrieve data from the database. Today's content is how to add new data to the database, modify and delete data in the database.

1. Method 1 for adding new data to the database: Use SQL statements, for example wuf50.asp.
In order to simplify future procedures, the connection part with the Access database is placed in a file. This file will not be explained when it is needed in the future.
<% 'AdoAccess.asp
Option Explicit
Response.Expires = 0
'Part 1: Establishing a connection
Dim Cnn, StrCnn
Set Cnn = Server.CreateObject("ADODB.Connection")
StrCnn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:InetpubhomeaspNorthwind.mdb"
Cnn.Open StrCnn
%>
Program wuf50.asp
<% @LANGUAGE = VBScript %>

<% ' wuf50.asp
'Part 2: Add new data using Execute of Connection object
Dim StrSQL, rsTest
StrSQL = "INSERT INTO shipper (company name, phone number) VALUES('wu''feng','0571-7227298')"
Cnn.Execute StrSQL
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser
Set rsTest = Cnn.Execute("Select * From carrier")
Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>
Please note the following points:
1. When using SQL statements to add data to an Access database, you must use Insert Into. To add data to a SQL Server database, just use Insert.
2. The format of adding data using SQL statements is as in the above example. Note that 'wu'feng' needs to be added. 'wu''feng' must be used in the statement because SQL statements use ' as the delimiter of strings.
3． By combining this example with what you learned previously, you can add data from an HTML form.
4. Note that there is a field with a data type of automatic numbering, such as "Shipper ID" in this example, so you don't need to think about how to write code to obtain an incrementing number.

Method 2: Use the Addnew method of the Recordset object, for example wuf51.asp.
<% @LANGUAGE = VBScript %>


<% ' wuf51.asp
'Part 2: Add new data using the AddNew method of the Recordset object
Dim StrSQL, rsTest
Set rsTest = server.CreateObject("ADODB.Recordset")
rsTest.CursorType = adOpenKeySet 'adOpenDynamic
'Without the following sentence, the database will not be allowed to be updated. Why?
rsTest.LockType = adLockOptimistic
rsTest.Open "Shipper",Cnn,,,adCmdTable

rsTest.AddNew
rsTest("Company Name") = "wu'feng"
rsTest("Telephone") = "0571-7227298"
rsTest.Update
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser
'Move the database pointer to the first record in the table
If Not rsTest.EOF <> 0 Then
Response.Write "There are [" & rsTest.RecordCount & "] pieces of data in the table" & " "
rsTest.MoveFirst
End If

Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>
analyze:
1. Why set rsTest.LockType = adLockOptimistic
The LockType property of the Recordset object has four optional values:
adLockReadOnly - the default value, indicating that the recordset is opened in read-only mode, so the data cannot be changed. In this case, an error will occur when using the AddNew method.
adLockPessimistic - Conservative record locking (entry-by-entry). Use the method of immediately locking the records of the data source when editing. At this time, other users cannot access the data.
adLockOptimistic – Open record locking (entry-by-entry). Records are locked only when the Update method is called. Think about it, is this property similar to the Lock and Unlock properties of the Application object we talked about?
adLockBatchOptimistic - open batch updates. Used to update data in batches, corresponding to the UpdateBatch method.
By the way, let's mention the CursorType attribute mentioned in the previous lecture, which also has four values:
adOpenForwardOnly - Forward only cursor, default value, can only scroll forward in the record. This saves resources and improves performance.
adOpenStatic - static cursor. A static copy of a collection of records that can be used to find data or generate reports. Additionally, additions, changes, or deletions made by other users are not visible. It is recommended to use only these two cursors in ASP.
adOpenKeyset - Keyset cursor. Keyset cursors are similar to dynamic cursors, except that they are prohibited from viewing records added by other users and are prohibited from accessing records deleted by other users. Data changes made by other users will still be visible.
adOpenDynamic - dynamic cursor. Can see additions, changes, and deletions made by other users. All types of moves within the recordset are allowed.
What is certain is that such an abstract description is somewhat specious, and I still don’t quite understand it. To put it simply,
(1) If you only retrieve data, just use the default value;
(2) If you use the Update method to update a piece of data, use adLockOptimistic for the LockType attribute, and use adLockBatchOptimistic if you use the UpdataBatch method to update data in batches.
(3) If there is a write action to the database, it is usually enough to use adOpenKeyset for the CursorType attribute.
How about it? Even if you don’t understand it yet, you can use it.

2. If you are not well versed in databases, it is often beneficial to use rsTest.MoveFirst to move the pointer to the first record before output is displayed. However, if there is no data in the database, the MoveFirst method cannot be used, so use the rsTest.EOF attribute to determine whether there is data in the database before use.
3． The RecordCount property (to obtain the number of records in the recordset) can be used only when the cursor type is set to adOpenKeyset or adOpenStatic.

2. Modify existing data in the database. Method 1: Use SQL statements. For example, wuf52.asp, the program is basically similar to wuf50.asp, only the key parts are listed here.
'Part 2: Modify data using the Execute method of the Connection object
Dim StrSQL, rsTest
StrSQL = "UPDATE carrier SET phone = '(503) 555-3188' WHERE phone LIKE '%99%'"
Cnn.Execute StrSQL
To modify the data, you do not need to use INSERT INTO...VALUES, but use the UPDATE...SET statement. The WHERE clause means to change the phone number containing the string "99" ("LIKE" and "%" are often used in fuzzy queries) to (503) 555-3188, if no conditions are set, all phone numbers in the table will be changed.

Method 2: Use the Update method of the Recordset object. Program wuf53.asp (similar to routine wuf51.asp)
'Part 2: Modify data using the Update method of the Recordset object
Dim StrSQL, rsTest
Set rsTest = server.CreateObject("ADODB.Recordset")
rsTest.LockType = adLockOptimistic
StrSQL = "SELECT last name, first name, date of birth FROM employee WHERE date of birth = #55-03-04#"
rsTest.Open StrSQL, Cnn,,,adCmdText

rsTest("name") = "Chinese"
rsTest.Update
analyze:
1. In the SQL statement, if the database is an Access database, the date should be enclosed in #55-03-04#, as in this example; if it is a SQL Server database, the date should be enclosed in '55-03-04'.
2. In rsTest.Open StrSQL, Cnn,,,adCmdText, since the first parameter is a SQL statement, the fifth parameter is adCmdText. In fact, the fifth parameter can be omitted, but adding it will make the script execution more efficient. high.
3． Using method one, you can update all records that meet the conditions (multiple records or one record) at one time, but Update in method two can only modify the current record (the first record that meets the conditions).

3. Delete data in the database. Method 1: Use SQL statements. Routine wuf55.asp
'Part 2: Use SQL statements to delete data
Dim StrSQL, rsTest
StrSQL = "DELETE FROM carrier WHERE phone number = '0571-7227298'"
Cnn.Execute StrSQL

Method 2: Use the Delete method of the Recordset object. Routine wuf56.asp
'Part 2: Use the Delete method of the Recordset object to delete data
Dim StrSQL, rsTest
Set rsTest = server.CreateObject("ADODB.Recordset")
rsTest.LockType = adLockOptimistic
StrSQL = "SELECT * FROM carrier WHERE phone number = '0571-7227298'"
rsTest.Open StrSQL, Cnn,,,adCmdText

While Not rsTest.EOF
rsTest.Delete
rsTest.MoveNext
Wend
If there are multiple records in the record set that meet the conditions, you must use a loop. Otherwise, the Delete method only deletes the current record, that is, the first record that meets the conditions.

4. Other useful knowledge
1. Updating data in batches Above we talked about how to use the Update method of the Recordset object to update data. In fact, the Recordset object can support two types of updates: immediate updates and batch updates.
With immediate update, all changes to the data will be written to the current data source immediately once the Update method is called.
Batch updates enable a provider to cache changes to multiple records and then use the UpdateBatch method to transfer them to the database in a single call. When updating multiple records, batch updates are more efficient than immediate updates.
The default is immediate update mode. To use batch update mode, use a client cursor, such as wuf54.asp.
<% @LANGUAGE = VBScript %>


<% ' wuf54.asp
'Part 2: Batch update mode
Dim StrSQL, rsTest
Set rsTest = server.CreateObject("ADODB.Recordset")
rsTest.CursorLocation = adUseClient 'Use client cursor type
rsTest.LockType = adLockBatchOptimistic
StrSQL = "SELECT * FROM carrier WHERE phone LIKE '%99%'"
rsTest.Open StrSQL, Cnn,,,adCmdText

rsTest.MoveFirst
While Not rsTest.EOF
rsTest("Company Name") = "Chinese"
rsTest.MoveNext
Wend
rsTest.UpdateBatch
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser
rsTest.Requery
Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>
Notice:
1) rsTest.CursorLocation = adUseClient has two values, the other value is adUseServer (default). For beginners, the cursor type of the Recordset object is a difficult part. I will not introduce it in detail here to avoid getting more and more confused. Please explore slowly in actual processing (try more).
2) rsTest.Requery: Use the Requery method to refresh the entire contents of the Recordset object of the data source. Calling this method is equivalent to calling the Close and Open methods successively.

2. Learn to use the Filter property of the Recordset object
<% @LANGUAGE = VBScript %>


<% ' wuf57.asp
'Part 2: Using the Filter property of the Recordset object
Dim StrSQL, rsTest
Set rsTest = server.CreateObject("ADODB.Recordset")
rsTest.CursorType = adOpenStatic
rsTest.LockType = adLockOptimistic
rsTest.Open "Shipper",Cnn,,,adCmdTable

'Filter out records that meet the criteria, while other records are filtered out
rsTest.Filter = "Company name = 'wu''feng'"
If rsTest.EOF Then 'If there is no such record, add it
rsTest.AddNew
rsTest("Company Name") = "wu'feng"
rsTest("Telephone") = "0571-7227298"
rsTest.Update
Else 'If there is a record that meets the condition, modify the first record that meets the condition
rsTest("Phone") = "(571) 7227298"
rsTest.Update
End If
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser
'Please carefully compare the difference between yes and no in the following sentence
'rsTest.Filter="" 'Used to clear the Filter attribute
rsTest.MoveFirst
Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>

3． In addition to the two methods introduced above, you can also use SQL statements and the Excute method of the Command object to maintain the database. Example wuf58.asp
<% @LANGUAGE = VBScript %>


<% ' wuf58.asp
'Part 2: Maintain the database using SQL statements and the Excute method of the Command object
Dim StrSQL, rsTest, cmdChange
StrSQL = "INSERT INTO shipper (company name, phone number) VALUES('wu''feng','0571-7227298')"

'Create command object.
Set cmdChange =server.CreateObject("ADODB.Command")
Set cmdChange.ActiveConnection = Cnn
cmdChange.CommandText = StrSQL
cmdChange.Execute
%>
<HTML>
<BODY>
<% 'Part 3: Display the obtained record set on the browser
Set rsTest = server.CreateObject("ADODB.Recordset")
rsTest.Open "Shipper", Cnn, , , adCmdTable
Do While Not rsTest.EOF
Response.Write rsTest(0) & " " & rsTest(1) & " " & rsTest(2) & " " & " "
rsTest.MoveNext
Loop

'Part 4: Cleaning up the battlefield
Cnn.close
Set rsTest = Nothing: Set Cnn = Nothing
%>
</BODY>
</HTML>

This lecture mainly introduces three methods of maintaining data. Beginners only need to master the first two methods. Generally speaking, try to use SQL statements to solve problems, which is simple and clear; the biggest advantage of using Recordset objects is that you can make use of its large number of properties and rich cursor types, and you have more choices, but it also brings some difficulties to use. The key It’s about exploring more and experimenting more.