Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Editor
Jun 12, 2013 at 2:40 PM
So I converted from DNN forum to AF about 30 days ago on one site. Everything has been going smooth. No issues at all.

I get a call this morning from a user who says he just signed up, activated and logged in via tapatalk. He can see the what's new module and click on posts and that works fine. But can't see a list of forums. This is so far just for this one user.

Logged in as host admin or regular account works just fine. I can see all of the forums and browse just fine. Its just this one user.

InnerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Data.SqlClient.SqlConnection.OnError
StackTrace:
Message: System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Modules.ActiveForums.SqlDataProvider.UI_ForumView(Int32 PortalId, Int32 ModuleId, Int32 UserId, Boolean IsSuper, String ForumIds) at DotNetNuke.Modules.ActiveForums.ForumController.GetForumView(Int32 portalId, Int32 moduleId, Int32 currentUserId, Boolean isSuperUser, String forumIds) at DotNetNuke.Modules.ActiveForums.Controls.ForumView.BuildForumView(Int32 ForumTemplateId, Int32 CurrentUserId, String ThemePath)


Anyone?
Editor
Jun 16, 2013 at 8:14 PM
My amazing developer actually resolved this issue. Its very freaking complicated, and it also goes along with several other issues I've been having. One being the fact that tapatalk doesn't respect forum permissions for those who have read/view only permissions on certain forums or groups.

I will figure out how what happened happened, his fix by way of sql script and any other details once we have the whole puzzle put together. It isn't duplicate user record which I what I suspected since only one user had experienced this issue. Its a mess in sql somewhere.
Editor
Jul 21, 2013 at 5:38 AM
I mentioned that i would post what fixed this and forgot all about it. Here is what went down again. I just copied and pasted this from tapatalk coeplex discussion.


So initially the issues that I saw was 2 things.

First I have a few forums in a group that are set to announcement forums. Basically admins can post and nobody else. Only read and subscribe. What I noticed was that a few people were actually starting to reply to these. Then I noticed that the only way they could reply was via tapatalk. Not from browser.

One day shortly after I saw the above, I had a user contact me. He was previously authorized, but was under punishment and was unauthorized from the websites. He was banned when the site was 5.6.7 and DNN Forums 5.0.2 and reinstated after the upgrade to DNN 7.0.6 and AF 5.0.4 beta 4. He got back on the forum and was catching up. He saw that tapatalk was available, and downloaded. Got on the forum via tapatalk and was able to browse a little.

Within 12 hours he was not able to use tapatalk any more, and when logging on via browser, NOTHING showed up except for the table header basically. I tested this on another machine and got the same results. Tested with other users of the same permissions and was able to use the forum just fine.

Keep in mind that I have converted this over from DNN forum using Christoc's script. I don't think that its necessarily his script that is the issue, but maybe how AF interacts with the converted data? Still testing to see why this happens.


The solution ended up being the same solution to fix both problems. My developer was focusing on correcting the issue for the user but the by-product was it corrected tapatalk respecting forum permissions. Here was some of his response:

Hi Seth,

I got that issue with Peanut fixed. Once debugging environment got setup it was relatively easy to fix that trouble.

We have a table named activeforums_Forums_Tracking, and from what i know till now it is to keep track of the 'Read' and 'Not Read' status of topics. It should have only 1 row for userid and forumid combination. But something caused a duplicate row for user "peanut" and forum "Security Announcements" combination.

Due to above duplicate data one of the stored procedure named "activeforums_UI_ForumView" malfunctioned, I have made a small fix in it to cope up with such instances. i explored tapatalk and active foums code to find any instance that can create such duplicate data but didn't found anything.

While writing this I can see another issue for Peanut. Records under security announcements forum are coming twice. I'll check this after having some food, most probably by time you will wake up, you will get Peanut working fine :)



So I got another response in the morning when I woke up:

As expected this duplicate records issue is again due to that duplicate record only and this time I don't want to make some dirty fix for this as these are more of like timebombs that bite us at some unknown point of time. So I suggest to delete this record.

Below are SQL lines that we need to remove that bad data

--Query 1: Check if bad data is present, for any user and forum combination
SELECT, COUNT(*)
FROM dbo.activeforums_Forums_Tracking
group by ForumId, UserId
having COUNT(*) > 1

--Get Id of records which have bad data
SELECT TrackingId
FROM dbo.activeforums_Forums_Tracking
WHERE ForumId = <from above> AND UserId = <from above>

--Punish culprit record
delete from dbo.activeforums_Forums_Tracking WHERE TrackingId = <>

I ran this on my local system and things seem to be fine. I'll run this once you get up, so that after I run this we can quickly check that deleting this record hasn't impacted anything else. If it does I'll revert it.

Let me know, when can we go for this.



I'm hoping that this information will possibly help out someone else. It definitely did for me. We still have a few issues with some settings not sticking and I'm working on figuring out those. For instance if I turn on HTML editor and Default DNN Editor, and then go in and change this for another forum group, i come back and it doesn't stick. Its back to basic. Just haven't had time to work with my developer or forum to test this to see why it happens. I think I'll try to find this discussion over on af codeplex and post a link here.