I recently updated from v3.2.2 to v3.3.7 and, since then, the query we've been using to populate an employee directory isn't working right. For some reason, all new users aren't showing up (although all existing users are, but without any of the custom dnn profile field information).
I was hoping someone could look at this query and let me know what I'm missing. All I want to do is list out the employee name, phone, fax, location, department, and title (the latter 3 being new dnn profile fields based on lists).
SELECT DISTINCT
'<b>' + dbo.GetProfileElement('FirstName',PropertyNames,PropertyValuesString) + '</b>' AS First_Name,
'<b>' + dbo.GetProfileElement('LastName',PropertyNames,PropertyValuesString) + '</b>' AS Last_Name,
'<b>' + dbo.GetProfileElement('Telephone',PropertyNames,PropertyValuesString) + '</b>' AS Telephone,
dbo.GetProfileElement('Fax',PropertyNames,PropertyValuesString) AS Fax,
dbo.GetProfileElement('Location',PropertyNames,PropertyValuesString) AS Location,
dbo.GetProfileElement('Department',PropertyNames,PropertyValuesString) AS Dept,
dbo.GetProfileElement('JobTitle',PropertyNames,PropertyValuesString) AS Title
FROM aspnet_users au
INNER JOIN aspnet_membership am ON au.userid = am.userid
INNER JOIN aspnet_profile ap ON ap.userid = am.userid
INNER JOIN aspnet_usersinroles ar ON ar.userid = am.userid
WHERE au.UserName != 'admin'
ORDER BY Last_Name
I'll be the first to admit I'm not up to speed on the new user tables and stored procedures, what they are, or how they work exactly ...I just need to fix this quickly and do the research later. We use SQLGridSelectedView to lay out the table and provide search and filter capabilities. It works, or has worked, really well for us.
Any help would be greatly appreciated.