/*--调用示例
exec p_show 'select * from xzkh_new..地区资料'
exec p_show 'select * from xzkh_new..地区资料',5,2,'地区编号,地区名称,助记码','地区编号'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
set nocount on
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
set nocount off
GO
调用以上存储过程
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Password=22222;Persist Security Info=True;User ID=sa;Initial Catalog=haha;Data Source=hhfh"
cn.Open
Set rs = New ADODB.Recordset
Dim Rsd As New ADODB.Command
Set rs = cn.Execute("exec p_show 'select * from std_tab',50,1,'std,org_num','org_num'")
msgbox rs.state
在查询分析器可以显示正常结果
但是我在vb代码中用ado执行存储过程,rs.state的状态是关闭的,没打开!
我想显示记录集结果rs("std") 说rs没打开不行,怎么解决这个问题!?
/*--调用示例
exec p_show 'select * from xzkh_new..地区资料'
exec p_show 'select * from xzkh_new..地区资料',5,2,'地区编号,地区名称,助记码','地区编号'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO
CREATE Proc p_show
@QueryStr nvarchar(1000), --查询语句,如果查询表,用:select * from 表
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(50) --标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))
,@FdShow=case isnull(@FdShow,'') when '' then '*' else @FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from('+@QueryStr+') a '+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer