richard summer
wading in the water Posts:20
|
03-01-2010 12:19 AM |
|
I am on DNN 4.9.3 and DUD3.0.
When I first loaded DUD on a page and tried to initialize the module, it will display the error messages showed at the bottom below the "Save" link and above the "Exit to Control Panel" link. I checked my site and found no custom user profile fields defined. Yet this is a child portal and I have total two portals on my local test environment. The other test portal only has 3 custom user profile fields. Currently there are only 1 user on this portal. Please help! Thanks!
DotNetNuke.Services.Exceptions.ModuleLoadException: Parameter count does not match Parameter Value count. ---> System.ArgumentException: Parameter count does not match Parameter Value count. at Microsoft.ApplicationBlocks.Data.SqlHelper.AssignParameterValues(SqlParameter[] commandParameters, Object[] parameterValues) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at DataSprings.DNN.Modules.DynamicUserDirectory.Data.SqlDataProvider.DataSprings_RetrieveDNNUserProfileData(Int32 PortalID, String RoleFilter, String LimitResults, Int32 AuthOnly, String strUserFilter) at DataSprings.DNN.Modules.DynamicUserDirectory.Business.DynamicUserDirectoryController.DataSprings_RetrieveDNNUserProfileData(Int32 PortalID, String RoleFilter, String LimitResults, Int32 AuthOnly, String strUserFilter) at DataSprings.DNN.Modules.DynamicUserDirectory.Common.GetDotNetNukeUserFields(Int32 PortalID, Int32 TabId, Int32 ModuleID, String strUserFilter, Boolean blnTop1) at DataSprings.DNN.Modules.DynamicUserDirectory.Settings.BindDotNetNukeFields() at DataSprings.DNN.Modules.DynamicUserDirectory.Settings.LoadMySettings() |
|
|
|
|
David To
river guide Posts:2719
|
03-01-2010 06:40 AM |
|
HI, what SQL version are you running and what is the compatibility mode of your SQL DNN database? -- DAvid |
|
|
|
|
richard summer
wading in the water Posts:20
|
03-01-2010 09:20 PM |
|
I am running on SQL server 2005 of a clean install with compatility level 90. I also tried to change the compatibility level to 80 and the problem remains. |
|
|
|
|
David To
river guide Posts:2719
|
03-02-2010 06:48 AM |
|
Hi, please reset compatibility mode back to 90. Then go to Host / SQL and type in the following statement: alter Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table {databaseOwner}tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table {databaseOwner}tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into {databaseOwner}tempDUDProfile from {databaseOwner}[{objectQualifier}Users] U inner join {databaseOwner}[{objectQualifier}userprofile] UP on U.userid = UP.userid inner join {databaseOwner}[{objectQualifier}profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from {databaseOwner}[{objectQualifier}UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') and exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}users] where issuperuser = 1 and isdeleted = 1) AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = ' + cast(@PortalID as varchar(3)) + ')' EXECUTE(@MySQL) END else if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') and not exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = ' + cast(@PortalID as varchar(3)) + ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) +'' EXECUTE(@MySQL) END --PRINT(@MySQL) END END Then after you execute this, go to Host / Host Settings and restart the application. See if this will clear your issue and get back with me. Thanks. -- David |
|
|
|
|
richard summer
wading in the water Posts:20
|
03-03-2010 06:26 PM |
|
I tried to run the script with success in Host SQL. However, this time the problem is as following: DotNetNuke.Services.Exceptions.ModuleLoadException: Invalid Object Name 'dbo.tempDUDPivot' ---> System.Data.SqlClient.SqlException: 对象名 'dbo.tempDUDPivot' 无效。 I checked the script in SQL Query and found that the stored procedure [DataSprings_RetrieveDNNUserProfileData3a] does NOT exist in my environment. I reinstalled DUD3.0 and still same. In my environment, only [DataSprings_RetrieveDNNUserProfileData] and [DataSprings_RetrieveDNNUserProfileData3] exists. Please help! |
|
|
|
|
David To
river guide Posts:2719
|
03-04-2010 06:25 AM |
|
Hi, can you go to host / sql and execute this as well: CREATE Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3a] AS BEGIN DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns + ',[' + cast(propertyname as varchar) + ']', '[' + cast(propertyname as varchar)+ ']') FROM {databaseOwner}tempDUDProfile GROUP BY propertyname DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO {databaseOwner}tempDUDPIVOT FROM {databaseOwner}tempDUDProfile PIVOT ( MAX(propertyvalue) FOR [propertyname] IN (' + @columns + ') ) AS p ' EXECUTE(@query) END Then do a Host / Host Settings Restart Application. -- David |
|
|
|
|
richard summer
wading in the water Posts:20
|
03-04-2010 07:26 PM |
|
Thanks! Now the problem is solved. |
|
|
|
|
richard summer
wading in the water Posts:20
|
03-07-2010 12:57 AM |
|
Sorry to have to trouble you again. I succeeded when I did so in my local environment. However, when I ran the above 2 procedures again in my production environment and restarted, I still get the following error message: DotNetNuke.Services.Exceptions.ModuleLoadException: Invalid Object Name 'dbo.tempDUDPivot' ---> System.Data.SqlClient.SqlException: 对象名 'dbo.tempDUDPivot' 无效。 What should I do then? |
|
|
|
|
David To
river guide Posts:2719
|
03-08-2010 05:51 AM |
|
HI, can you restart the Dynamic User Directory Scheduler? You can go to Host / Schedule, edit it and change the time from 60 minutes to 45 minutes and then update and it should click. Then you should be able to click on the "history" of it and it should verify that the "tempDUDprofile" and "tempDUDpivot" tables have been created successfully. See if this works out and if it doesn't, is this a site we can have host access to? -- David |
|
|
|
|
richard summer
wading in the water Posts:20
|
03-08-2010 07:58 PM |
|
Problem Solved. Thanks a lot! |
|
|
|
|