API Questions on Kentico API.
Version 5.x > API > Adding Users via API View modes: 
User avatar
Member
Member
andy.holt-staffordshire.gov - 8/16/2011 2:06:18 AM
   
Adding Users via API
Hi

We're running a bit of VB code to add a load of users to a site from a csv file. However we've found that we've had to put alll of the CMS update steps in a 'Try...Catch' as below:-

Dim ExistingUser As New CMS.SiteProvider.UserInfo

ExistingUser = CMS.SiteProvider.UserInfoProvider.GetUserInfo(NewUserName)

If ExistingUser Is Nothing Then

Dim NewUser As New CMS.SiteProvider.UserInfo
NewUser.UserName = NewUserName
NewUser.FirstName = NewFirstName
NewUser.LastName = NewLastName
NewUser.FullName = NewFirstName & " " & NewLastName
NewUser.MiddleName = ""
NewUser.PreferredCultureCode = ""
NewUser.PreferredUICultureCode = ""
NewUser.UserEnabled = True
NewUser.UserIsEditor = False
NewUser.UserIsExternal = False
NewUser.UserIsHidden = False
NewUser.UserHasAllowedCultures = False
NewUser.Email = NewEmail

NewUser.PasswordFormat = "SHA1"

Try
CMS.SiteProvider.UserInfoProvider.SetUserInfo(NewUser)
Catch ex As Exception

End Try

Try
CMS.SiteProvider.UserInfoProvider.SetPassword(NewUserName, NewPassword)
Catch ex As Exception

End Try

Try
CMS.SiteProvider.UserInfoProvider.AddUserToSite(NewUser.UserName, "StaffordshirePrepared")
Catch ex As Exception

End Try

Try
CMS.SiteProvider.UserInfoProvider.AddUserToRole(NewUser.UserName, "SPMembers", "StaffordshirePrepared")
Catch ex As Exception

End Try

Response.Write("User Added OK!")


If we don't put the 'TRY..CATCH' in we get the following error regarding the Event Log table:-

[DataConnection.ExecuteQuery]: Query: DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT [EventID] FROM [CMS_EventLog] WITH (NOLOCK) WHERE SiteID IS NULL 000)) AND (SiteID IS NULL): caused exception: Incorrect syntax near '000'.

Any ideas? (I'm running the code on a local PC, so the code is not actually running against any site). Our users are still getting added with the 'TRY..CATCH' in but we don't know if we causing any additional problems by doing this.

Thanks in advance

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/16/2011 2:52:21 AM
   
RE:Adding Users via API
Hello,


What version are you using? If 5.5, could you apply the latest hotfix for the version 5.5 (Hotfixes download) and make sure that there are no errors while applying the hotfix script and that all .dll-s are re-written? This issue was fixed by hotfix 5.5.14, but apply the latest one (they are cumulative). Thank you.


Best regards,
Helena Grulichova

User avatar
Member
Member
andy.holt-staffordshire.gov - 8/16/2011 4:28:51 AM
   
RE:Adding Users via API
Hi

We've applied the latest hotfix and it's still doing the same thing. We're doing a bulk import of around 400 users. When we run the code we get some going into the system at first, then the error described appears. It seems that once this error occurs it won't let us add any further users (even if we restart the import routine). I've checked the csv file we're importing and all of the entries are fine in that.

Any suggestions ?

User avatar
Member
Member
andy.holt-staffordshire.gov - 8/16/2011 4:29:48 AM
   
RE:Adding Users via API
Hi, I forgot to point out we're using 5.5R2 and the latest hotfix for that.

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/16/2011 4:46:16 AM
   
RE:Adding Users via API
Hello,


thank you for your reply. The query from the error message should clear Event logs from your database. It wants to log the creation or update of a user and clear the old logs before and this throws the error. Could you please write here a result of the following SQL query from your current Kentico database?

SELECT QueryText FROM CMS_Query Where [QueryName] = 'deleteolderlogs'


Best regards,
Helena Grulichova


User avatar
Member
Member
andy.holt-staffordshire.gov - 8/16/2011 5:02:44 AM
   
RE:Adding Users via API
Hi Helena
Here is the result from the query.

DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT ##TOPN## [EventID] FROM [CMS_EventLog] WITH (NOLOCK) WHERE ##WHERE## ORDER BY ##ORDERBY##)) AND (##WHERE##)

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/16/2011 6:08:32 AM
   
RE:Adding Users via API
Hi,


have you upgraded your installation from the version 5.5? We are inspecting the issue with our developers.


Best regards,
Helena Grulichova

User avatar
Member
Member
andy.holt-staffordshire.gov - 8/16/2011 6:42:30 AM
   
RE:Adding Users via API
Hi Helena

Yes, our version is an upgraded one from 5.5.

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/18/2011 3:05:10 AM
   
RE:Adding Users via API
Hi,


our developers are inspecting the issue as a possible bug. I will let you know with news as soon as possible.


Best regards,
Helena Grulichova

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 8/22/2011 2:00:04 AM
   
RE:Adding Users via API
Hello,


we were trying to reproduce the issue but we were unsuccessful. If the issue persists, can you send us to support@kentico.com a project folder and database backup (or rather make downloadable) so that we can reproduce the issue and find a cause?


Best regards,
Helena Grulichova

User avatar
Guest
Tim - 12/5/2011 5:05:34 AM
   
RE:Adding Users via API
Hi Helena,

I was wondering, did you find a solution to this problem?

I'm receiving a similair error in release v5.5.4083 R2

Query: DELETE FROM [CMS_EventLog] WHERE ([EventID] NOT IN (SELECT [EventID] FROM [CMS_EventLog] WITH (NOLOCK) WHERE SiteID = 1 ORDER BY 0)) AND (SiteID = 1): caused exception: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

At CMS.SiteProvider.UserInfoProvider.AuthenticateUser

User avatar
Kentico Developer
Kentico Developer
kentico_helenag - 12/14/2011 5:04:57 PM
   
RE:Adding Users via API
Hi Tim,


we were not able not reproduce the issue so the bug was rejected.

Can you upgrade to v6? Unless it helps, please send us your project folder and database so that we can reproduce it and inspect.

Thank you,

Helena Grulichova