If no record move to next valid record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

If no record move to next valid record

Author

Topic

crobson
Starting Member

5 Posts

Posted - 2011-01-10 : 11:29:31

I have inherited a database with records that have been deleted. I’m working on a table of news items. This means that there are some missing id’s where these records have been deleted. You are able to open any news story’s in the archive(1000’s) then using next and previous buttons to navigate through all of the news stories.
At present if you navigate to the next record that has been deleted, a record set end of file is thrown and a default message saying “news item no longer available” is shown.
Is there a way to detect this missing record and move to the next valid news story(a valid record with id)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:31:07

are you using sql 2005 or above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crobson
Starting Member

5 Posts

Posted - 2011-01-11 : 04:33:33

It is a ms sql server 2005, thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-11 : 05:01:09

Well...instead of specifying the ID of the next (or previous) article, why don't you specify the ID of the current article and then the action (GoNext/GoPrevious)? Then you could have a query like this:


IF @Action = 'GoNext'
  SELECT TOP 1 * FROM Articles where ID > @CurrentID ORDER BY ID
ELSE 
  SELECT TOP 1 * FROM Articles where ID < @CurrentID ORDER BY ID

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

crobson
Starting Member

5 Posts

Posted - 2011-01-11 : 06:50:16

Hi Lumbago, the @Action that is in your sql, after looking online is this a trigger? Could you give me a little more explination of how to implement this please.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-11 : 07:08:26

Nono, this is a local variable in sql server and can be whatever basically. If you want to implement this functionality using a stored procedure for example you can do like this:


CREATE PROCEDURE GetNextPreviousArticle (
  @Action varchar(30),
  @CurrentID int
  )
AS
BEGIN

IF @Action = 'GoNext'
  SELECT TOP 1 * FROM Articles where ID > @CurrentID ORDER BY ID
ELSE 
  SELECT TOP 1 * FROM Articles where ID < @CurrentID ORDER BY ID

END

Then you could execute the procedure like this from your code:

EXEC GetNextPreviousArticle 'GoNext', 32154

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 10:43:43

what value will you be pasing from page when using navigation ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crobson
Starting Member

5 Posts

Posted - 2011-01-11 : 10:53:51

I'm using asp and im requesting the page with the news item id in the querystring for both the previous and next buttons with +1 or -1 accordingly like so:

<%if intNewsid>1 then%>
<a href="news.asp?<%=intNewsid-1%>"><span>< Previous</span></a>
<%end if
if intNewsid<intLastNewsItemId then%>
<a href="news.asp?<%=intNewsid+1%>"><span>Next ></span></a>
<%end if%>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:26:41

i think problem is hardcoding value as 1. If you need to do that then you might need to generate a new column with correct sequence using ROW_NUMBER() function and then use it for searching against newsid value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

crobson
Starting Member

5 Posts

Posted - 2011-01-11 : 11:54:40

Hi, yes the hard coded +/-1 works fine when there are no deleted records and it doesn't have to be 1 so long as the code returns the next or previous record from the table. I'm trying to work out how to retrieve the next record even if the id of the record is more than 1 (if records are deleted).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:58:55

as suggested try generating a derived sequence column using ROW_NUMBER and use it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-12 : 03:11:42

visakh: what's wrong with using my method? To build a proper row_number()-query you'd have to number every single article in the archive just to get the next one (unless you fake it with something like ArticleID BETWEEN ArticleID-100 AND ArticleID+100 or something).

crobson: you can do like this ->


<%if intNewsid>1 then%>
<a href="news.asp?Newsid=<%=intNewsid%>&Action=Previous"><span>< Previous</span></a>
<%end if
if intNewsid<intLastNewsItemId then%>
<a href="news.asp?Newsid=<%=intNewsid%>&Action=Next"><span>Next ></span></a>
<%end if%>

and then something like this:


<%
NewsID = TRIM(Request.Querystring("NewsID"))
Action = TRIM(Request.Querystring("Action"))

IF Action = "Previous"
  SQL = SELECT TOP 1 * FROM table WHERE NewsID < " & NewsID & " ORDER BY NewsID DESC"
ELSEIF Action = "Next"
  SQL = SELECT TOP 1 * FROM table WHERE NewsID > " & NewsID & " ORDER BY NewsID"
END IF
%>

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 04:13:51

Damn cant wait for the Beta version of Denali.Life will be much simpler when this kind of paging issues come.

PBUH

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-12 : 04:37:19

I haven't had a chance to look at Denali at all...what will be different?

crobson: I was going to write this in my previous post but forgot about it. The code I gave you is to be regarded as an example and is not intended for production use. And the reason for this is that this code is WIDE OPEN for sql injection attacks and can potentially give hackers full control of your entire server. Here's one of many articles on how to protect yourself: http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 04:46:54

quote:
haven't had a chance to look at Denali at all...what will be different?

http://www.sqlmag.com/blogs/puzzled-by-t-sql/tabid/1023/entryid/74149/Denali-T-SQL-at-a-Glance-OFFSET-FETCH.aspx

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources