如下为ADO Command对象的Excute方法描述，其中第一个参数RecordsAffected就是返回的记录数 如 Set rs = cmd.Execute( RecordsAffected) 如果返回几条那么传入的参数RecordsAffected执行后就变为几 要注意的是rs的CursorLocation要设为rs.CursorLocation=adUseClient，以避免返回值是-1。_______________________________________________________________ Execute Method (ADO Command) Executes the query, SQL statement, or stored procedure specified in the CommandText property. SyntaxFor a row-returning Command:Set recordset = command.Execute( RecordsAffected, Parameters, Options )For a non–row-returning Command:command.Execute RecordsAffected, Parameters, OptionsReturn ValueReturns a Recordset object reference.ParametersRecordsAffected Optional. A Long variable to which the provider returns the number of records that the operation affected. The RecordsAffected parameter applies only for action queries or stored procedures. RecordsAffected does not return the number of records returned by a result-returning query or stored procedure. For this information, use the RecordCount property.Parameters Optional. A Variant array of parameter values passed with an SQL statement. (Output parameters will not return correct values when passed in this argument.)Options Optional. A Long value that indicates how the provider should evaluate the CommandText property of the Command object. Can be any of the following:Constant Description adCmdText Indicates that the provider should evaluate CommandText as a textual definition of a command, such as an SQL statement. adCmdTable Indicates that ADO should generate an SQL query to return all rows from the table named in CommandText. adCmdTableDirect Indicates that the provider should return all rows from the table named in CommandText. adCmdStoredProc Indicates that the provider should evaluate CommandText as a stored procedure. adCmdUnknown Indicates that the type of command in CommandText is not known. adAsyncExecute Indicates that the command should execute asynchronously. adAsyncFetch Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be fetched asynchronously. See the CommandType property for a more detailed explanation of the first four constants in this list.ResUsing the Execute method on a Command object executes the query specified in the CommandText property of the object. If the CommandText property specifies a row-returning query, any results the execution generates are stored in a new Recordset object. If the command is not a row-returning query, the provider returns a closed Recordset object. Some application languages allow you to ignore this return value if no Recordset is desired.If the query has parameters, the current values for the Command object's parameters are used unless you override these with parameter values passed with the Execute call. You can override a subset of the parameters by omitting new values for some of the parameters when calling the Execute method. The order in which you specify the parameters is the same order in which the method passes them. For example, if there were four (or more) parameters and you wanted to pass new values for only the first and fourth parameters, you would pass Array(var1,,,var4) as the Parameters argument.Note Output parameters will not return correct values when passed in the Parameters argument.An ExecuteComplete event will be issued when this operation concludes.
'引用ADO(Microsoft ActiveX Data Objects 2.X Library) Private Sub Command1_Click() On Error GoTo err Dim cn As New ADODB.Connection, cmd As New ADODB.Command,affectedNum As Long cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登陆用户名;Password=登录密码;Initial Catalog=数据库名;Data Source=yourSERVICE" cn.Open rs.CursorLocation=adUseClient'设置客户端游标 cmd.ActiveConnection = cn cmd.CommandText="update 表1 set 字段1=1 where 字段1=2" Set rs = cmd.Execute(affectedNum) MsgBox affectedNum '显示影响的行数 Exit Sub err: MsgBox err.Description End Sub
Private Sub Command1_Click() Dim a As Integer Dim conn As ADODB.Connection Set conn = New ADODB.Connection Dim sql As String conn.Open connstring sql = "insert tbname SELECT columenname='aaaa'" conn.Execute sql, a MsgBox a
如
Set rs = cmd.Execute( RecordsAffected)
如果返回几条那么传入的参数RecordsAffected执行后就变为几
要注意的是rs的CursorLocation要设为rs.CursorLocation=adUseClient，以避免返回值是-1。_______________________________________________________________
Execute Method (ADO Command)
Executes the query, SQL statement, or stored procedure specified in the CommandText property.
SyntaxFor a row-returning Command:Set recordset = command.Execute( RecordsAffected, Parameters, Options )For a non–row-returning Command:command.Execute RecordsAffected, Parameters, OptionsReturn ValueReturns a Recordset object reference.ParametersRecordsAffected Optional. A Long variable to which the provider returns the number of records that the operation affected. The RecordsAffected parameter applies only for action queries or stored procedures. RecordsAffected does not return the number of records returned by a result-returning query or stored procedure. For this information, use the RecordCount property.Parameters Optional. A Variant array of parameter values passed with an SQL statement. (Output parameters will not return correct values when passed in this argument.)Options Optional. A Long value that indicates how the provider should evaluate the CommandText property of the Command object. Can be any of the following:Constant Description
adCmdText Indicates that the provider should evaluate CommandText as a textual definition of a command, such as an SQL statement.
adCmdTable Indicates that ADO should generate an SQL query to return all rows from the table named in CommandText.
adCmdTableDirect Indicates that the provider should return all rows from the table named in CommandText.
adCmdStoredProc Indicates that the provider should evaluate CommandText as a stored procedure.
adCmdUnknown Indicates that the type of command in CommandText is not known.
adAsyncExecute Indicates that the command should execute asynchronously.
adAsyncFetch Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be fetched asynchronously.
See the CommandType property for a more detailed explanation of the first four constants in this list.ResUsing the Execute method on a Command object executes the query specified in the CommandText property of the object. If the CommandText property specifies a row-returning query, any results the execution generates are stored in a new Recordset object. If the command is not a row-returning query, the provider returns a closed Recordset object. Some application languages allow you to ignore this return value if no Recordset is desired.If the query has parameters, the current values for the Command object's parameters are used unless you override these with parameter values passed with the Execute call. You can override a subset of the parameters by omitting new values for some of the parameters when calling the Execute method. The order in which you specify the parameters is the same order in which the method passes them. For example, if there were four (or more) parameters and you wanted to pass new values for only the first and fourth parameters, you would pass Array(var1,,,var4) as the Parameters argument.Note Output parameters will not return correct values when passed in the Parameters argument.An ExecuteComplete event will be issued when this operation concludes.
'引用ADO(Microsoft ActiveX Data Objects 2.X Library)
Private Sub Command1_Click()
On Error GoTo err
Dim cn As New ADODB.Connection, cmd As New ADODB.Command,affectedNum As Long
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=登陆用户名;Password=登录密码;Initial Catalog=数据库名;Data Source=yourSERVICE"
cn.Open
rs.CursorLocation=adUseClient'设置客户端游标
cmd.ActiveConnection = cn
cmd.CommandText="update 表1 set 字段1=1 where 字段1=2"
Set rs = cmd.Execute(affectedNum)
MsgBox affectedNum '显示影响的行数
Exit Sub
err:
MsgBox err.Description
End Sub
Dim a As Integer
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim sql As String
conn.Open connstring
sql = "insert tbname SELECT columenname='aaaa'"
conn.Execute sql, a
MsgBox a
End Sub
a就是影响的个数我的测试成功了