 |
|
|
|
www.JamellDigital.com
Joined: 1/5/2006
Posts: 291
|
|
|
Success! At least in producing an error in the Event Viewer. Here it is below.
AssemblyVersion: 04.09.02
PortalID: 0
PortalName: SDInet
UserID: 6
UserName: David.Jamell.SU
ActiveTabID: 42
ActiveTabName: User Accounts
RawURL: /Admin/UserAccounts/tabid/42/ctl/Edit/mid/359/filter/All/currentpage/1/Default.aspx
AbsoluteURL: /Default.aspx
AbsoluteURLReferrer: http://sdinet.sdicorp.com/Admin/UserAccounts/tabid/42/ctl/Edit/mid/359/filter/All/currentpage/1/Default.aspx
UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727)
DefaultDataProvider: DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider
ExceptionGUID: 31abecaa-53ad-4e92-b344-8bf02ef3ba6f
InnerException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserProfile_Users". The conflict occurred in database "SDINet", table "dbo.Users", column 'UserID'. The statement has been terminated.
FileName:
FileLineNumber: 0
FileColumnNumber: 0
Method: System.Data.SqlClient.SqlConnection.OnError
StackTrace:
Message: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserProfile_Users". The conflict occurred in database "SDINet", table "dbo.Users", column 'UserID'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Security.Membership.Data.SqlDataProvider.UpdateProfileProperty(Int32 ProfileId, Int32 UserId, Int32 PropertyDefinitionID, String PropertyValue, Int32 Visibility, DateTime LastUpdatedDate) at DotNetNuke.Security.Profile.DNNProfileProvider.UpdateUserProfile(UserInfo user) at DotNetNuke.Entities.Profile.ProfileController.UpdateUserProfile(UserInfo objUser) at DotNetNuke.Security.Membership.AspNetMembershipProvider.CreateUser(UserInfo& user)
Source:
Server Name: SDI-CNPROD
|
| |
|
|
 | |
|
|
|
brandonhaynes.org
Joined: 2/6/2006
Posts: 1172
|
|
|
Hi David,
Like layers of an onion, we have peeled back one more clue as to the source of your problem. The error you posted above is a manifestation of an exception management issue at the very lowest level of the membership provider (for which I created a work item), but it does not reveal the actual source of your error.
There is almost certainly a problem with your AddUser stored procedure, likely caused by upgrade or data integrity issue (apropos, I have recently been helping another user with a similar issue). I can provide some an additional assembly that will aid in revealing the ultimate error, but I'd recommend posting the contents of that procedure here before we proceed. It should read:
ALTER PROCEDURE [dbo].[AddUser]
@PortalID int,
@Username nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@AffiliateId int,
@IsSuperUser bit,
@Email nvarchar(256),
@DisplayName nvarchar(100),
@UpdatePassword bit,
@Authorised bit
AS
DECLARE @UserID int
SELECT @UserID = UserID
FROM dbo.Users
WHERE Username = @Username
IF @UserID is null
BEGIN
INSERT INTO dbo.Users (
Username,
FirstName,
LastName,
AffiliateId,
IsSuperUser,
Email,
DisplayName,
UpdatePassword
)
VALUES (
@Username,
@FirstName,
@LastName,
@AffiliateId,
@IsSuperUser,
@Email,
@DisplayName,
@UpdatePassword
)
SELECT @UserID = SCOPE_IDENTITY()
END
IF @IsSuperUser = 0
BEGIN
IF not exists ( SELECT 1 FROM dbo.UserPortals WHERE UserID = @UserID AND PortalID = @PortalID )
BEGIN
INSERT INTO dbo.UserPortals (
UserID,
PortalID,
Authorised
)
VALUES (
@UserID,
@PortalID,
@Authorised
)
END
END
SELECT @UserID
Brandon
|
| |
|
|
| |
|
|
|
www.JamellDigital.com
Joined: 1/5/2006
Posts: 291
|
|
|
No problem. Here is the contents of that procedure from my database. I went over it twice and it seems to be exactly the same, but I'll let you be the judge.
ALTER PROCEDURE [dbo].[AddUser]
@PortalID int,
@Username nvarchar(100),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@AffiliateId int,
@IsSuperUser bit,
@Email nvarchar(256),
@DisplayName nvarchar(100),
@UpdatePassword bit,
@Authorised bit
AS
DECLARE @UserID int
SELECT @UserID = UserID
FROM dbo.Users
WHERE Username = @Username
IF @UserID is null
BEGIN
INSERT INTO dbo.Users (
Username,
FirstName,
LastName,
AffiliateId,
IsSuperUser,
Email,
DisplayName,
UpdatePassword
)
VALUES (
@Username,
@FirstName,
@LastName,
@AffiliateId,
@IsSuperUser,
@Email,
@DisplayName,
@UpdatePassword
)
SELECT @UserID = SCOPE_IDENTITY()
END
IF @IsSuperUser = 0
BEGIN
IF not exists ( SELECT 1 FROM dbo.UserPortals WHERE UserID = @UserID AND PortalID = @PortalID )
BEGIN
INSERT INTO dbo.UserPortals (
UserID,
PortalID,
Authorised
)
VALUES (
@UserID,
@PortalID,
@Authorised
)
END
END
SELECT @UserID
.
|
| |
|
|
| |
|
|
|
brandonhaynes.org
Joined: 2/6/2006
Posts: 1172
|
|
|
Hi David,
Your SP looks fine. There is likely an integrity issue causing this problem.
Please execute the following SQL against your database to see if you can generate the error without having to do more library replacement. If this fails, I will compile some additional debugging libraries for you to try:
SET XACT_ABORT ON
BEGIN TRANSACTION
EXEC [AddUser]
0,
'username',
'firstname',
'lastname',
null,
0,
'email@email.com',
'displayname',
0,
1
ROLLBACK TRANSACTION
I'd advise replacing the random strings above with the actual data that you were using via the UI, this will expose any esoteric foreign key issues that might exist.
Deeper into the rabbit hole we go...
Brandon
|
| |
|
|
| |
|
|
|
www.JamellDigital.com
Joined: 1/5/2006
Posts: 291
|
|
|
When trying to issue this against the database using SQL Server Management Studio on the Server Console I get a dialog box titled "SQL Syntax Errors Encountered" with the following error:
The Set SQL construct or statement is not supported.
I click "OK" to dismiss the dialog box and get another dialog box titled "Microsoft SQL Server Management Studio" with the following error:
SQL Execution Error.
Executed SQL statement: SET XACT_ABORT ON BEGIN TRANSACTION EXEC [AddUser] 0, 'test.user', 'test', 'user', null, 0, 'testuser@sdicorp.com', 'testuser', 0, 1 ROLLBACK TRANSACTION
Error Source .Net SqlClient Data Provider
Error Message: Cannot insert the value NULL into 'EMPID', table 'SDINet.dbo.User_Profile'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Am I doing something wrong, or is this the error you expected?
|
| |
|
|
| |