ERRMSG: Subquery returned more than 1 value when trying to access private message attachments

Summary

Applies To

InstantForum.NET 4.1.4 & InstantForum.NET 4.1.4 SP1. This will be resolved for the next update.

Workarounds

To resolve this issue execute the following TSQL against your existing InstantForum.NET 4.1.4 database...

-----------------------------------------------------------------------------------------------

DROP PROCEDURE if_sp_SelectAttachment;
GO

CREATE  PROCEDURE [if_sp_SelectAttachment] (
@intAttachmentID int,
@intUserID int
)
AS
SET NOCOUNT ON

/* Get the forum this attachment is within */

-- local variables
DECLARE @IsPrivateMessage bit
DECLARE @intForumID int
DECLARE @intPrivateMessageID int
DECLARE @SQL nvarchar(4000)

-- determine if the requested attachment belongs to a private message
SET @IsPrivateMessage = (SELECT TOP 1 IsPrivateMessage FROM InstantForum_AttachmentsPosts WHERE AttachmentID = @intAttachmentID)

/* Returns a single attachment based on the @intAttachmentID parameter */

SELECT @SQL = 'SELECT InstantForum_Attachments.AttachmentID, InstantForum_Attachments.UserID, InstantForum_Attachments.AttachmentBLOB,
InstantForum_Attachments.[Filename], InstantForum_Attachments.[Views], InstantForum_Attachments.ContentLength, InstantForum_Attachments.ContentType
FROM InstantForum_Attachments WHERE InstantForum_Attachments.AttachmentID = ' CAST(@intAttachmentID As nvarchar(40)) ' AND EXISTS '

-- if this is a private message attachment ensure user is either the sender or receiver of the PM
IF (@IsPrivateMessage = 1)
BEGIN
    /* ensure user has permission to access PM attachment */

    -- get forum identity for private message
    SET @intPrivateMessageID = (SELECT PrivateMessageID FROM InstantForum_PrivateMessages WHERE PrivateMessageID = (SELECT TOP 1 PostID FROM InstantForum_AttachmentsPosts WHERE AttachmentID = @intAttachmentID))

    SELECT @SQL = @SQL ' (SELECT PrivateMessageID FROM InstantForum_PrivateMessages WHERE (PrivateMessageID = '    CAST(@intPrivateMessageID As nvarchar(40))
    ' AND AuthorID = ' CAST(@intUserID As nvarchar(40)) ' OR RecipientID = '   CAST(@intUserID As nvarchar(40)) ') ) '

END
ELSE
BEGIN

    /* this is a standard attachment within a forum, check the current user has permission to access the forum the attachment is within */

    -- get forum identity for attachment post
    SET @intForumID = (SELECT ForumID FROM InstantForum_Topics WHERE PostID = (SELECT PostID FROM InstantForum_AttachmentsPosts WHERE AttachmentID = @intAttachmentID))

    IF (@intUserID = 0) -- anonymous users
    BEGIN
        SELECT @SQL = @SQL ' (SELECT InstantForum_ForumsRoles.ForumRoleID FROM InstantForum_ForumsRoles WHERE
        InstantForum_ForumsRoles.ForumID =  ' CAST(@intForumID As nvarchar(40)) ' AND InstantForum_ForumsRoles.RoleID IN (SELECT InstantASP_Roles.RoleID FROM InstantASP_Roles WHERE AnonymousRole = 1))'
    END
    ELSE IF (@intUserID > 0) -- authenticated users
    BEGIN
        SELECT @SQL = @SQL ' (SELECT InstantForum_ForumsRoles.ForumRoleID FROM InstantForum_ForumsRoles WHERE
        InstantForum_ForumsRoles.ForumID =  ' CAST(@intForumID As nvarchar(40)) '  AND InstantForum_ForumsRoles.RoleID IN (SELECT InstantASP_Roles.RoleID FROM InstantASP_UsersRoles INNER JOIN
        InstantASP_Roles ON InstantASP_UsersRoles.RoleID = InstantASP_Roles.RoleID WHERE (InstantASP_UsersRoles.UserID = '   CAST(@intUserID As nVarChar(40)) ')))'
    END

END

-- ensure we use sp_executesql to cache execution plan for dynamic query don't just use EXEC
EXECUTE sp_executesql @SQL

GO

-----------------------------------------------------------------------------------------------

The TSQL file is also attached below. You will need to execute this against your existing InstantForum.NET using Enterprise Manager or SQL Management Studio. If you have any questions on how to apply this update don't hesitate to contact us or post your questions below.