vb源码:
Public Function GetSchoolDepartmentListBysWhere(PageSize, PageNo, PageCount, sWhere, arrList) As Boolean
'On Error Resume Next Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset
objCmd.ActiveConnection = objConn '数据库连结字符串
objCmd.CommandText = "op_GetSchoolDepartmentListBysWhere"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@PageCount", adInteger, adParamReturnValue)
objCmd.Parameters.Append objCmd.CreateParameter("@sWhereField", adVarChar, adParamInput, 3000, sWhere)
objCmd.Parameters.Append objCmd.CreateParameter("@PageNo", adInteger, adParamInput, , CInt(PageNo))
objCmd.Parameters.Append objCmd.CreateParameter("@PageSize", adInteger, adParamInput, , CInt(PageSize))
Set objRs = objCmd.Execute()
If Not objRs.EOF Then
arrList = objRs.GetRows()
GetSchoolDepartmentListBysWhere = True
Else
GetSchoolDepartmentListBysWhere = False
End If objRs.Close
Set objRs = Nothing PageCount = objCmd.Parameters("@PageCount").Value
Set objCmd = Nothing If Err.Number <> 0 Then
GetSchoolDepartmentListBysWhere = False
End If
End Function存储过程
CREATE PROCEDURE [dbo].[op_GetSchoolDepartmentListBysWhere]
@sWhereField varchar(3000),
@PageNo int,
@PageSize int
AS
/*定义局部变量*/
DECLARE @BeginID varchar(6)
DECLARE @EndID varchar(6)
DECLARE @RecordCount int
DECLARE @PageCount int
DECLARE @RowCount int
DECLARE @Sqlstr varchar(3000) create table #temp(
KSH varchar(6) NOT NULL,
KSMC varchar(60) NOT NULL,
fatherKSH varchar(6) NOT NULL
)
set @sqlstr='select KSH,KSMC,FatherKSH from SchoolDepartment '
set @Sqlstr=@sqlstr+ ' order by KSH'
set @sqlstr='insert into #temp (KSH,KSMC,FatherKSH) '+@sqlstr
execute(@sqlstr)
/*关闭计数*/
SET NOCOUNT ON
/*求纪录总数*/
SELECT @RecordCount = COUNT(*) FROM #temp
IF (@RecordCount = 0) --如果没有纪录,则返回零
RETURN 0 /*取得总页数*/
SET @PageCount = @RecordCount / @PageSize
IF @PageCount * @PageSize < @RecordCount
SET @PageCount = @PageCount + 1 /*判断页数是否正确*/
IF (@PageNo - 1) * @PageSize > @RecordCount
RETURN (-1) /*求开始rootID*/
SET @RowCount = (@PageNo - 1) * @PageSize + 1
/*限制条数*/
SET ROWCOUNT @RowCount
SELECT @BeginID = KSH FROM #temp order by KSH desc /*结束rootID*/
SET @RowCount = @PageNo * @PageSize
/*限制条数*/
SET ROWCOUNT @RowCount
SELECT @EndID = KSH FROM #temp order by KSH desc
/*恢复系统变量*/
SET ROWCOUNT 0
SET NOCOUNT OFF SELECT S.* FROM SchoolDepartment S,#temp T WHERE T.KSH=S.KSH order by S.KSH -- RETURN(@PageCount)
GO
运行时老是提示“对象关闭时,不允许操作”等错误,希望高手帮小弟一把,多谢!
Public Function GetSchoolDepartmentListBysWhere(PageSize, PageNo, PageCount, sWhere, arrList) As Boolean
'On Error Resume Next Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset
objCmd.ActiveConnection = objConn '数据库连结字符串
objCmd.CommandText = "op_GetSchoolDepartmentListBysWhere"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("@PageCount", adInteger, adParamReturnValue)
objCmd.Parameters.Append objCmd.CreateParameter("@sWhereField", adVarChar, adParamInput, 3000, sWhere)
objCmd.Parameters.Append objCmd.CreateParameter("@PageNo", adInteger, adParamInput, , CInt(PageNo))
objCmd.Parameters.Append objCmd.CreateParameter("@PageSize", adInteger, adParamInput, , CInt(PageSize))
Set objRs = objCmd.Execute()
If Not objRs.EOF Then
arrList = objRs.GetRows()
GetSchoolDepartmentListBysWhere = True
Else
GetSchoolDepartmentListBysWhere = False
End If objRs.Close
Set objRs = Nothing PageCount = objCmd.Parameters("@PageCount").Value
Set objCmd = Nothing If Err.Number <> 0 Then
GetSchoolDepartmentListBysWhere = False
End If
End Function存储过程
CREATE PROCEDURE [dbo].[op_GetSchoolDepartmentListBysWhere]
@sWhereField varchar(3000),
@PageNo int,
@PageSize int
AS
/*定义局部变量*/
DECLARE @BeginID varchar(6)
DECLARE @EndID varchar(6)
DECLARE @RecordCount int
DECLARE @PageCount int
DECLARE @RowCount int
DECLARE @Sqlstr varchar(3000) create table #temp(
KSH varchar(6) NOT NULL,
KSMC varchar(60) NOT NULL,
fatherKSH varchar(6) NOT NULL
)
set @sqlstr='select KSH,KSMC,FatherKSH from SchoolDepartment '
set @Sqlstr=@sqlstr+ ' order by KSH'
set @sqlstr='insert into #temp (KSH,KSMC,FatherKSH) '+@sqlstr
execute(@sqlstr)
/*关闭计数*/
SET NOCOUNT ON
/*求纪录总数*/
SELECT @RecordCount = COUNT(*) FROM #temp
IF (@RecordCount = 0) --如果没有纪录,则返回零
RETURN 0 /*取得总页数*/
SET @PageCount = @RecordCount / @PageSize
IF @PageCount * @PageSize < @RecordCount
SET @PageCount = @PageCount + 1 /*判断页数是否正确*/
IF (@PageNo - 1) * @PageSize > @RecordCount
RETURN (-1) /*求开始rootID*/
SET @RowCount = (@PageNo - 1) * @PageSize + 1
/*限制条数*/
SET ROWCOUNT @RowCount
SELECT @BeginID = KSH FROM #temp order by KSH desc /*结束rootID*/
SET @RowCount = @PageNo * @PageSize
/*限制条数*/
SET ROWCOUNT @RowCount
SELECT @EndID = KSH FROM #temp order by KSH desc
/*恢复系统变量*/
SET ROWCOUNT 0
SET NOCOUNT OFF SELECT S.* FROM SchoolDepartment S,#temp T WHERE T.KSH=S.KSH order by S.KSH -- RETURN(@PageCount)
GO
运行时老是提示“对象关闭时,不允许操作”等错误,希望高手帮小弟一把,多谢!
解决方案 »
- VB集散控制系统
- 100分跪求MSHFLEXGRID带区问题
- Filter不到recordset
- win98下,除了setwindowrgn,还有甚末方法使窗体透明?
- 请高手帮我检查哈时钟,并标注。我不知道错在哪
- 呜呜~我的数据库备份为什么不行?
- 用Crystal Report做报表时,希望在打印记录时,每隔一行记录底色是灰色,怎么做??
- 如何改变ListBox中某一项的字符串颜色?
- 怎样在字库中查找到一个字的位置?务必请帮忙!
- 救急!谁有vb的中英文对照资料(一万个字符)
- 请问如何建一个带有“目录树”并且有数据集合在一起的数据文件?(高分求帮助)
- richtextbox控件,如何让^V到里面的图片不能拉大或缩小?
//运行时老是提示“对象关闭时,不允许操作”等错误,希望高手帮小弟一把,多谢! 是因为你打开了数据库没有进行关闭吧!在存储过程最前面先关闭数据例:Set DataEzsb1 = Nothing
CREATE PROCEDURE [dbo].[op_GetSchoolDepartmentListBysWhere]
@sWhereField varchar(3000),
@PageNo int,
@PageSize int
AS
SELECT S.* FROM SchoolDepartment SGO
还是请各位帮忙了,谢谢
http://community.csdn.net/Expert/TopicView1.asp?id=3587201