寒,你们有没有看清楚楼主的题目。比如新闻a的id是100,则我们可以用select * top 1 from news where id<100 order by id desc 来取得上一条记录,反过来就是下一条。
CREATE PROCEDURE [dbo].[p_GetNearNewsTitle] @NewsId int, @Type varchar(20) AS DECLARE @PreviousId int, @NextId int, @PreviousTitle varchar(100) , @NextTitle varchar(100) IF(NOT EXISTS (SELECT TOP 1 [Id] FROM News WHERE [Id] > @NewsId AND MClass = @Type ORDER BY [Id])) BEGIN SET @PreviousId = 0; SET @PreviousTitle = ''; END ELSE BEGIN SELECT TOP 1 @PreviousId = [Id], @PreviousTitle = Title FROM News WHERE [Id] > @NewsId AND MClass = @Type ORDER BY [Id] END IF(NOT EXISTS (SELECT TOP 1 [Id] FROM News WHERE [Id] < @NewsId AND MClass = @Type ORDER BY [Id] DESC)) BEGIN SET @NextId = 0; SET @NextTitle = ''; END ELSE BEGIN SELECT TOP 1 @NextId = [Id], @NextTitle = Title FROM News WHERE [Id] < @NewsId AND MClass = @Type ORDER BY [Id] DESC END SELECT [Id] AS NewsId, Title, Cont AS Content, WTime AS CreateDate, MFrom AS ComeFrom, @PreviousId AS PId, @NextId AS NId, @PreviousTitle AS PTitle, @NextTitle AS NTitle FROM News WHERE [Id] = @NewsId GO
{
DateTime CurrentTime = DateTime.Now;
if (Request.QueryString["page"] == null)
{
CurrentPage = 1;
}
else
{
CurrentPage = Int16.Parse(Request.QueryString["page"]);
} SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand selectSql = conn.CreateCommand();
selectSql.CommandText = "SELECT TOP " + MaxPerPage + " * FROM Article WHERE (id NOT IN (SELECT TOP " + MaxPerPage*(CurrentPage - 1) + " id FROM Article))";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectSql;
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds, "Article");
conn.Close(); Board.DataSource = ds.Tables[0].DefaultView;
Board.DataBind(); Pagination(); TimeSpan PageDisplayTimeSpan = DateTime.Now - CurrentTime;
DisplayTimeSpan.Text = PageDisplayTimeSpan.Milliseconds.ToString();
} private void Pagination()
{
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand selectSql = conn.CreateCommand();
selectSql.CommandText = "SELECT COUNT(id) AS TatleRows FROM Article";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectSql;
DataSet ds = new DataSet();
conn.Open();
adapter.Fill(ds, "Article");
conn.Close(); string DisplayPageText;
int TableRows = Int32.Parse(ds.Tables["Article"].Rows[0][0].ToString());
int PossiblePage;
int TatlePage = Math.DivRem(TableRows, MaxPerPage, out PossiblePage);
if (PossiblePage != 0)
{
TatlePage += 1;
}
DisplayPageText = CurrentPage.ToString() + "/" + TatlePage.ToString() + " 页 "; if (Request.QueryString["page"] == null)
{
CurrentPage = 1;
}
else
{
CurrentPage = Int32.Parse(Request.QueryString["page"]);
}
if (CurrentPage == 1)
{
if (CurrentPage == TatlePage)
{
DisplayPageText += "第一页 上一页 下一页 最后页";
}
else
{
DisplayPageText += "第一页 上一页 <a href=\"default.aspx?page=" + (CurrentPage + 1) + "\">下一页</a> <a href=\"default.aspx?page=" + TatlePage + "\">最后页</a>";
}
}
else
{
if (CurrentPage == TatlePage)
{
DisplayPageText += "<a href=\"default.aspx?page=1\">第一页</a> <a href=\"default.aspx?page=" + (CurrentPage - 1) + "\">上一页</a> 下一页 最后页";
}
else
{
DisplayPageText += "<a href=\"default.aspx?page=1\">第一页</a> <a href=\"default.aspx?page=" + (CurrentPage - 1) + "\">上一页</a> <a href=\"default.aspx?page=" + (CurrentPage + 1) + "\">下一页</a> <a href=\"default.aspx?page=" + TatlePage + "\">最后页</a>";
}
} DisplayPageText += " 转到<select name=\"menu1\" onchange=\"MM_jumpMenu('parent',this,0)\" style=\"font-size:9pt\" size=\"1\">";
for (int i = 1; i <=TatlePage; i++)
{
DisplayPageText += "<option value=\"default.aspx?page=" + i + "\">" + i + "</option>";
}
DisplayPageText += "</select>页"; DisplayPage.Text = DisplayPageText; } public string AdminResponse(int id)
{
string ResponseContent;
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
SqlCommand selectSql = conn.CreateCommand();
selectSql.CommandText = "SELECT Response.Content as Content, ResponseAdmin, ResponseTime FROM Article, Response WHERE ResponseID=Response.id AND Article.id=" + id;
conn.Open();
SqlDataReader reader = selectSql.ExecuteReader();
if (reader.Read())
{
ResponseContent = "<span id=\"RedFont\">============= " + reader["ResponseAdmin"] + " 在 " + reader["ResponseTime"] + " 时回复:</span><br>" + reader["Content"];
}
else
{
ResponseContent = "";
}
reader.Close();
conn.Close();
return ResponseContent;
}
来取得上一条记录,反过来就是下一条。
@NewsId int,
@Type varchar(20)
AS
DECLARE @PreviousId int, @NextId int, @PreviousTitle varchar(100) , @NextTitle varchar(100)
IF(NOT EXISTS (SELECT TOP 1 [Id] FROM News WHERE [Id] > @NewsId AND MClass = @Type ORDER BY [Id]))
BEGIN
SET @PreviousId = 0;
SET @PreviousTitle = '';
END
ELSE
BEGIN
SELECT TOP 1 @PreviousId = [Id], @PreviousTitle = Title FROM News WHERE [Id] > @NewsId AND MClass = @Type ORDER BY [Id]
END
IF(NOT EXISTS (SELECT TOP 1 [Id] FROM News WHERE [Id] < @NewsId AND MClass = @Type ORDER BY [Id] DESC))
BEGIN
SET @NextId = 0;
SET @NextTitle = '';
END
ELSE
BEGIN
SELECT TOP 1 @NextId = [Id], @NextTitle = Title FROM News WHERE [Id] < @NewsId AND MClass = @Type ORDER BY [Id] DESC
END
SELECT [Id] AS NewsId, Title, Cont AS Content, WTime AS CreateDate, MFrom AS ComeFrom, @PreviousId AS PId,
@NextId AS NId, @PreviousTitle AS PTitle, @NextTitle AS NTitle FROM News WHERE [Id] = @NewsId
GO
另外就是怎么实现
两个办法:
第一、直接写链接,就是在当前页面中就根据当前新闻的id按照相关新闻的条件把相关新闻的链接找出来放在“上一篇”这里第二、把当前新闻的id写在“上一页”的链接参数里 到点这个链接的时候再去查找。
比如:新闻显示页面叫shownews.aspx 那么你"上一页"链接写成shownews.aspx?prenews=xxx “下一页”写成shownews.aspx?nextnews=xxx 在shownews页面里面检查参数,根据不同的参数来查找新闻