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(with id etc)? I'm using old asp for this site, is there a way to detect this while navigating through a record set or will this type of functionality have to come from the database, maybe a trigger? Thanks for any help.
我继承了一个数据库，其中的记录已被删除。我正在写一张新闻项目表。这意味着有一些丢失的id，这些记录被删除了。您可以在归档(1000)中打开任何新闻故事，然后使用next和前面的按钮来浏览所有的新闻故事。目前，如果导航到已删除的下一个记录，将抛出一个记录集文件结尾，并显示一条默认消息，称“新闻项不再可用”。是否有一种方法可以检测这个丢失的记录并转移到下一个有效的新闻故事(id等)?我在这个站点上使用的是旧的asp，是否有一种方法可以在浏览记录集时检测到它，或者这种类型的功能必须来自数据库，也许是一个触发器?感谢任何帮助。
3 个解决方案
#1
3
SELECT TOP 1 *
FROM news
WHERE id >= @next_id
ORDER BY
id
#2
1
For SQL Server 2005 and above see MSDN.
对于SQL Server 2005和以上版本，请参阅MSDN。
You can use row_number to create a contiguous number column.
您可以使用row_number创建一个连续的数字列。
#3
0
1) Since you know that it affects front-end (your 'record set end of file') of course you can manually increment/decrement identifier and try fetching another record from DB, this seems to be the best that you can do without going into database. But this can be very uneffective if many records in a row are missing. I would advise changing code in database instead.
1)既然您知道它会影响前端(您的“记录集文件端”)，当然您可以手动增加/递减标识符，并尝试从DB中获取另一条记录，这似乎是最好的方法，而无需进入数据库。但是，如果连续丢失了许多记录，这将非常无效。我建议改为在数据库中修改代码。
Assuming you have a query like this:
假设您有这样的查询:
SELECT * FROM News WHERE Id=@Id
where @Id is identifier which you're trying to fetch. Instead you will have something like this:
@Id是要获取的标识符。相反，你会得到这样的东西:
SELECT * FROM News WHERE Id=(SELECT MIN(Id) FROM News WHERE Id>=@Id)
This will allow you to select first available record. You should use MAX instead of MIN and <= instead of >= if you will look for 'previous' news item, example above should work for next news item.
这将允许您选择first available记录。您应该使用MAX而不是MIN， <=而不是>=如果您要查找'previous'新闻项，上面的示例应该适用于下一个新闻项。
Also do not forget that with this approach you will have to increment/decrement identifiers for next/previous records based on the value that you fetched from database instead of that which you were looking for. Example: you have following identifiers in your SQL table - 1,5,12,21. You've opened news item with Id=1. 'Next' button will start looking for Id>=2 and will return record with Id=5. When you will open it then your 'Next' button should look for record with Id>=6 (not 2).
还不要忘记，使用这种方法，您必须根据从数据库中获取的值(而不是您正在查找的值)为下一个/以前的记录增加/减少标识符。示例:在SQL表1、5、12、21中有以下标识符。你已经用Id=1打开了新闻条目。“Next”按钮将开始查找Id>=2，并返回Id=5的记录。当你打开它时，你的“下一步”按钮应该查找Id>=6(不是2)的记录。
Next point is that you will have to provide not only identifier of record you are trying to fetch but also direction in which to look for. And this parameter should also be passed in http query string.
下一点是，您不仅要提供您试图获取的记录的标识符，还要提供要查找的方向。这个参数也应该在http查询字符串中传递。
Also this approach may be not very user-friendly since all pages with following urls will display the same item:
site/news.asp?Id=2&direction=next
site/news.asp?Id=3&direction=next
site/news.asp?Id=4&direction=next
site/news.asp?Id=5&direction=next
此外，这种方法可能不太友好，因为所有带有以下url的页面都将显示相同的项目:site/news.asp?下网站/ news.asp Id = 2 & = ?下网站/ news.asp Id = 3 & = ?Id = 4 & =下一个网站/ news.asp ? Id = 5 & =
2) So maybe it will be more user-friendly to determine which record will be next and previous in advance, when you are displaying current record. In this case you will have to execute query like this:
2)因此，在显示当前记录时，提前确定下一条记录和前一条记录可能会更方便用户使用。在这种情况下，您必须执行如下查询:
SELECT (SELECT MAX(Id) FROM News WHERE Id<@Id) as previousId, (SELECT MIN(Id) FROM News WHERE Id>@Id) as nextId
and then correspondingly update Urls for your 'Next'&'Previous' buttons. So if in previous example we will open news item with Id=5 then 'previous' button will navigate directly to Id=1 and 'Next' button will navigate to Id=12.
然后相应地更新你的“下一个”和“上一个”按钮的url。因此，如果在前面的示例中，我们将使用Id=5打开新闻项，那么'previous'按钮将直接导航到Id=1， 'Next'按钮将导航到Id=12。
I believe the second approach is even better in your case since it can be implemented with less changes and it also allows you implementing graying out 'Next'&'Previous' links if corresponding records are not available (you will know this by having NULL returned by the query for previousId or nextId).
我认为第二种方法在您的例子中甚至更好，因为它可以用更少的更改实现，而且如果没有相应的记录，它还允许您实现灰色的“Next”和“Previous”链接(您可以通过查询previousId或nextId返回NULL来了解这一点)。
Hope this helps :)
希望这有助于:)
#1
3
SELECT TOP 1 *
FROM news
WHERE id >= @next_id
ORDER BY
id
#2
1
For SQL Server 2005 and above see MSDN.
对于SQL Server 2005和以上版本，请参阅MSDN。
You can use row_number to create a contiguous number column.
您可以使用row_number创建一个连续的数字列。
#3
0
1) Since you know that it affects front-end (your 'record set end of file') of course you can manually increment/decrement identifier and try fetching another record from DB, this seems to be the best that you can do without going into database. But this can be very uneffective if many records in a row are missing. I would advise changing code in database instead.
1)既然您知道它会影响前端(您的“记录集文件端”)，当然您可以手动增加/递减标识符，并尝试从DB中获取另一条记录，这似乎是最好的方法，而无需进入数据库。但是，如果连续丢失了许多记录，这将非常无效。我建议改为在数据库中修改代码。
Assuming you have a query like this:
假设您有这样的查询:
SELECT * FROM News WHERE Id=@Id
where @Id is identifier which you're trying to fetch. Instead you will have something like this:
@Id是要获取的标识符。相反，你会得到这样的东西:
SELECT * FROM News WHERE Id=(SELECT MIN(Id) FROM News WHERE Id>=@Id)
This will allow you to select first available record. You should use MAX instead of MIN and <= instead of >= if you will look for 'previous' news item, example above should work for next news item.
这将允许您选择first available记录。您应该使用MAX而不是MIN， <=而不是>=如果您要查找'previous'新闻项，上面的示例应该适用于下一个新闻项。
Also do not forget that with this approach you will have to increment/decrement identifiers for next/previous records based on the value that you fetched from database instead of that which you were looking for. Example: you have following identifiers in your SQL table - 1,5,12,21. You've opened news item with Id=1. 'Next' button will start looking for Id>=2 and will return record with Id=5. When you will open it then your 'Next' button should look for record with Id>=6 (not 2).
还不要忘记，使用这种方法，您必须根据从数据库中获取的值(而不是您正在查找的值)为下一个/以前的记录增加/减少标识符。示例:在SQL表1、5、12、21中有以下标识符。你已经用Id=1打开了新闻条目。“Next”按钮将开始查找Id>=2，并返回Id=5的记录。当你打开它时，你的“下一步”按钮应该查找Id>=6(不是2)的记录。
Next point is that you will have to provide not only identifier of record you are trying to fetch but also direction in which to look for. And this parameter should also be passed in http query string.
下一点是，您不仅要提供您试图获取的记录的标识符，还要提供要查找的方向。这个参数也应该在http查询字符串中传递。
Also this approach may be not very user-friendly since all pages with following urls will display the same item:
site/news.asp?Id=2&direction=next
site/news.asp?Id=3&direction=next
site/news.asp?Id=4&direction=next
site/news.asp?Id=5&direction=next
此外，这种方法可能不太友好，因为所有带有以下url的页面都将显示相同的项目:site/news.asp?下网站/ news.asp Id = 2 & = ?下网站/ news.asp Id = 3 & = ?Id = 4 & =下一个网站/ news.asp ? Id = 5 & =
2) So maybe it will be more user-friendly to determine which record will be next and previous in advance, when you are displaying current record. In this case you will have to execute query like this:
2)因此，在显示当前记录时，提前确定下一条记录和前一条记录可能会更方便用户使用。在这种情况下，您必须执行如下查询:
SELECT (SELECT MAX(Id) FROM News WHERE Id<@Id) as previousId, (SELECT MIN(Id) FROM News WHERE Id>@Id) as nextId
and then correspondingly update Urls for your 'Next'&'Previous' buttons. So if in previous example we will open news item with Id=5 then 'previous' button will navigate directly to Id=1 and 'Next' button will navigate to Id=12.
然后相应地更新你的“下一个”和“上一个”按钮的url。因此，如果在前面的示例中，我们将使用Id=5打开新闻项，那么'previous'按钮将直接导航到Id=1， 'Next'按钮将导航到Id=12。
I believe the second approach is even better in your case since it can be implemented with less changes and it also allows you implementing graying out 'Next'&'Previous' links if corresponding records are not available (you will know this by having NULL returned by the query for previousId or nextId).
我认为第二种方法在您的例子中甚至更好，因为它可以用更少的更改实现，而且如果没有相应的记录，它还允许您实现灰色的“Next”和“Previous”链接(您可以通过查询previousId或nextId返回NULL来了解这一点)。
Hope this helps :)
希望这有助于:)