http://support.microsoft.com/default.aspx?scid=kb;en-us;235340#top
例子中
Dim adoRs As ADODB.Recordset
Dim adoCm As ADODB.Command
Dim strSQL As String
Set adoCm = New ADODB.Command
With adoCm
Set .ActiveConnection = Conn
.CommandType = adCmdText
.CommandText = "if exists (select * from sysobjects " & _
"where id = object_id('dbo.spADOTempTest') " & _
"and sysstat & 0xf = 4) " & _
"drop procedure dbo.spADOTempTest"
.Execute
.CommandText = "Create procedure spADOTempTest " & _
"as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM TItem " & _
"SELECT * FROM #test go"
.Execute
.CommandType = adCmdStoredProc
.CommandText = "spADOTempTest"
'the default for Prepared statements is false.
'.Prepared = False
End With
Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = Conn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
'Uncomment the next line with the SQLOLEDB provider to fix the error.
'.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProc----出错:提示‘无法支持要求的属性’ MsgBox "Recordset returned...", vbOKOnly
While Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
Wend
例子中
Dim adoRs As ADODB.Recordset
Dim adoCm As ADODB.Command
Dim strSQL As String
Set adoCm = New ADODB.Command
With adoCm
Set .ActiveConnection = Conn
.CommandType = adCmdText
.CommandText = "if exists (select * from sysobjects " & _
"where id = object_id('dbo.spADOTempTest') " & _
"and sysstat & 0xf = 4) " & _
"drop procedure dbo.spADOTempTest"
.Execute
.CommandText = "Create procedure spADOTempTest " & _
"as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM TItem " & _
"SELECT * FROM #test go"
.Execute
.CommandType = adCmdStoredProc
.CommandText = "spADOTempTest"
'the default for Prepared statements is false.
'.Prepared = False
End With
Set adoRs = New ADODB.Recordset
With adoRs
Set .ActiveConnection = Conn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
'Uncomment the next line with the SQLOLEDB provider to fix the error.
'.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProc----出错:提示‘无法支持要求的属性’ MsgBox "Recordset returned...", vbOKOnly
While Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
Wend
Dim adoRs As adoDb.Recordset
Dim adoCm As adoDb.Command
Dim strSQL As StringSet adoCn = New adoDb.Connection
With adoCn
.ConnectionString = "Provider=SQLOLEDB;Server=<ServerName>;" & _
"Database=Pubs;Uid=<username>;Pwd=<strong password>"
'.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _
"Database=Pubs;Uid=<username>;Pwd=<strong password>"
.CursorLocation = adUseServer
.Open
End WithSet adoCm = New adoDb.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "if exists (select * from sysobjects " & _
"where id = object_id('dbo.spADOTempTest') " & _
"and sysstat & 0xf = 4) " & _
"drop procedure dbo.spADOTempTest"
.Execute
.CommandText = "Create procedure spADOTempTest " & _
"as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM Employee " & _
"SELECT * FROM #test go"
.Execute
.CommandType = adCmdStoredProc
.CommandText = "spADOTempTest"
'the default for Prepared statements is false.
'.Prepared = False
End WithSet adoRs = New adoDb.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
'去掉注释行
Uncomment the next line with the SQLOLEDB provider to fix the error.
.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProcMsgBox "Recordset returned...", vbOKOnlyWhile Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
WendadoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing
2. 存储过程中加set nocount on .CommandText = "Create procedure spADOTempTest " & _
"as "& _
"SET NOCOUNT ON "& _
"CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM TItem " & _
"SELECT * FROM #test go"& _
"SET NOCOUNT OFF "
Dim adoRs As adoDb.Recordset
Dim adoCm As adoDb.Command
Dim strSQL As StringSet adoCn = New adoDb.Connection
With adoCn
.ConnectionString = "Provider=SQLOLEDB;Server=<ServerName>;" & _
"Database=Pubs;Uid=<username>;Pwd=<strong password>"
'.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _
"Database=Pubs;Uid=<username>;Pwd=<strong password>"
.CursorLocation = adUseServer
.Open
End WithSet adoCm = New adoDb.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "if exists (select * from sysobjects " & _
"where id = object_id('dbo.spADOTempTest') " & _
"and sysstat & 0xf = 4) " & _
"drop procedure dbo.spADOTempTest"
.Execute
.CommandText = "Create procedure spADOTempTest " & _
"as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM Employee " & _
"SELECT * FROM #test go"
.Execute
.CommandType = adCmdStoredProc
.CommandText = "spADOTempTest"
'the default for Prepared statements is false.
'.Prepared = False
End WithSet adoRs = New adoDb.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
'去掉注释行
'Uncomment the next line with the SQLOLEDB provider to fix the error.
.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProcMsgBox "Recordset returned...", vbOKOnlyWhile Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
WendadoCn.Close
Set adoCn = Nothing
Set adoRs = Nothing
2. 存储过程中加set nocount on .CommandText = "Create procedure spADOTempTest " & _
"as "& _
"SET NOCOUNT ON "& _
"CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM TItem " & _
"SELECT * FROM #test go"& _
"SET NOCOUNT OFF "
"as "& _
"SET NOCOUNT ON "& _
"CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM TItem " & _
"SELECT * FROM #test go"& _
"SET NOCOUNT OFF "