很多方法都试过了,rs为null,TotalCount却是能够正确返回值.存储过程是没有问题的,查询分析器,.net调用也没有问题,但用jsp就不知怎么搞了?麻烦各位大哥帮帮忙!
==========================================================
cStmt = objConn.prepareCall("{? = call Sp_DataPage(?,?,?,?,?,?,?,?)}");
cStmt.setString(2,TableName);
cStmt.setString(3,IDField);
cStmt.setString(4,Field);
cStmt.setString(5,SearchCondition);
cStmt.setString(6,Od);
cStmt.setInt(7,ePageCount);
cStmt.setInt(8,CurPage);
cStmt.registerOutParameter(9,Types.INTEGER);
cStmt.execute();
ResultSet rs = (ResultSet)cStmt.getObject(1);
int TotalCount = cStmt.getInt(9);
==========================================================
cStmt = objConn.prepareCall("{? = call Sp_DataPage(?,?,?,?,?,?,?,?)}");
cStmt.setString(2,TableName);
cStmt.setString(3,IDField);
cStmt.setString(4,Field);
cStmt.setString(5,SearchCondition);
cStmt.setString(6,Od);
cStmt.setInt(7,ePageCount);
cStmt.setInt(8,CurPage);
cStmt.registerOutParameter(9,Types.INTEGER);
cStmt.execute();
ResultSet rs = (ResultSet)cStmt.getObject(1);
int TotalCount = cStmt.getInt(9);
============================================================
CREATE PROCEDURE Sp_DataPage
(
@TableName VARCHAR(50), -- 表名/视图名
@IDField VARCHAR(50), -- ID字段名
@Field VARCHAR(1000), -- 字段名
@SearchCondition VARCHAR(1000),-- 查询条件
@Od VARCHAR(500), -- 排序@ePageCount NUMERIC, -- 每页记录条数
@CurPage NUMERIC, -- 当前页
@TotalCount NUMERIC OUT -- 返回分页的记录总数
)
ASDECLARE @exSQL1 nVARCHAR(2000)
DECLARE @exSQL2 nVARCHAR(2000)IF (@SearchCondition = '')
BEGIN
SET @SearchCondition = '1=1'
ENDIF (@Field = '')
BEGIN
SET @Field = '*'
ENDIF (@Od = '')
BEGIN
SET @Od = 'DESC'
END
--获取要分页的记录总数
SET @exSQL1 = 'SELECT @TCount=COUNT('+@IDField+') '
+'FROM ' + @TableName + ' '
+'WHERE ' + @SearchCondition
EXEC sp_ExecuteSql @exSQL1, N'@TCount INT OUT',@TotalCount OUT
--获取某页数据
SET @exSQL2 = 'SELECT TOP ' + CAST(@ePageCount AS VARCHAR(32)) + ' '+ @Field +' FROM ' + @TableName + ' '
+ 'WHERE ('+ @SearchCondition +') AND '+ @IDField +' NOT IN ('
+ 'SELECT TOP ' + CAST((@ePageCount*(@CurPage-1)) AS VARCHAR(32)) + ' '+ @IDField +' '
+ 'FROM '+ @TableName +' WHERE '+ @SearchCondition +' ORDER BY '+ @Od + ' ) '
+ 'ORDER BY ' + @OdEXEC sp_ExecuteSql @exSQL2
GO
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:858)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
org.apache.jsp.MyOffice.Bulletin.list_jsp._jspService(list_jsp.java:110)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
ResultSet rs = (ResultSet)cStmt.getObject(1);
has some problem. Or:
In the procedure, as a test, try to execute exSQL1 and exSQL2 directly instead of using the exec. I think there are some problem of result return after execute the sql when using the exec.