在SQL Server2000中写了一个存储过程,先用SELECT INTO语名将多个数据表连接查询后生成一个局部临时表#T1, 然后再用Select 语句对临时表进行分组统计,想将分组统计的多行记录结果用ADO的方式返回到VB中,并在卓面表格中显示出来,
存储过程在SQL Server查询分析器中执行通过且正确。在VB中调用时出现提示: 操作类型 sql与Cursor不兼容,由于本人是初次使用VB调用存储过程,所以不知道怎么做,请各位高手指教,存储过程及调用程序该如何改写才行,谢谢!
存储过程程序如下:
USE JZSCFXDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Proc_Energy01' and type = 'P')
DROP PROCEDURE Proc_Energy01
GOCREATE PROCEDURE Proc_Energy01 (@parIn varchar(6),@OutPar CURSOR VARYING OUTPUT
)
AS
/* 用左连接创建临时表 */
SELECT b.油田ID,油田名称,a.井号,年月,日产液,日产油,含水 into #T1 FROM 井号名称表 a LEFT JOIN 油田区块名称表 b ON a.区块ID=b.区块ID LEFT JOIN 油田名称表 c ON b.油田ID=c.油田ID LEFT JOIN 产能原始表 d ON a.井号=d.井号 WHERE 年月=@parIn ORDER BY 油田名称 SET @OutPar = CURSOR /*设置游标,也是程序执行最后将要返回的结果*/
SCROLL STATIC FOR
SELECT 油田名称,count (*) as 井数,sum(日产液) 日产液,sum(日产油) as 日产油
FROM #T1 where 日产液<>0
GROUP BY 油田名称 ORDER BY 油田名称
OPEN @OutPar
FETCH FIRST FROM @OutPar
go
DECLARE @MyCursor CURSOR
EXEC Proc_Energy01 @OutPar = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
VB调用程序如下:
Private Sub Command1_Click()
Dim strConn As String '数据库连接字符串
Dim Conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim parIn As ADODB.Parameter '输入参数
Dim parOut As ADODB.Parameter
Dim rs As ADODB.Recordset
strConn = "Provider=sqloledb;Data Source=Toshiba;" & _
"Initial Catalog=JZSCFXDB;User Id=sa;Password=sa; "
Set parIn = New ADODB.Parameter
Set parOut = New ADODB.Parameter
With parIn
.Type = adVarChar '参数类型
.Size = 6 '参数长度
.Direction = adParamInput '参数方向,输入或输出
.Value = "200707"
End With
With parOut ‘以下几句可能有问题
.Type = adVariant
.Direction = adParamOutput
End With
cmd.Parameters.Append parIn '加入参数
cmd.Parameters.Append parOut '加入输出参数
Conn.CursorLocation = adUseClient '使用由本地游标库提供的客户端游标
Conn.Open strConn
Set cmd.ActiveConnection = Conn
With cmd
.CommandType = adCmdStoredProc
.CommandText = "Proc_energy01" '存储过程名
.CommandTimeout = 15
.Execute '运行存储过程
Set rs = .Execute '如果有记录集返回的写法
End With
Do While Not rs.EOF
Debug.Print rs.Fields(0)
rs.MoveNext
Loop
End Sub
存储过程在SQL Server查询分析器中执行通过且正确。在VB中调用时出现提示: 操作类型 sql与Cursor不兼容,由于本人是初次使用VB调用存储过程,所以不知道怎么做,请各位高手指教,存储过程及调用程序该如何改写才行,谢谢!
存储过程程序如下:
USE JZSCFXDB
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Proc_Energy01' and type = 'P')
DROP PROCEDURE Proc_Energy01
GOCREATE PROCEDURE Proc_Energy01 (@parIn varchar(6),@OutPar CURSOR VARYING OUTPUT
)
AS
/* 用左连接创建临时表 */
SELECT b.油田ID,油田名称,a.井号,年月,日产液,日产油,含水 into #T1 FROM 井号名称表 a LEFT JOIN 油田区块名称表 b ON a.区块ID=b.区块ID LEFT JOIN 油田名称表 c ON b.油田ID=c.油田ID LEFT JOIN 产能原始表 d ON a.井号=d.井号 WHERE 年月=@parIn ORDER BY 油田名称 SET @OutPar = CURSOR /*设置游标,也是程序执行最后将要返回的结果*/
SCROLL STATIC FOR
SELECT 油田名称,count (*) as 井数,sum(日产液) 日产液,sum(日产油) as 日产油
FROM #T1 where 日产液<>0
GROUP BY 油田名称 ORDER BY 油田名称
OPEN @OutPar
FETCH FIRST FROM @OutPar
go
DECLARE @MyCursor CURSOR
EXEC Proc_Energy01 @OutPar = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
VB调用程序如下:
Private Sub Command1_Click()
Dim strConn As String '数据库连接字符串
Dim Conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim parIn As ADODB.Parameter '输入参数
Dim parOut As ADODB.Parameter
Dim rs As ADODB.Recordset
strConn = "Provider=sqloledb;Data Source=Toshiba;" & _
"Initial Catalog=JZSCFXDB;User Id=sa;Password=sa; "
Set parIn = New ADODB.Parameter
Set parOut = New ADODB.Parameter
With parIn
.Type = adVarChar '参数类型
.Size = 6 '参数长度
.Direction = adParamInput '参数方向,输入或输出
.Value = "200707"
End With
With parOut ‘以下几句可能有问题
.Type = adVariant
.Direction = adParamOutput
End With
cmd.Parameters.Append parIn '加入参数
cmd.Parameters.Append parOut '加入输出参数
Conn.CursorLocation = adUseClient '使用由本地游标库提供的客户端游标
Conn.Open strConn
Set cmd.ActiveConnection = Conn
With cmd
.CommandType = adCmdStoredProc
.CommandText = "Proc_energy01" '存储过程名
.CommandTimeout = 15
.Execute '运行存储过程
Set rs = .Execute '如果有记录集返回的写法
End With
Do While Not rs.EOF
Debug.Print rs.Fields(0)
rs.MoveNext
Loop
End Sub
AS
BEGIN
Set NoCount On
代码
Set NoCount Off
End