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
Previous
 
Next
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:

Statements:
 
Statment#1
 
SELECT
u.UserName,
u.FirstName,
u.LastName,
u.Email,
Numerical.PropertyValue AS Numerical,
Street.PropertyValue AS Street,
Telephone.PropertyValue As Telephone,
Committee.PropertyValue AS Committee,
Volunteer.PropertyValue as Volunteer
FROM
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,
UserID INT,
PropertyName NVARCHAR(50),
PropertyValue NVARCHAR(3750)
)
AS BEGIN
INSERT INTO @ProfileFieldTable
SELECT PPD.PortalID,
UP.UserID,
PPD.PropertyName,
UP.PropertyValue
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 )

RETURN
END

 
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.

Mark
 
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.

Mark
 
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

 

or

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.


 
Previous
 
Next
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.

Content Layout

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  

What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out