Learn More





Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Looking for help with my site's sqlLooking for help with my site's sql
New Post
5/21/2013 5:58 PM

Hi everyone and thanks for taking the time to read.  I was recently made a superuser on a dying site that needs a lot of work. I was able to figure out how to add most of the requested information that I was asked to do. I am down to two problems that I can not figure out on my own. They are:

The board incharge of the site  wants me to allow only one username per address(like how there is only one user name allowed on the site). I think this would be possible by using the site's SQl. I have made two fields unit (allowing only a 3 didget number for the numerical of the residence) and address (allowing users to select the street name from a drop down list) that are required on registration. Both fields show up under address in the users module. Does anyone know how I would go about writing this statement/string and adding it to the sql if it is even possible? I also do not have access to the web.config file and don't want to bother the company hosting the site fur us as they host it for free. If It was possible and I were able to add a stament/string to the sql, is it something I could reverse if I messed it up, if not I would contact the person hosting everything for us prior to adding it.

The second thing is I have been using the reports module. I have figured out all of the reports I need to make except for one. I have two different statements That I have not been able to figure out how to join. Both statements work by them self, but I can not seem to join them together proerly. they are as follows:

Numerical.PropertyValue AS Numerical,
Street.PropertyValue AS Street,
Telephone.PropertyValue As Telephone,
Committee.PropertyValue AS Committee,
Volunteer.PropertyValue as Volunteer
dbo.udf_UserProfileField('unit') AS Numerical left OUTER JOIN dbo.vw_Users AS U on Numerical.PortalId = U.PortalId AND Numerical.UserID = U.UserId and Numerical.PropertyValue >= ' '  join
dbo.udf_UserProfileField('street') AS Street ON U.UserId = Street.UserID AND U.PortalId = Street.PortalId  and Street.PropertyValue >= ' ' join
dbo.udf_UserProfileField('Telephone') AS Telephone ON U.UserId = Telephone.UserID AND U.PortalId = Telephone.PortalId and Telephone.PropertyValue >= ' ' join
dbo.udf_UserProfileField('Committee') AS Committee ON U.UserId = Committee.UserID AND U.PortalId = Committee.PortalId  and Committee.PropertyValue >= ' ' Join
dbo.udf_UserProfileField('Volunteer') AS Volunteer on U.UserId = Volunteer.UserID AND U.PortalId = Volunteer.PortalId
 and volunteer.PropertyValue >= ' '  
Statement #2
SELECT Authorised from
UserPortals where Authorised='true'


This may seem to juvenile stuff to a lot of you, but I haven't really messed with web design stuff since HTML. I would appreciate any help/advice that you can give to nudge me into the right direction. we are using:

DotNetNuke Community Edition

05.06.02 (144)

  and the way I figured out the long statement for user defined fields was by running this script:

CREATE FUNCTION [dbo].[udf_UserProfileField]
@PropertyName NVARCHAR(50)
RETURNS @ProfileFieldTable TABLE
PortalId INT,
PropertyName NVARCHAR(50),
PropertyValue NVARCHAR(3750)
INSERT INTO @ProfileFieldTable
FROM dbo.Users AS U
INNER JOIN dbo.UserProfile AS UP ON U.UserID = UP.UserID
INNER JOIN dbo.ProfilePropertyDefinition AS PPD ON UP.PropertyDefinitionID = PPD.PropertyDefinitionID
WHERE ( PPD.PropertyName = @PropertyName )


New Post
5/22/2013 6:59 AM
#1 - Figure out what sql is pulling back the drop down list of entries for addresses and change it to filter out whatever exists. That way people won't even see what's already been selected.

#2 - You can use a union to merge your 2 statements but they have to have the same amount of fields in the select part. Update your second statement to return the same fields as the first and use a union to merge them.

New Post
5/22/2013 9:07 PM

Mark, I have tried union statements  with no luck prior and afterwords.  I have even tried things as: select firstname, lastname from users union select  Authorised from
UserPortals where Authorised='true'. and can not get the results I need. Basically I need the table to show only those profiles where Authorised=true located in . I do not even need to show the authorised table.

As for the other question, I may have made my question confusing.  I have two user profile fields: Unit and Street.

Unit will only allow a user to place a three didget number in it ex. 123

Street lets users select their street name from a drop down list. Ex. First Street, Second Street , Third Street, Ect.

lets say user#1  is registering for my page. They set up the unit field as 123 and The Street field as First Street. Under User accounts it now shows the user's address as 123 First Street.

What I am attempting to find out is if another user comes in and attempts to make an account with the same unit and street is there a formula I can enter to check against the users fields to reject this new registration? Ex. User#2 is registering for my page. User#2 then enters 123 in the Unit Field and First Street in the Street Field and clicks submit. With the new code (if it is even possible) it would check current fields and reject user#2 stating something like "there is already a user registered with unit 123 on First Street.

New Post
5/23/2013 6:58 AM
Union - Are you sure the value in the field Authorised is "true"? Double check it. If you have records that have that column as "true", they should be coming back.

Address - I'd create a custom module for that. You can use the DNN API to add users and could probably check for an existing address prior to entering them.

New Post
5/23/2013 7:47 AM

Mark, Let me start off by thanking you for helping me with this stuff. I am assuming that the problem is probably with me since I understand just a little bit of this stuff.

SELECT Authorised from
UserPortals where Authorised='true'    returns 9 results

SELECT Authorised from
UserPortals where Authorised='false'   returns 3 results


So that part is right. To dumb it down for me, how would you use a union in the following two statements to have them show up as two columns (authorised and username)?:

SELECT Authorised from
UserPortals where Authorised='true'

select username from users


If I write: SELECT Authorised from
UserPortals where Authorised='true'  union all select username from users



SELECT Authorised from
UserPortals where Authorised='true'  union select username from users

I get the error message; There was an error executing the data source: There is an error in your SQL at line 1: Conversion failed when converting the nvarchar value 'admin' to data type bit.

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Looking for help with my site's sqlLooking for help with my site's sql

These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
Try Evoq
For Free
Start Free Trial
a Demo
See Evoq Live
Need More Information?