Basic ASP knowledge Command object

This article mainly introduces the Command object in ASP. it is an ASP getting started tutorial. if you are interested, refer to the Coonamd object definition to execute commands on the data source, it can be used to query database tables and return a record set. It can also be used to add, change, and delete database tables.
1. steps for using the Command object:
When using the Command object to process data on the ASP page, you should first set the Command type, Command text, and related active database connections, and pass Command parameters through the Parameter object, you can call the Execute method to Execute SQL statements or call stored procedures to retrieve, add, modify, and delete database records. The procedure is as follows:
1. use the ActiveCommand attribute to set related database connections;
2. use the CommandType attribute to set the command type;
3. use the CommandText attribute to define executable texts of commands (such as SQL statements;
4. use the CommandTimeout attribute to set the command timeout time;
5. Execute the command using the Execute method.
II. attributes of the Command object:

III. Command object method ---- Execute
This method runs the query specified in the CommandText attribute. The syntax format is divided into the following two forms.
1. for the Command returned by line:
Set recordset = command. Execute (RecordsAffected, Parameters, Options)
2. for commands not returned by line:
Command. Execute RecordsAffected, Parameters, Options
The RecordsAffected parameter is the number of records affected by the Operation returned by the provider. Rarameters is the parameter value transmitted using SQL statements. Options indicates how the provider assigns a value to the CommandText attribute of the Command object.
4. use the Parameters set
A Command object has a Parameters set composed of Parameter objects. a Parameter object represents a Parameter or independent variable associated with a Command object based on parameterized queries or stored procedures. By creating a Parameter object and adding it to the Parameter set, you can pass the required data to the parameterized query. Steps for using the Parameter set are shown in the following table:
Steps for using the Parameter set

V. Application example of Command object
1. this is a simple basic information management system for employees. Its functions include: 1) Adding employee information; 2) changing employee information; 3) deleting employee information, search employee information. It contains seven pages and a database. They are:
1) homepage: index. asp
2) add data page: add.htm
3) save the add data page: add. asp
4) modify data page: Update. asp
5) save the changed data page: Update1.asp
6) delete record page: Detele. asp
7) search employee information page: shousho. asp
8) Database: RSGL. mdb. this database uses the "employee basic information table".
2. the code for each page is as follows:
1) homepage: index. asp.The page features:
A) create two objects: Connectiion object and Recordset object. the object is used to connect to the database and return a record set;
B) create a table so that the do while loop statement displays each record in the table;
C) create three superconnections. one is used to connect to the add data page, the other is used to connect to the change data page through the specified employee name, and the other is used to connect to the delete page through the specified employee name.

<% @ Language = "VBScript" %>Employee basic information management system<% '***************** Create two objects (connection object and record set object) * ******************* dim cnn, rstset cnn = Server. createObject ("ADODB. connection ") set rst = Server. createObject ("ADODB. recordset ") 'specifies the connection string, cnn. connectionString = "PROVIDER = Microsoft. jet. OLEDB.4.0; Data Source = "& server. mapPath (".. /rsgl. mdb ") cnn. opensSQL = "select * from employee basic information table" 'rst. open sSQL, cnn, 1, 1 set rst = cnn. execute (sSQL, ad1_text) %>
 
 
 <% 'Use the do while loop statement to display each record. Do while Not rst. eoft1 = rst ("employee name") t2 = rst ("Department") t3 = rst ("home address") t4 = rst ("Home Phone ") t5 = rst ("Email") tt =" 
   "Response. write ttrst. MoveNextloopcnn. CloseSet cnn = Nothing %> 
  

  
  

    Basic information table of faculty and staff 
  
  
  
 
   
 
    
Query records | add Records
 
   
  
   
 
    
Employee Name
 
    
Department
 
    
Home address
 
    
Home phone number
 
    
Email
 
    
Status
 
   
 
    
"& T1 &"
 
    
"& T2 &"
 
    
"& T3 &"
 
    
"& T4 &"
 
    
"& T5 &"
 
    
"Tt = tt &" modify | delete
 
   
 
 

2) add data page: add.htm.

This page is composed of a form. its function is to submit data to the add. asp page.

Add record

3) save the add data page: add. asp.

The page features:

The a)、uses the requestobject to obtain the value submitted from the add.htm page;

B) create three objects (connection object, record set object, and instruction object) and five parameters to execute the INSERT command by calling the parameters.

<% @ Language = "VBScript" %>Add record
 <% '***************** Create three objects (connection object, record set object, and instruction object) and the five parameters ******************** dim cnn, rst, sorted set cnn = Server. createObject ("ADODB. connection ") set rst = Server. createObject ("ADODB. recordset ") set cmd = Server. createObject ("ADODB. command ") 'specifies the connection string, cnn. connectionString = "PROVIDER = Microsoft. jet. OLEDB.4.0; Data Source = "& server. mapPath (".. /rsgl. mdb ") cnn. open 'sets the ActiveConnection attribute to associate the Command object with the opened connection set cmd. activeConnectio N = cnn 'specifies that the command text sent to the data provider is an SQL language. Cmd. commandType = ad1_textcmd. commandText = "insert into employee Basic Information Table (employee name, department, home address, home phone number, Email) values (?,?,?,?,?) "'Create five Parameter objects set PrmName = cmd. createParameter ("employee name", adVarChar, adParamInput, 10) set PrmDepartment = cmd. createParameter ("Department", adVarChar, adParamInput, 10) set PrmAddr = cmd. createParameter ("home address", adVarChar, adParamInput, 12) set PrmTel = cmd. createParameter ("Home Phone", adVarChar, adParamInput, 15) set PrmEmail = cmd. createParameter ("Email", adVarChar, adParamInput, 20) 'adds the parameter object to the Parameters set. Cmd. parameters. append prmNamecmd. parameters. append prmDepartmentCmd. parameters. append prmAddrCmd. parameters. append prmTelCmd. parameters. append prmemail' uses the form value to set the parameter value PrmName. value = Request. form ("txtName") PrmDepartment. value = Request. form ("txtDepartment") PrmAddr. value = Request. form ("txtAddr") PrmTel. value = Request. form ("txtTel") PrmEmail. value = Request. form ("txtEmail") 'execute the INSERT command cmd. execute %>
 
 
 

  
  
 
   
 
    
Employee basic information table
 
   
 
   
 
    
Employee name:
 
    
<% = PrmName. Value %>
 
   
 
   
 
    
Department:
 
    
<% = PrmDepartment. Value %>
 
   
 
   
 
    
Home address:
 
    
<% = PrmAddr. Value %>
 
   
 
   
 
    
Home Phone number:
 
    
<% = PrmTel. Value %>
 
   
 
   
 
    
Email:
 
    
<% = PrmEmail. Value %>
 
   
 
  
 
 
 
  

  

  

   
  

  
Record Added successfully!
  
Return record add form | return to home page
 

4) modify the data page: Update. asp.The page features:

A) create two objects: Connectiion object and Recordset object. the object is used to connect to the database and return a record set;

B) create a form to submit the changed data.

Cnn. connectionString = "PROVIDER = Microsoft. jet. OLEDB.4.0; Data Source = "& server. mapPath (".. /rsgl. mdb ") cnn. opensSQL = "select * from employee basic information table where employee name = '" & a & "'" 'rst. open sSQL, cnn, 1, 1 set rst = cnn. execute (sSQL, ad1_text) %>Change record

  

5) save the changed data page: Update2.asp.

The page features:

A) use the Request object to obtain the value submitted from the Update. asp page;

B) create two objects (connection object and record set object );

C) display the changed records in the table.

<% @ Language = "VBScript" %> <% '******************** extract a value from the submitted form **** * ********************** Dim Name, department, Addr, Tel, EmailName = Trim (Request. form ("txtName") Department = Trim (Request. form ("txtDepartment") Addr = Trim (Request. form ("txtAddr") Tel = Trim (Request. form ("txtTel") Email = Trim (Request. form ("txtEmail") %>Change record
 <% '***************** Create two objects (connection object and record set object) * ******************* dim cnn, rst, sorted set cnn = Server. createObject ("ADODB. connection ") set rst = Server. createObject ("ADODB. recordset ") 'specifies the connection string, cnn. connectionString = "PROVIDER = Microsoft. jet. OLEDB.4.0; Data Source = "& server. mapPath (".. /rsgl. mdb ") cnn. opensSQL = "update employee basic information table set Department = '" & Department & "', home address = '" & Addr &"', home phone number = '"& Tel &"', Email = '"& Email &" 'Where employee name =' "& name &" '"rst. open sSQL, cnn, 1, 2 set rst = nothing %>
 
 
 

  
  
 
   
 
    
Employee basic information table
 
   
 
   
 
    
Employee name:
 
    
<% = Name %>
 
   
 
   
 
    
Department:
 
    
<% = Department %>
 
   
 
   
 
    
Home address:
 
    
<% = Addr %>
 
   
 
   
 
    
Home Phone number:
 
    
<% = Tel %>
 
   
 
   
 
    
Email:
 
    
<% = Email %>
 
   
 
  
 
 
 
  

  Record changed!
  
Back to homepage
 

6) delete data page: Detele. asp.

A) use the Request object to get the name of the employee to be deleted;

B) create three objects (connection object, record set object and instruction object) and a parameter to delete the record with the value specified by the parameter;

C) a prompt box indicating successful deletion is displayed.

Change record
 <% '***************** Create three objects (connection object, record set object, and instruction object) and a parameter ******************** dim cnn, rst, sorted set cnn = Server. createObject ("ADODB. connection ") set rst = Server. createObject ("ADODB. recordset ") set cmd = Server. createObject ("ADODB. command ") 'specifies the connection string, cnn. connectionString = "PROVIDER = Microsoft. jet. OLEDB.4.0; Data Source = "& server. mapPath (".. /rsgl. mdb ") cnn. open 'sets the ActiveConnection attribute to associate the Command object with the opened connection set cmd. activeConnectio N = cnn 'specifies that the command text sent to the data provider is an SQL language. Cmd. CommandType = ad1_textcmd. CommandText = "Delete from employee basic information table where employee name =? "'Create a Parameter object set PrmName = cmd. CreateParameter (" employee name ", adVarChar, adParamInput, 10) 'to add the parameter object to the Parameters set. Cmd. Parameters. Append prmName 'use the form Value to set the parameter Value PrmName. Value = name'. Execute the Delete command cmd. Execute %>

 
 
  
Record deleted!
  
Back to homepage
 

7) search for employee information page: shousho. asp.

A) use a list box to submit the search conditions;

B) create three objects (connection object, record set object, and instruction object) and a Parameter, and assign the Value submitted in the form to the Parameter using the Value attribute of the Parameter object;

C) use the for loop statement to display each record in the retrieved record set in a table.

<% @ Language = "VBScript" %>Retrieve records using parameterization
 
Query basic information about employees in different departments
  <% '***************** Create three objects (connection object, record set object, and instruction object) and a parameter ******************** dim cnn, rst, cmd, iset cnn = Server. createObject ("ADODB. connection ") set rst = Server. createObject ("ADODB. recordset ") set cmd = Server. createObject ("ADODB. command ") 'specifies the connection string, cnn. connectionString = "PROVIDER = Microsoft. jet. OLEDB.4.0; Data Source = "& server. mapPath (".. /rsgl. mdb ") cnn. open 'create a Parameter object set PrmDepartment = cmd. createParameter ("Department", DVarChar, adParamInput, 10) 'add the Parameter object to cmd in the Prmameters set. parameters. append prmDepartment 'uses the name of the department submitted by the user as the value of the parameter object prmDepartment. value = Request. form ("department") 'specifies that the command text sent to the data provider is an SQL language. Cmd. commandType = adyuntext 'sets the ActiveConnection attribute to associate the Command object with the opened connection set cmd. activeConnection = cnn '****** if the name of the department is not submitted, or if all departments are selected, all records are displayed. otherwise, query by parameters. * *************** If PrmDepartment. value = "" or Request. form ("department") = "all" thencmd. commandText = "select * from employee basic information table" Else "uses parameterized query statements as command text cmd. commandText = "select * from employee basic information table where is the department? "End if 'sends an SQL statement to the server and returns a record Set rst = cmd. execute '----------- if the record set does not exist, a prompt message is displayed. Otherwise, the matching records are listed. ---------- If rst. EOF then %>
No matching record found!
<% Else %>
 
  <% For I = 0 to rst. Fields. Count-1%> 
    <% Next %> <% While not rst. eof %> 
   
     <% 'Use the for loop statement to list the values of each field in a record. For I = 0 to rst. Fields. Count-1 'if the field value is empty, a space is displayed if IsNull (rst (I) then %> 
    <% Else %> 
    <% End if %> <% next %> 
   <% Rst. MoveNextwend %> 
  

  
  
 
   
<% = Rst (I). Name %>
 
   
 
<% = Rst (I) %>
 
 
<% End if %>

The above is an introductory tutorial on ASP basic knowledge Command objects. I hope it will be helpful for you to learn and discuss more.