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

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