I have a portal for a regional organization that receives membership data from a national organization. The data is sent in an Excel spreadsheet at the end of the month. If someone is in the spreadsheet they should be a registered user in the portal. If not, they get deleted.
Because of the condition of the data, a lot of transformation needs to occur and there are 2 dozen custom profile properties that need to be updated. We also want to use OnyakTech's Axon email functionality (we have it for another portal). So we need to create the users (or update them) every month. After we process the import, we compare people currently in the database and if they don't exist in the Excel data we delete them from the portal. Then reports are distributed to 5 people who handle membership within the organization.
I had a set of VB apps that did pieces such as import the Excel Data into SQL Server (other ways were proving troublesome), and a few stored procedures that were updating the tables. But I would prefer one app that does it all, imports the data, does the user management and then emails the reports.
I found a limited number of websites with sample code and came up with the following to add a user:
userInfo = New UserInfo
With userInfo
.PortalID = 0
.Username = sUserName
.Email = sEmail
.FirstName = sFirstName
.LastName = sLastName
.DisplayName = sDisplayName
.IsSuperUser = False
.Membership.Approved = True
.Membership.PasswordQuestion = "What is your NMRA Number (omit spaces)?"
.Membership.PasswordAnswer = drExcel("id")
.Membership.Password = sPassword
.Membership.UpdatePassword = True
oUP = New UserProfile
oUP.PreferredLocale = "en-US"
oUP.TimeZone = -300
oUP.FirstName = sFirstName
oUP.LastName = sLastName
oUP.Street = drExcel("address1").ToString().Trim()
oUP.Unit = drExcel("address2").ToString().Trim()
oUP.City = drExcel("City").ToString().Trim()
oUP.Region = drExcel("State").ToString().Trim()
oUP.PostalCode = sZipCode
oUP.Telephone = drExcel("phone").ToString().Trim()
oUP.SetProfileProperty("Suffix", drExcel("suffix").ToString().Trim())
oUP.SetProfileProperty("Cell", "")
oUP.SetProfileProperty("Fax", "")
oUP.SetProfileProperty("NMRANumber", drExcel("memtype").ToString().Trim())
oUP.SetProfileProperty("Joined", drExcel("joined").ToString().Trim())
oUP.SetProfileProperty("MemberType", GetNMRAMembershipType(drExcel("memtype").ToString().Trim()))
oUP.SetProfileProperty("Division", sDivision)
oUP.SetProfileProperty("DisplayProfile", "False")
oUP.SetProfileProperty("DisplayAddress", "False")
oUP.SetProfileProperty("DisplayPhone", "False")
oUP.SetProfileProperty("Rerail", drExcel("rerail").ToString().Trim())
oUP.SetProfileProperty("Expiration", drExcel("expires").ToString().Trim())
oUP.SetProfileProperty("Renewed", drExcel("renewed").ToString().Trim())
oUP.SetProfileProperty("County", drExcel("county").ToString().Trim())
.Profile = oUP
End With
' attempt to create the DNN user
If UserController.CreateUser(userInfo) = UserCreateStatus.AddUser Then
sSQL = "INSERT INTO SSRDNN.dbo.OnyakAXOptIns(CampaignSystemID, Email, Enabled, CreatedDate, DisabledDate, FirstName, LastName) VALUES (7, @sEmail, 1, GETDATE(), '1/1/1900', @sfName, @slname)"
oCmd = New SqlCommand(sSQL, oConn)
oCmd.Parameters.Add(New SqlParameter("@sEmail", sEmail))
oCmd.Parameters.Add(New SqlParameter("@sfName", sFirstName))
oCmd.Parameters.Add(New SqlParameter("@slname", sLastName))
oCmd.ExecuteNonQuery()
Select Case sDivision
Case "Northern"
sSQL = "INSERT INTO SSRDNN.dbo.OnyakAXOptIns(CampaignSystemID, Email, Enabled, CreatedDate, DisabledDate, FirstName, LastName) VALUES (8, @sEmail, 1, GETDATE(), '1/1/1900', @sfName, @slname)"
Case "Easetern"
sSQL = "INSERT INTO SSRDNN.dbo.OnyakAXOptIns(CampaignSystemID, Email, Enabled, CreatedDate, DisabledDate, FirstName, LastName) VALUES (9, @sEmail, 1, GETDATE(), '1/1/1900', @sfName, @slname)"
Case "Southern"
sSQL = "INSERT INTO SSRDNN.dbo.OnyakAXOptIns(CampaignSystemID, Email, Enabled, CreatedDate, DisabledDate, FirstName, LastName) VALUES (10, @sEmail, 1, GETDATE(), '1/1/1900', @sfName, @slname)"
Case "Western"
sSQL = "INSERT INTO SSRDNN.dbo.OnyakAXOptIns(CampaignSystemID, Email, Enabled, CreatedDate, DisabledDate, FirstName, LastName) VALUES (11, @sEmail, 1, GETDATE(), '1/1/1900', @sfName, @slname)"
End Select
oCmd = New SqlCommand(sSQL, oConn)
oCmd.Parameters.Add(New SqlParameter("@sEmail", sEmail))
oCmd.Parameters.Add(New SqlParameter("@sfName", sFirstName))
oCmd.Parameters.Add(New SqlParameter("@slname", sLastName))
oCmd.ExecuteNonQuery()
roleName = "Registered Users"
rc = New RoleController()
ri = rc.GetRoleByName(0, roleName)
rc.AddUserRole(0, userInfo.UserID, ri.RoleID, drExcel("expires").ToString)
roleName = "NMRA Member"
rc = New RoleController()
ri = rc.GetRoleByName(0, roleName)
rc.AddUserRole(0, userInfo.UserID, ri.RoleID, drExcel("expires").ToString)
roleName = sDivision + " Division Member"
rc = New RoleController()
ri = rc.GetRoleByName(0, roleName)
rc.AddUserRole(0, userInfo.UserID, ri.RoleID, drExcel("expires").ToString)
End If
On the line
.FirstName = sFirstName
It throws an error "Object reference not set to an instance of an object" when I step through the code but I'm able to continue to step through the code.
On the line
.LastName = sLastName
It doesn't throw an error. But in the case of this line and the line above, neither value is placed into the userInfo object
No matter where these assignments occur the values are not set on the userInfo object. They are blank.
Setting the first membership property, no matter what property it is (I've mixed them around to see if it mattered) throws an error, "Object reference not set to an instance of an object" when I step through the code but I'm able to continue to step through the code. When complete though, all membership properties have the correct values.
Setting the Profile object properties doesn't set the values, the properties have their default values. None of the profile values are saved, neither the default profile items or the custom items set with SetProfileProperty(). All are blank.
Calling If UserController.CreateUser(userInfo) = UserCreateStatus.AddUser Then throws an error, "Object reference not set to an instance of an object". This time it's a critical error and I can't continue through the code.
I am stumped with this one. From all the samples I found this code should work.
Does anyone have any ideas?
Thanks in advance