A definition of a command that you intend to execute against a data source.
Sets a Connection object or connection string, or returns a connection string that describes the current database connection associated with the Command object.
object.ActiveConnection
String. Sets or returns a String containing the definition for a connection or a Connection object. Default is a NULL object reference.
Read/write.
For a description of the connection string format, see the ConnectionString property.
See in Sample Script for Command, Parameter, and Parameters objects
A collection of Parameter objects that corresponds to the CommandText of a Command object.
object.Parameters
Parameters.
Read-only.
Creates a new Parameter object with the specified properties.
object.CreateParameter(
Name As String,
Type As Long,
Size As Long,
Value As Variant)
The name of the Parameter object.
The data type of the Parameter object. See the Type property for valid settings.
The maximum length for the parameter value in characters or bytes.
The value for the Parameter object.
Parameter object.
Executes the specified query, SQL statement, stored procedure, or provider-specific text.
object.Execute(
CommandText As String,
RecordsAffected As Long)
A String containing the SQL statement, table name, stored procedure, or provider-specific text to execute. Currently supports only SQL statements.
Optional. A Long variable to which the provider returns the number of records that the operation affected.
Returns a Recordset object reference if the command text specifies a row returning query else returns NULL.
This sample script retrieves the details about the department having department number 50
The sample does the following:
’ Sample script for Command Object
’ Create a command object, set the active connection
’
Option VBA
On Error Resume Next
Set CM = CreateObject("UCX:NDODB.COMMAND")
CM.ActiveConnection
="DBTYPE=0;DATABASE=ORANW81.WORLD;UID=scott;PWD=tiger"
’Create parameters,execute the sql query and display the results
’
Set PR = CM.CreateParameter("DEPTNO", adInteger, 0, 0, 50)
Set PRS = CM.Parameters
PRS.append (PR)
Set RS = CM.Execute("Select * from dept where deptno =:Deptno", lRec)
If Err <> 0 Then
GoTo errorhandler
End If
DisplayRS (RS)
RS.Close
Do While PRS.Count > 0
PRS.Delete 0
Loop
’ Create parameters and execute a query to insert a new row
’
Set PR = CM.CreateParameter(":deptno", adInteger, 0, 2, 99)
PRS.append (PR)
Set PR = CM.CreateParameter(":dname", adChar, 0, 0, "Egg")
PRS.append (PR)
Set PR = CM.CreateParameter(":loc", adChar, 0, 0, "NewYork")
PRS.append (PR)
Call DisplayParams(PRS)
CM.Execute("INSERT INTO dept VALUES (:deptno,:dname,:loc)",lRec)
If Err <> 0 Then
GoTo errorhandler
End If
’ Subroutine to display the details of a record set.
’
Sub DisplayRS(RS As Object)
Dim FLDS As Object
Dim FLD As Object
Dim RecValue As String
Dim FldCount As Long
Set FLDS = RS.Fields
RecValue = ""
For FldCount = 1 To FLDS.Count
Set FLD = FLDS.Item(FldCount)
RecValue = RecValue & " " & CStr(FLD.Name)
Next
Print RecValue
PrintLine (60)
Do
RecValue = ""
For FldCount = 1 To FLDS.Count
Set FLD = FLDS.Item(FldCount)
RecValue = RecValue & " " & CStr(FLD.Value)
Next
Print RecValue
RS.MoveNext
Loop While Not RS.EOF
End Sub
’ Subroutine to display the details of a parameters collection.
’
Sub DisplayParams(Params As Object)
Dim PRM As Object
Dim ParamCount As Long
Do While Params.HasMoreElements
Set PRM = Params.Next
Print PRM.Name & ": " & PRM.Value & ": " & PRM.Size &": " & PRM.Type
Loop
PrintLine (60)
End Sub
’Subroutine to print a line
’
Sub PrintLine(Number)
For PrintCount = 1 To Number
Line = Line & "-"
Next
Print Line
End Sub