Ado|web four, Command object:
As you can see from the literal meaning of English, command is used to perform commands and parameter passing. The command object's bulk parameter transmission, storedprocude execution, and so on flexible and powerful function is also the reason that it is favored. The command object is primarily about passing parameters to SQL statements, Storedprocude, and relying on the powerful capabilities of SQL Server to complete the operation of the database, and the Recordset object can be said that Microsoft has newly encapsulated the data object, and provides a series of methods and properties to simplify the programming of the database. Let's look at the following example, which uses two different methods to add a new record bar to the database. From there you can see clearly the differences between the command object and the Recordset object.
Method 1 (Command)
Const ADCMDTEXT=&H0001
Const ADINTEGER=3
Const ADVARCHAR=200
Const ADPARAMINPUT = &h0001
Set Conn=server.createobject ("ADODB. Connection ")
Set Comm=server.createobject ("Adodb.command")
Conn.Open "driver={Microsoft Access Driver};D bq=" & _
Server.MapPath ("/source_asp") & "/property/employee.mdb";
Comm. Activeconnection=conn
Comm.commandtype=adcmdtext
comm.commandtext= "INSERT into employee (job_id,fri_name,last_name)" & _
& "VALUES (?,?,?)"
Set Param=comm. CreateParameter ("ID", adinteger,adparaminput,3,4)
Comm. Parameters.Append param
Set Param=comm. CreateParameter ("FN", advarchar,adparaminput,255, "Bill")
Comm. Parameters.Append param
Set Param=comm. CreateParameter ("LN", advarchar,adparaminput,255, "Gates")
Comm. Parameters.Append param
Comm. Execute
Conn.close
Method 2 (RecordSet)
Const ADCMDTABLE=&H0002
Set Conn=server.createobject ("ADODB. Connection ")
Set Rs=server.createobject ("ADODB. RecordSet ")
Conn.Open "Driver={microsoft Access Driver (*.mdb)};D bq=" & _
Server.MapPath ("/source_asp") & "/property/employee.mdb";
Rs. Activeconnection=conn
Rs.Open "Employee",,, adCmdTable
Rs.addnew
RS ("job_id") =4
RS ("fri_name") = "Bill"
RS ("last_name") = "Gates"
Rs.update
Rs.close
Conn.close
As you can see from the example above, the two objects are different ways of dealing with some problems. The Recordset object seems to be better understood because it adds elements that are not in ANSI SQL, it actually produces a recordset on the database with SQL, and then uses a cursor to point to the Recordset, which is used to traverse the recordset. But in terms of performance command performance is also relatively superior. Its application is also very good. And if you're adding records in batches, you can also appreciate the benefits of the first option, because the command object is to process the recordset generated by the SQL as a whole. The properties, methods, and collections of the Command object are described in detail below.
1, CreateParameter method: Used to produce a parameter object, commonly used as set Param=comm. CreateParameter (Name,type,direction,size,value), where name is the reference name of the parameter, which is useful when referring to the value of the parameter, or type, for the specified parameter, for example, an integer of Adinteger direction Specifies whether the parameter is input or output, and the corresponding value is adParamInput and adparamoutput;size the maximum length or maximum value of the specified parameter; value specifies the values of the parameters. Each option can be written separately, and the following two ways are equivalent:
Set param= Comm. CreateParameter (Name,type,direction,size,value)
And
Set param= Comm. CreateParameter (Name,type,direction,size)
Param.value=value
The following approach has greater flexibility. Note that the CreateParameter method is used only to create a new parameter object, and it is also necessary to pass the parameter to the command object using the Append method of the Parameter object.
2. Execute method: After specifying the CommandText and passing the parameters out, execute with the Execute method.
3, ActiveConnection properties: To specify the connection to the connection object, one of the tricks here is that different command objects point to the same connection connection.
4, CommandText property: Its value can be a SQL command sentence, can be a table name, can also be a storedprocedure name.
5, CommandType property: its value by the CommandText corresponding value of the given, respectively, Adcmdtext,adcmdtable,adcmdstoredproc. The same meaning as the corresponding option in the previous Execute method for the Connection object.
6, CommandTimeout property: Set the value of the timeout for command execution.
7, properties set: We do not speak more, and connection objects of the property collection is not much different.
8, Parameters Collection: That is, the collection of Parameter objects, he has the main Item method, append method, and the Count property, usage and the Property object and the Error object of the corresponding properties and methods, the following gives an example:
Const ADCMDTEXT=&H0001
Const ADINTEGER=3
Const ADVARCHAR=200
Const adParamInput = &h0001
Set Conn=server.createobject ("ADODB. Connection ")
Set Comm=server.createobject ("Adodb.command")
Conn.Open "Driver={microsoft Access Driver (*.mdb)};D bq=" & _
Server.MapPath ("/source_asp") & "/property/employee.mdb";
Comm. Activeconnection=conn
Comm.commandtype=adcmdtext
Comm.commandtext= "Insert into employee (job_id,fri_name,last_name)" & _
"Values (?,?,?);"
Set Param=comm. CreateParameter ("ID", adinteger,adparaminput,3)
Param.value=14
Comm. Parameters.Append param
Set Param=comm. CreateParameter ("FN", advarchar,adparaminput,255, "Bill")
Comm. Parameters.Append param
Set Param=comm. CreateParameter ("LN", advarchar,adparaminput,255, "Gates")
Comm. Parameters.Append param
Comm. Execute
Conn.close
"The folowing statments show the value of Parametrs
Dim i
For I=0 to Comm.parameters.count-1
Response.Write Comm.parameters.item (i) & "<br>"
Next
Of course, we can also use a reference to a parameter without a number, using the name defined in the previous createparameter, such as FN, ID, and so on. In addition, we can change the display section of the above program to
Dim key
For each key in Comm.parameters
Response.Write key& "<br>"
Next
Here I would like to focus on the StoredProcedure, it is strong enough to let us pay attention to it, of course, this will involve some of the knowledge of SQL Server
