|
First, add new data to the database Method One: Use SQL statements, example wuf50.asp. To simplify future programs, place the connection portion of the Access database in a file that is not described later when it needs to be used. <% ' adoaccess.asp Option Explicit Response.Expires = 0 ' Part One: establishing a connection Dim Cnn, strcnn Set Cnn = Server.CreateObject ("ADODB.") Connection ") strcnn = "Provider = microsoft.jet.oledb.4.0; Data Source = C:\Inetpub\home\asp\Northwind.mdb " Cnn.open strcnn %> Program wuf50.asp <% @LANGUAGE = VBScript%> <!--#include file= "adoaccess.asp"--> <% ' wuf50.asp ' Part Two: Add new data using Execute with the Connection object Dim strSQL, Rstest strSQL = "INSERT into shippers (company name, phone number) VALUES (' Wu ' Feng ', ' 0571-7227298 ')" Cnn.execute strSQL %> <HTML> <BODY> <% ' Part III: Display the resulting recordset to the browser Set rstest = Cnn.execute ("SELECT * from Freight forwarders") Do as not rstest.eof Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> " Rstest.movenext Loop ' Part four: cleaning the battlefield Cnn.close Set rstest = Nothing:set Cnn = Nothing %> </BODY> </HTML> Please note the following points: 1. When you use SQL statements to add data to an Access database, you must use INSERT into to add data to the SQL Server database, and you can use the insert. 2. Format to add data using SQL statements as in the previous example, note that you need to add ' Wu ' Feng ', and you must use ' Wu ' Feng ' in the statement because the SQL statement uses ' as the delimiter for the string. 3. By combining this example with previous knowledge, you can implement adding data from an HTML form. 4. Note that there is a field with an AutoNumber data type, such as the "Shipping ID" in this example, so you don't have to think about how to write code to get an incremented number. Method Two: Use the AddNew method of the Recordset object, example Wuf51.asp. <% @LANGUAGE = VBScript%> <!--#include file= "adoaccess.asp"--> <!--#include file= "Adovbs.inc"--> <% ' wuf51.asp ' Part two: adding new data using the AddNew method of the Recordset object Dim strSQL, Rstest Set rstest = server. CreateObject ("ADODB.") Recordset ") Rstest.cursortype = adOpenKeyset ' adopendynamic ' Without this, you won't be allowed to update the database, why? Rstest.locktype = adLockOptimistic Rstest.open "Freight Forwarders", Cnn,,, adCmdTable Rstest.addnew Rstest ("company name") = "Wu ' Feng" Rstest ("telephone") = "0571-7227298" Rstest.update %> <HTML> <BODY> <% ' Part III: Display the resulting recordset to the browser ' Move the database pointer to the first record in the table If not rstest.eof <> 0 Then Response.Write Existing ["& Rstest.recordcount &"] data "&" <Br><Br> "in Table" Rstest.movefirst End If Do as not rstest.eof Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> " Rstest.movenext Loop ' Part four: cleaning the battlefield Cnn.close Set rstest = Nothing:set Cnn = Nothing %> </BODY> </HTML> Analysis: 1. Why to set rstest.locktype = adLockOptimistic The LockType property of the Recordset object has four optional values: adlockreadonly--the default value, which means that you cannot change the data by opening the recordset as read-only, in which case an error occurs using the AddNew method. adlockpessimistic--Conservative record Locking (article-by-article). The way to lock the record of a data source immediately when editing. At this point, other users cannot access the data. adlockoptimistic--Open Record Lock (article by article). Locks a record only when the Update method is invoked. Consider whether this attribute is the same as the lock, unlock attribute of the Application object we talked about. adlockbatchoptimistic--Open batch update. Used for mass updating of data, corresponding to the UpdateBatch square method. Incidentally, we'll mention the CursorType attribute mentioned in the previous lecture, which also has four values: adopenforwardonly--only forward cursors, default values, can only be scrolled forward in the record. This can save resources and improve performance. adopenstatic--static cursors. A static copy of a collection of records that can be used to find data or generate reports. In addition, additions, changes, or deletions made to other users are not visible. It is recommended that only these two cursors be used in ASP. adopenkeyset--keyset cursor. Keyset cursors are similar to dynamic cursors, except that they prohibit viewing of records added by other users and prohibit access to records deleted by other users, and data changes made by other users will remain visible. adopendynamic--dynamic cursors. You can see additions, changes, and deletions made by other users. Allows all types of movement in a recordset. To be sure, such an abstract description is a bit specious, or not quite clear, simply said, (1) If you only retrieve the data, you can use the default value; (2) If you use the Update method to update a piece of data, the LockType property uses adLockOptimistic to update the data in batches using the Updatabatch method, then the adlockbatchoptimistic is used. (3) If the database has write action, CursorType properties generally use adOpenKeyset is enough. What do you think? Even if it's not quite clear, it's going to work. 2. If you are not proficient in the database, it is often useful to use Rstest.movefirst to move the pointer to the first record before the output is displayed. However, if there is no data in the database, you cannot use the MoveFirst method, so use the rstest.eof attribute to determine if there is data in the database before using it. 3. The RecordCount property (gets the number of records in the Recordset) can be used only when the cursor type is set to adOpenKeyset or adOpenStatic. Ii. modifying existing data in the database Method One: Use SQL statements. For example Wuf52.asp, the program is basically similar to wuf50.asp, where only the key parts are listed. ' Part two: modifying data using the Execute method of the Connection object Dim strSQL, Rstest strSQL = "UPDATE shippers SET phone = ' (503) 555-3188 ' WHERE phone like '%99% '" Cnn.execute strSQL Modify data without insert INTO ... VALUES, but with the update ... Set statement, where clause means to change the phone number containing the string "" "(" like ","% "often used in a fuzzy query) to (503) 555-3188, and all phone numbers in the table will be changed if the condition is not set. Method Two: Use the Update method of the Recordset object. Program wuf53.asp (similar routine wuf51.asp) ' Part two: modifying 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, name, date of birth from employee WHERE Birth date = #55 -03-04#" Rstest.open strSQL, Cnn,,, adCmdText Rstest ("First name") = "Chinese" Rstest.update Analysis: 1. SQL statement, if the database is an Access database, the date is enclosed in #55-03-04#, as in this case; if it is a SQL Server database, the date is surrounded by ' 55-03-04 '. 2. Rstest.open strSQL, Cnn,,, adCmdText, because the first argument is an SQL statement, so the fifth argument is adCmdText, in fact, the fifth parameter is completely omitted, but plus it can make the script more efficient. 3. With method One, you can update all records (multiple records or one record) that match the criteria at one time, but update in method two can only modify the current record (the first record that matches the criteria). Iii. deleting data from a database Method One: Use SQL statements. Routine wuf55.asp ' Part two: deleting data using SQL statements Dim strSQL, Rstest strSQL = "DELETE from shippers WHERE call = ' 0571-7227298 '" Cnn.execute strSQL Method Two: Use the Delete method of the Recordset object. Routine wuf56.asp ' Part Two: delete data by using the Recordset object's Delete method Dim strSQL, Rstest Set rstest = server. CreateObject ("ADODB.") Recordset ") Rstest.locktype = adLockOptimistic strSQL = "SELECT * FROM shippers WHERE call = ' 0571-7227298 '" Rstest.open strSQL, Cnn,,, adCmdText While not rstest.eof Rstest.delete Rstest.movenext Wend If more than one record in the recordset meets the criteria, you must use a loop, otherwise the Delete method deletes only the current record, which is the first qualifying record. Iv. Other Useful knowledge 1. Mass Update data Above we talked about how to update the data using the Update method of the Recordset object, in fact, the Recordset object can support two types of updates: Immediate and batch update. With immediate updating, once the Update method is invoked, all changes to the data are immediately written to the current data source. With batch updates, you can enable providers to cache changes to multiple records and then use the UpdateBatch method to route them to the database in a single call. Batch updates are more efficient than immediate updates when you update multiple records. The default is immediate update mode. Use the batch update mode to use client cursors, example wuf54.asp. <% @LANGUAGE = VBScript%> <!--#include file= "adoaccess.asp"--> <!--#include file= "Adovbs.inc"--> <% ' wuf54.asp ' Part two: Batch update mode Dim strSQL, Rstest Set rstest = server. CreateObject ("ADODB.") Recordset ") Rstest.cursorlocation = adUseClient ' using Client cursor type Rstest.locktype = adLockBatchOptimistic strSQL = "SELECT * FROM shippers 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 III: Display the resulting recordset to the browser Rstest.requery Do as not rstest.eof Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> " Rstest.movenext Loop ' Part four: cleaning the battlefield Cnn.close Set rstest = Nothing:set Cnn = Nothing %> </BODY> </HTML> Attention: 1) rstest.cursorlocation = adUseClient has two values, another value is adUseServer (default), for beginners, the Recordset object cursor type is a difficult part, no longer detailed here to avoid getting confused, Please try to find out slowly in the actual processing. 2) Rstest.requery: Use the Requery method to refresh the entire contents of the Recordset object for the data source. Calling this method equals calling the close and Open methods successively. 2. Learn to use the Filter property of a Recordset object <% @LANGUAGE = VBScript%> <!--#include file= "adoaccess.asp"--> <!--#include file= "Adovbs.inc"--> <% ' wuf57.asp ' Part two: 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 "Freight Forwarders", Cnn,,, adCmdTable ' Filter out records that match the criteria, while other records are filtered out Rstest.filter = "Company name = ' Wu ' Feng '" If rstest.eof Then ' without this record, add 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 of qualifying Rstest ("telephone") = "(571) 7227298" Rstest.update End If %> <HTML> <BODY> <% ' Part III: Display the resulting recordset to the browser ' Please carefully compare the following sentence to the difference between ' rstest.filter= ' ' to clear the Filter property Rstest.movefirst Do as not rstest.eof Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> " Rstest.movenext Loop ' Part four: cleaning the battlefield Cnn.close Set rstest = Nothing:set Cnn = Nothing %> </BODY> </HTML> 3. In addition to the two methods described above, you can also use SQL statements, the Excute method of the Command object, to maintain the database. Case wuf58.asp <% @LANGUAGE = VBScript%> <!--#include file= "adoaccess.asp"--> <!--#include file= "Adovbs.inc"--> <% ' wuf58.asp ' Part two: Maintaining the database using the Excute method of SQL statements and command objects Dim strSQL, Rstest, Cmdchange strSQL = "INSERT into shippers (company name, phone number) VALUES (' Wu ' Feng ', ' 0571-7227298 ')" ' Create a Command object. Set Cmdchange =server. CreateObject ("Adodb.command") Set cmdchange.activeconnection = Cnn Cmdchange.commandtext = strSQL Cmdchange.execute %> <HTML> <BODY> <% ' Part III: Display the resulting recordset to the browser Set rstest = server. CreateObject ("ADODB.") Recordset ") Rstest.open "Shippers", Cnn,,, adCmdTable Do as not rstest.eof Response.Write rstest (0) & "" & Rstest (1) & "& Rstest (2) &" "&" <BR> " Rstest.movenext Loop ' Part four: cleaning the battlefield Cnn.close Set rstest = Nothing:set Cnn = Nothing %> </BODY> </HTML> This talk mainly introduces the maintenance of data three ways, beginners as long as the first two methods can be mastered. In general, the use of SQL statements to solve the problem, simple and straightforward, while the most benefit of using a Recordset object is that it can take advantage of its large number of attributes and rich cursor types, there are more choices, but also bring some challenges to use, the key is to explore more, more experiments. |
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
