ASPNETDB.MDF-uniqueidentifier - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

ASPNETDB.MDF-uniqueidentifier

Author

Topic

lenchase
Starting Member

1 Post

Posted - 2006-01-25 : 19:50:46

Hi:
I am using the built in securty database from vs2005
I am trying to write an insert trigger that will add a role each time a user is added but I seem to be having difficulty I believe it's with the uniqueidentifier datatype. when I run this trigger via an insert statement I get the following error.
Can anyone set me straight?
========error==============
Cannot insert the value NULL into column 'RoleId', table 'R:\AAAPROJECTS\ASP\VS2005\MERCERBUCKS\APP_DATA\ASPNETDB.MDF.dbo.aspnet_UsersInRoles'; column does not allow nulls. INSERT fails.
==================insert statement==========

Declare @NewUserID as uniqueIdentifier
set @NewUserID=newID()
print Cast(@NewUserID as varchar(50))
insert into dbo.aspnet_Users (ApplicationID,UserID,UserName,LoweredUserName,LastActivityDate)
Values('f23f01f0-7ad7-463b-87e2-f3e9141e6426',@NewUserID,'lrchase','lrchase','7/20/2005')
======trigger============================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [InsertRole]
ON [dbo].[aspnet_Users]
AFTER INSERT
AS
DECLARE @RoleID uniqueidentifier
DECLARE @UserID uniqueidentifier
Select @UserID=Userid from inserted
Print @UserID
SELECT @RoleID = RoleID
FROM dbo.aspnet_Roles where dbo.aspnet_Roles.LoweredRoleName='user'
Print @RoleID
SET NOCOUNT ON;
Insert Into dbo.aspnet_UsersInRoles
Values(@UserID,@RoleID)

Thanks Len

jeffreymfischer
Starting Member

10 Posts

Posted - 2009-10-07 : 11:15:11

Looking at your trigger, I would assume that if you ran the following query, you'd get nothing back. It would appear that there are no aspnet_Roles with a LowerRoleName of 'user'.


SELECT RoleID
FROM dbo.aspnet_Roles where dbo.aspnet_Roles.LoweredRoleName='user'

I've written a detailed article on an enterprise-ready unique identifier solution.

http://blog.scoftware.com/post/2009/08/29/SQL-Server-UniqueIdentifier-Globally-Unique-Globally-Sequential-SOLUTION.aspx

Read it and provide feedback.

Jeff Fischer

Scoftware Achitect/Developer
http://blog.scoftware.com

Scoftware Achitect/Developer
http://blog.scoftware.com

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2009-10-07 : 18:50:32

why not simply use the MS membership provider to create the db rather than inserting the values yourself

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources