要求:
1.查询语句已经写好了,要作为参数传进去。
2.要返回总行数。
3.接收返回的数据及总行数,当前页。
4.帮忙写出asp.net 那边怎么接收数据,页面怎么跳转怎么传参?越详细越好。
5.不要求通用,符合上面条件即可。
1.查询语句已经写好了,要作为参数传进去。
2.要返回总行数。
3.接收返回的数据及总行数,当前页。
4.帮忙写出asp.net 那边怎么接收数据,页面怎么跳转怎么传参?越详细越好。
5.不要求通用,符合上面条件即可。
解决方案 »
- 在cs里怎样得到aspx里的值
- 郁闷!!!!!屏蔽ViewState并重置连接,CONNECTION_RESET
- mvc下面如何用富文本编辑器 如DotNetTextBox.dll等?
- 2009年3月12日 Thursday 怎么把Thursday换成中文
- 为sql加上一个复杂的查询条件
- 如何在外部事件中获得GridView当前行的DataKey?
- ~~~~~~~~~~~~~~以前问过,但是没有人回答的问题:关于Datalist的几个问题,请高手帮忙!!!!!!!!!!
- 如何在**.aspx.cs文件中加VBSCRIPT和asp语句?和标本完整显示?
- 关于ASP的 WorkFlow , 一些基础问题。
- 检索 COM 类工厂中 CLSID 为 {8E560CFD-2EE5-45D1-9661-0EB708BB58FA} 的组件时失败,原因是出现以下错误: 800
- 求 《ASP.NET性能高级编程》 示例代码
- 请教大哥们一个问题
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server"> protected void Page_Load(Object sender, EventArgs e)
{
int intPageNo,intPageSize,intPageCount;
intPageSize = 25;
if (Request["CurrentPage"]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request["CurrentPage"]);
}
SqlConnection mySqlConnection = new SqlConnection("server=(local);Database=test;user id=sa;password=");
SqlCommand mySqlCommand = new SqlCommand("up_GetTopicList", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter workParm;
//搜索表字段,以","号分隔
workParm = mySqlCommand.Parameters.Add("@a_TableList", SqlDbType.VarChar, 200);
mySqlCommand.Parameters["@a_TableList"].Value = "OFFERID,type,offertime";
//搜索表名
workParm = mySqlCommand.Parameters.Add("@a_TableName", SqlDbType.VarChar, 30);
mySqlCommand.Parameters["@a_TableName"].Value = "offer";
//搜索条件,如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3"
workParm = mySqlCommand.Parameters.Add("@a_SelectWhere", SqlDbType.VarChar, 500);
mySqlCommand.Parameters["@a_SelectWhere"].Value = "where type='idl'";
//表主键字段名,必须为INT类型
workParm = mySqlCommand.Parameters.Add("@a_SelectOrderId", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrderId"].Value = "offerid";
//排序,可以使用多字段排序但主键字段必需在最前面
workParm = mySqlCommand.Parameters.Add("@a_SelectOrder", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrder"].Value = "order by offerid desc";
//页号
workParm = mySqlCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageNo"].Value = intPageNo;
//每页显示数
workParm = mySqlCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageSize"].Value = intPageSize;
//总记录数(存储过程输出参数)
workParm = mySqlCommand.Parameters.Add("@RecordCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;
//当前页记录数(存储过程返回值)
workParm = mySqlCommand.Parameters.Add("RowCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.ReturnValue; mySqlConnection.Open();
Repeater.DataSource = mySqlCommand.ExecuteReader();
Repeater.DataBind();
mySqlConnection.Close();
Int32 RecordCount = (Int32)mySqlCommand.Parameters["@RecordCount"].Value;
Int32 RowCount = (Int32)mySqlCommand.Parameters["RowCount"].Value;
LabelRecord.Text = RecordCount.ToString();
LabelRow.Text = intPageNo.ToString();
intPageCount = RecordCount/intPageSize;
if ((RecordCount%intPageSize)>0)
intPageCount += 1;
LabelPage.Text = intPageCount.ToString();
if (intPageNo>1)
{
HLFistPage.NavigateUrl = "select.aspx?CurrentPage=1";
HLPrevPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = "";
HLPrevPage.NavigateUrl = "";
//HLFistPage.Enabled = false;
//HLPrevPage.Enabled = false;
}
if (intPageNo<intPageCount)
{
HLNextPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo+1);
HLEndPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageCount);
}
else
{
HLNextPage.NavigateUrl = "";
HLEndPage.NavigateUrl = "";
//HLNextPage.Enabled=false;
//HLEndPage.Enabled=false;
}
}</script>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<head>
<link href="/style.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.TP10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">A:link {
COLOR: #cc6666
}
</style>
</head>
<body>
<form runat="server">
<span class="high"> 第<font color="#CC0000"><asp:Label id="LabelRow" runat="server"/></font>页 | 共有<asp:Label id="LabelPage" runat="server"/>页
| <asp:Label id="LabelRecord" runat="server"/>条信息 |
<asp:HyperLink id="HLFistPage" Text="首页" runat="server"/>
| <asp:HyperLink id="HLPrevPage" Text="上一页" runat="server"/>
| <asp:HyperLink id="HLNextPage" Text="下一页" runat="server"/>
| <asp:HyperLink id="HLEndPage" Text="尾页" runat="server"/></span><br>
<asp:Repeater id=Repeater runat="server"> <HeaderTemplate> <table width="583" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#000000"><table width="100%" border="0" cellpadding="4" cellspacing="1" class="TP10_5">
<tr bgcolor="#999999">
<td align="center"> <strong><font color="#FFFFFF">订单号</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">服务项目</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">预订日期</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">操作人员</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">分配状态</font></strong></td>
<td> <div align="center"></div></td>
</tr>
</HeaderTemplate> <ItemTemplate> <tr align="center" bgcolor="#FFFFFF" class="small" onMouseOver='this.style.background="#CCCCCC"' onMouseOut='this.style.background="#FFFFFF"'>
<td><%# DataBinder.Eval(Container.DataItem, "offerid") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "type") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "offertime") %></td>
<td> </td>
<td> </td>
<td><a href="javascript:void(window.open('info.asp?id=<%# DataBinder.Eval(Container.DataItem, "offerid") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr> </ItemTemplate> <FooterTemplate> </table></td>
</tr>
</table> </FooterTemplate> </asp:Repeater> </form>
</body>
</html>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server"> protected void Page_Load(Object sender, EventArgs e)
{
int intPageNo,intPageSize,intPageCount;
intPageSize = 25;
if (Request["CurrentPage"]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request["CurrentPage"]);
}
SqlConnection mySqlConnection = new SqlConnection("server=(local);Database=test;user id=sa;password=");
SqlCommand mySqlCommand = new SqlCommand("up_GetTopicList", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter workParm;
//搜索表字段,以","号分隔
workParm = mySqlCommand.Parameters.Add("@a_TableList", SqlDbType.VarChar, 200);
mySqlCommand.Parameters["@a_TableList"].Value = "OFFERID,type,offertime";
//搜索表名
workParm = mySqlCommand.Parameters.Add("@a_TableName", SqlDbType.VarChar, 30);
mySqlCommand.Parameters["@a_TableName"].Value = "offer";
//搜索条件,如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3"
workParm = mySqlCommand.Parameters.Add("@a_SelectWhere", SqlDbType.VarChar, 500);
mySqlCommand.Parameters["@a_SelectWhere"].Value = "where type='idl'";
//表主键字段名,必须为INT类型
workParm = mySqlCommand.Parameters.Add("@a_SelectOrderId", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrderId"].Value = "offerid";
//排序,可以使用多字段排序但主键字段必需在最前面
workParm = mySqlCommand.Parameters.Add("@a_SelectOrder", SqlDbType.VarChar, 50);
mySqlCommand.Parameters["@a_SelectOrder"].Value = "order by offerid desc";
//页号
workParm = mySqlCommand.Parameters.Add("@a_intPageNo", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageNo"].Value = intPageNo;
//每页显示数
workParm = mySqlCommand.Parameters.Add("@a_intPageSize", SqlDbType.Int);
mySqlCommand.Parameters["@a_intPageSize"].Value = intPageSize;
//总记录数(存储过程输出参数)
workParm = mySqlCommand.Parameters.Add("@RecordCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;
//当前页记录数(存储过程返回值)
workParm = mySqlCommand.Parameters.Add("RowCount", SqlDbType.Int);
workParm.Direction = ParameterDirection.ReturnValue; mySqlConnection.Open();
Repeater.DataSource = mySqlCommand.ExecuteReader();
Repeater.DataBind();
mySqlConnection.Close();
Int32 RecordCount = (Int32)mySqlCommand.Parameters["@RecordCount"].Value;
Int32 RowCount = (Int32)mySqlCommand.Parameters["RowCount"].Value;
LabelRecord.Text = RecordCount.ToString();
LabelRow.Text = intPageNo.ToString();
intPageCount = RecordCount/intPageSize;
if ((RecordCount%intPageSize)>0)
intPageCount += 1;
LabelPage.Text = intPageCount.ToString();
if (intPageNo>1)
{
HLFistPage.NavigateUrl = "select.aspx?CurrentPage=1";
HLPrevPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = "";
HLPrevPage.NavigateUrl = "";
//HLFistPage.Enabled = false;
//HLPrevPage.Enabled = false;
}
if (intPageNo<intPageCount)
{
HLNextPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageNo+1);
HLEndPage.NavigateUrl = String.Concat("select.aspx?CurrentPage=","",intPageCount);
}
else
{
HLNextPage.NavigateUrl = "";
HLEndPage.NavigateUrl = "";
//HLNextPage.Enabled=false;
//HLEndPage.Enabled=false;
}
}</script>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<head>
<link href="/style.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.TP10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">A:link {
COLOR: #cc6666
}
</style>
</head>
<body>
<form runat="server">
<span class="high"> 第<font color="#CC0000"><asp:Label id="LabelRow" runat="server"/></font>页 | 共有<asp:Label id="LabelPage" runat="server"/>页
| <asp:Label id="LabelRecord" runat="server"/>条信息 |
<asp:HyperLink id="HLFistPage" Text="首页" runat="server"/>
| <asp:HyperLink id="HLPrevPage" Text="上一页" runat="server"/>
| <asp:HyperLink id="HLNextPage" Text="下一页" runat="server"/>
| <asp:HyperLink id="HLEndPage" Text="尾页" runat="server"/></span><br>
<asp:Repeater id=Repeater runat="server"> <HeaderTemplate> <table width="583" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#000000"><table width="100%" border="0" cellpadding="4" cellspacing="1" class="TP10_5">
<tr bgcolor="#999999">
<td align="center"> <strong><font color="#FFFFFF">订单号</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">服务项目</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">预订日期</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">操作人员</font></strong></td>
<td align="center"> <strong><font color="#FFFFFF">分配状态</font></strong></td>
<td> <div align="center"></div></td>
</tr>
</HeaderTemplate> <ItemTemplate> <tr align="center" bgcolor="#FFFFFF" class="small" onMouseOver='this.style.background="#CCCCCC"' onMouseOut='this.style.background="#FFFFFF"'>
<td><%# DataBinder.Eval(Container.DataItem, "offerid") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "type") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "offertime") %></td>
<td> </td>
<td> </td>
<td><a href="javascript:void(window.open('info.asp?id=<%# DataBinder.Eval(Container.DataItem, "offerid") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr> </ItemTemplate> <FooterTemplate> </table></td>
</tr>
</table> </FooterTemplate> </asp:Repeater> </form>
</body>
</html>
可是不会用,所以来求助了~
(
@SQL nVARCHAR(4000), --不带排序语句的SQL语句
@Page int, --页码
@RecsPerPage int, --每页容纳的记录数
@ID VARCHAR(255), --需要排序的不重复的ID号
@Sort VARCHAR(255), --排序字段及规则
@DoCount bit --非0取记录总数
)
AS DECLARE @Str nVARCHAR(4000) if(@DoCount != 0 )
begin
set @Str = 'select count(*) as RowCounts from ('+@SQL+') T'
exec (@Str)
end
else
begin
if(@Sort != '')
begin
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+')
T WHERE (T.'+@ID+') NOT IN
(SELECT TOP '+ CAST ((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM (
'+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort
end
else
begin
SET @Str='SELECT TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+')
T WHERE (T.'+@ID+') NOT IN
(SELECT TOP '+ CAST ((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM (
'+@SQL+') T9 ) '
end
--PRINT @Str
EXEC sp_ExecuteSql @Str
-- return
end
GO
下面是调用
this.NetPager1.Sql = sql;
this.NetPager1.MyDataList = "dgProject";
this.NetPager1.IDflag = "projID";
this.NetPager1.Sort = " yearnum desc";
请不要用表名和字段名还有条件做参数了。
ALTER PROCEDURE [dbo].[Components_MultiPage]
@TableName NVARCHAR(MAX),
@Fields NVARCHAR(MAX),
@Where NVARCHAR(MAX),
@OrderBy NVARCHAR(MAX),
@Groupby NVARCHAR(MAX),
@PageIndex INT,
@PageSize INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @BeginIndex INT
DECLARE @EndIndex INT
DECLARE @Sql NVARCHAR(MAX)
DECLARE @SqlCount NVARCHAR(MAX)
DECLARE @GroupbyString NVARCHAR(MAX)
IF(@Where = '') BEGIN
SET @Where = '1 = 1'
END
SELECT @BeginIndex = (@PageIndex - 1) * @PageSize
SELECT @EndIndex = @PageIndex * @PageSize
IF(@Groupby <> N'') BEGIN
SET @GroupbyString = N' GROUP BY ' + @Groupby
END ELSE BEGIN
SET @GroupbyString = N' '
END
SET @Sql = N'
BEGIN WITH TheTable AS(
SELECT ' + @Fields + ', ROW_NUMBER() OVER(ORDER BY ' + @OrderBy + ') AS RowNumber
FROM ' + @TableName + ' WHERE ' + @Where + ' ' + @GroupbyString + ')
SELECT * FROM TheTable WHERE RowNumber > ' + CONVERT(NVARCHAR(255), @BeginIndex) + ' AND RowNumber <= ' + CONVERT(NVARCHAR(255), @EndIndex) + '
END'
SET @SqlCount = N'SELECT COUNT(*) AS [Rows] FROM ' + @TableName + ' WHERE ' + @Where + ' '
-- PRINT @Sql
-- PRINT @SqlCount
EXECUTE SP_EXECUTESQL @Sql
EXECUTE SP_EXECUTESQL @SqlCount
-- DEBUG
-- EXECUTE SP_EXECUTESQL dbo.Components_MultiPage 'User_Users', 'UserId', '', 'UserId', 1, 30
END --引用分页存储过程
ALTER PROCEDURE [dbo].[Works_GetWorksByStatus]
@PageIndex int,
@PageSize int,
as
begin
DECLARE @TableName NVARCHAR(MAX)
DECLARE @Fields NVARCHAR(MAX)
DECLARE @Where NVARCHAR(MAX)
DECLARE @Orderby NVARCHAR(MAX)
DECLARE @Groupby NVARCHAR(MAX)
begin
SET @TableName = N'...'
SET @Fields=N'...'
SET @Where=N'...'
SET @Orderby=N'...'
SET @Groupby=N'...'
EXECUTE Components_MultiPage @TableName, @Fields, @Where, @Orderby, @Groupby, @PageIndex, @PageSize
END