Products

Solutions

Resources

Partners

Community

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

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

HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...Adding to SQL StatementAdding to SQL Statement
Previous
 
Next
New Post
1/11/2012 3:33 PM
 

Hi All,

 

Awhile ago I got some help on writing an SQL statement that would allow me to create an address book from my database. Please keep in mind I know absolutely  nothing about doing this :)

 

It is working great but I'd like to add the date that users created the account. I know this is stored in dbo.UserPortals as CreatedDate. I've just had no success adding to my SQL address book.

 

Here is the statement as it is working right now:

 SELECT TOP (100) PERCENT dbo.Users.LastName AS [Last Name],

                         dbo.Users.FirstName AS [First Name],

                         dbo.Users.Username AS [Username],

                         Address.PropertyValue AS Address,

                         City.PropertyValue AS City,

                         State.PropertyValue AS State,

                         Zip.PropertyValue AS Zip,

                         Phone.PropertyValue AS [Phone Num],

                         dbo.Users.Email

FROM                     dbo.Users

INNER JOIN               dbo.UserProfile AS Address

ON                       dbo.Users.UserID = Address.UserID

AND                      Address.PropertyDefinitionID = '26'

LEFT OUTER JOIN          dbo.UserProfile AS City

ON                       dbo.Users.UserID = City.UserID

AND                      City.PropertyDefinitionID = '27'

LEFT OUTER JOIN          dbo.UserProfile AS State

ON                       dbo.Users.UserID = State.UserID

AND                      State.PropertyDefinitionID = '28'

LEFT OUTER JOIN          dbo.UserProfile AS Zip

ON                       dbo.Users.UserID = Zip.UserID

AND                      Zip.PropertyDefinitionID = '30'

LEFT OUTER JOIN          dbo.UserProfile AS Phone

ON                       dbo.Users.UserID = Phone.UserID

AND                      Phone.PropertyDefinitionID = '31'

 

Any tips on how to add CreatedDate as the last column of my "spreadsheet"? I am using the AdvancedDataGrid module if that helps.

 

Thank you in advance for your help!!

 

Mike

 
New Post
1/13/2012 10:38 AM
 

actually, the date the user was created is in the users table, which as it is already in your query is simple to add e.g.

SELECT

TOP (100) PERCENT Users.LastName AS [Last Name], Users.FirstName AS [First Name], Users.Username, Address.PropertyValue AS Address,

City

.PropertyValue AS City, State.PropertyValue AS State, Zip.PropertyValue AS Zip, Phone.PropertyValue AS [Phone Num], Users.Email, Users.CreatedOnDate

FROM

Users INNER JOIN

UserProfile

AS Address ON Users.UserID = Address.UserID AND Address.PropertyDefinitionID = '26' LEFT OUTER JOIN

UserProfile

AS City ON Users.UserID = City.UserID AND City.PropertyDefinitionID = '27' LEFT OUTER JOIN

UserProfile

AS State ON Users.UserID = State.UserID AND State.PropertyDefinitionID = '28' LEFT OUTER JOIN

UserProfile

AS Zip ON Users.UserID = Zip.UserID AND Zip.PropertyDefinitionID = '30' LEFT OUTER JOIN

UserProfile

AS Phone ON Users.UserID = Phone.UserID AND Phone.PropertyDefinitionID = '31'

The date in the UserPortals table is when that user was associated with that portal e.g. in a multi-portal installation a user can be associated with multiple portals by registering with the same username/password combination (or alternatively using the SiteGroups enhancement in the PE/EE editions). Obviously I could register for someportal.com today and for someotherportal.com (both being parent portals) later this month, so if you want those values you will need to join to the UserPortals table.The query would look like this:

SELECT

TOP (100) PERCENT Users.LastName AS [Last Name], Users.FirstName AS [First Name], Users.Username, Address.PropertyValue AS Address,

City

.PropertyValue AS City, State.PropertyValue AS State, Zip.PropertyValue AS Zip, Phone.PropertyValue AS [Phone Num], Users.Email, Users.CreatedOnDate,

UserPortals

.CreatedDate

FROM

Users INNER JOIN

UserProfile

AS Address ON Users.UserID = Address.UserID AND Address.PropertyDefinitionID = '26' INNER JOIN

UserPortals

ON Users.UserID = UserPortals.UserId LEFT OUTER JOIN

UserProfile

AS City ON Users.UserID = City.UserID AND City.PropertyDefinitionID = '27' LEFT OUTER JOIN

UserProfile

AS State ON Users.UserID = State.UserID AND State.PropertyDefinitionID = '28' LEFT OUTER JOIN

UserProfile

AS Zip ON Users.UserID = Zip.UserID AND Zip.PropertyDefinitionID = '30' LEFT OUTER JOIN

UserProfile

However you should be aware that your first query is portal-agnostic i.e. it is working against all the user records and not filtered by the portalID. As such the final query I've given you will give a row for each portal the user is a member of which the createdate of each. If you want to filter for your portal you'll have to add a where clause such as "where UserPortals.PortalID=@portalid"

AS Phone ON Users.UserID = Phone.UserID AND Phone.PropertyDefinitionID = '31'


Buy the new Professional DNN7: Open Source .NET CMS Platform book Amazon US
 
New Post
1/17/2012 4:25 PM
 

Cathal thanks for the quick reply. I had a chance to try it today and unfortunately I got an error and it won't work?? I'll post the error as it means nothing to me, because I don't understand this stuff hehe.

 

Error: System.Data.OleDb.OleDbException: Invalid column name 'CreatedOnDate'. at Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) at EfficionConsulting.AdvancedDataGrid.AdvancedDataGrid.BindData()
CommandText: SELECT TOP (100) PERCENT Users.LastName AS [Last Name], Users.FirstName AS [First Name], Users.Username, Address.PropertyValue AS Address, City .PropertyValue AS City, State.PropertyValue AS State, Zip.PropertyValue AS Zip, Phone.PropertyValue AS [Phone Num], Users.Email, Users.CreatedOnDate FROM Users INNER JOIN UserProfile AS Address ON Users.UserID = Address.UserID AND Address.PropertyDefinitionID = '26' LEFT OUTER JOIN UserProfile AS City ON Users.UserID = City.UserID AND City.PropertyDefinitionID = '27' LEFT OUTER JOIN UserProfile AS State ON Users.UserID = State.UserID AND State.PropertyDefinitionID = '28' LEFT OUTER JOIN UserProfile AS Zip ON Users.UserID = Zip.UserID AND Zip.PropertyDefinitionID = '30' LEFT OUTER JOIN UserProfile AS Phone ON Users.UserID = Phone.UserID AND Phone.PropertyDefinitionID = '31'

 

Looks like it is saying there is no such column?? The only place I could find that column was where I stated in my first post. I am on DNN 04.09.04 if that matters?

 

Any thoughts?

 

Mike

 
New Post
1/18/2012 2:44 PM
 

The table Users in DNN 4.9.4 does not have the CreatedDate. Since you know the column is from another tabale UserPortals, you can add a LEFT JOIN to your query's end and add the column to your select list:

 

SELECT ...

,UserPortals.CreatedDate

FROM Users

......

LEFT JOIN dbo.UserPortals on UserPortals.UserId =Users.UserID

 
Previous
 
Next
HomeHomeDevelopment and...Development and...SQL and SQL Ser...SQL and SQL Ser...Adding to SQL StatementAdding to SQL Statement


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.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out