Wrox Programmer Forums
|
BOOK: ASP.NET Website Programming Problem-Design-Solution
This is the forum to discuss the Wrox book ASP.NET Website Programming: Problem - Design - Solution, Visual Basic .NET Edition by Marco Bellinaso, Kevin Hoffman; ISBN: 9780764543869
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET Website Programming Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 6th, 2004, 10:58 AM
Authorized User
 
Join Date: Nov 2003
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to seanmayhew
Default Stored Procedures

I've copied and pasted all of the stored procedures into the Query Analyzer to run install them on my own DB rather than thePhile db but when I run the Analyzer I get this list of errors:

Code:
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_AddUserToRole, Line 9
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 1, Procedure sp_Accounts_AddUserToRole, Line 13
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_DeleteRole, Line 8
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_GetUserRoles, Line 6
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 1, Procedure sp_Accounts_GetUserRoles, Line 6
Invalid column name 'RoleID'.
Server: Msg 207, Level 16, State 3, Procedure sp_Accounts_RemoveUserFromRole, Line 7
Invalid column name 'RoleID'.
Here are the SP queries.

Code:
CREATE PROCEDURE sp_Accounts_AddPermissionToRole
@RoleID int,
@PermissionID int
AS
    DECLARE @Count int

    SELECT @Count = Count(PermissionID) FROM Accounts_RolePermissions WHERE
        RoleID = @RoleID and PermissionID = @PermissionID

    IF @Count = 0
        INSERT INTO Accounts_RolePermissions(RoleID, PermissionID)
        VALUES(@RoleID, @PermissionID)

GO


CREATE PROCEDURE sp_Accounts_AddUserToRole
@UserID int,
@RoleID int
AS
    DECLARE @Count int

    SELECT @Count = Count(UserID) FROM Accounts_UserRoles WHERE
        RoleID = @RoleID AND UserID = @UserID

    IF @Count = 0
        INSERT INTO Accounts_UserRoles(UserID, RoleID)
        VALUES(@UserID, @RoleID)

GO


CREATE PROCEDURE sp_Accounts_ClearPermissionsFromRole
@RoleID int
AS
    DELETE Accounts_RolePermissions WHERE RoleID = @RoleID

GO


CREATE PROCEDURE sp_Accounts_CreateRole
@Description varchar(50)
AS
    INSERT INTO Accounts_Roles(Description) VALUES(@Description)
    RETURN @@IDENTITY

GO



CREATE PROCEDURE sp_Accounts_CreateUser
@EmailAddress varchar(255),
@Password binary(20),
@FirstName varchar(30),
@LastName varchar(30),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(40),
@State varchar(2),
@ZipCode varchar(10),
@HomePhone varchar(14),
@Country varchar(50),
@UserID int output
AS
    INSERT INTO Accounts_Users(EmailAddress, Password, FirstName, LastName, Address1, Address2,
        City, State, ZipCode, HomePhone, Country)
    VALUES(@EmailAddress, @Password, @FirstName, @LastName, @Address1, @Address2,
        @City, @State, @ZipCode, @HomePhone, @Country)

    SET @UserID = @@IDENTITY

    RETURN 1

GO


CREATE PROCEDURE sp_Accounts_DeleteRole
@RoleID int
AS
    BEGIN TRANSACTION
        DELETE Accounts_RolePermissions WHERE RoleID = @RoleID
        DELETE Accounts_UserRoles WHERE RoleID = @RoleID
        DELETE Accounts_Roles WHERE RoleID = @RoleID
    COMMIT TRANSACTION

GO


CREATE PROCEDURE sp_Accounts_DeleteUser
@UserID int
AS
     DECLARE @MemberID int

     SELECT @MemberID = MemberId FROM Forums_Members WHERE UserId = @UserId

     IF @MemberID IS NOT NULL
     BEGIN
          DELETE Forums_Members WHERE UserId = @UserId
          DELETE Forums_Topics WHERE MemberId = @MemberId
          DELETE Forums_Replies WHERE MemberId =  @MemberId
     END

     DELETE Accounts_UserRoles WHERE UserId = @UserId
     DELETE ACcounts_Users WHERE UserId = @UserId
GO



CREATE PROCEDURE sp_Accounts_GetAllRoles
AS
    SELECT RoleID, Description FROM Accounts_Roles ORDER BY Description ASC

GO


CREATE PROCEDURE sp_Accounts_GetEffectivePermissionList
@UserID int
AS
   SELECT DISTINCT PermissionID FROM Accounts_RolePermissions WHERE RoleID IN
    (SELECT RoleID FROM Accounts_UserRoles WHERE UserID = @UserID)


GO

CREATE PROCEDURE sp_Accounts_GetPermissionCategories
AS
    SELECT * FROM Accounts_PermissionCategories
GO


CREATE PROCEDURE sp_Accounts_GetPermissionCategoryDetails
@CategoryID int
AS
    SELECT CategoryID, Description FROM Accounts_PermissionCategories WHERE CategoryID = @CategoryID

GO


CREATE PROCEDURE sp_Accounts_GetPermissionList
@RoleID int = NULL
AS
    IF @RoleID IS NULL
        SELECT PermissionID, Description, CategoryID FROM Accounts_Permissions ORDER BY Description
    ELSE
        SELECT ap.PermissionID, ap.Description, ap.CategoryID FROM Accounts_Permissions ap INNER JOIN
        Accounts_RolePermissions apr ON ap.PermissionID = apr.PermissionID WHERE
        apr.RoleID = @RoleID ORDER BY ap.Description

GO


CREATE PROCEDURE sp_Accounts_GetRoleDetails
@RoleID int
AS
    SELECT RoleID, Description FROM Accounts_Roles WHERE RoleID = @RoleID

GO

CREATE PROCEDURE sp_Accounts_GetStateList
AS
    SELECT * FROM Accounts_StateCodes ORDER BY StateCode ASC
GO


CREATE PROCEDURE sp_Accounts_GetUserDetails
@UserID int
AS
    SELECT * FROM Accounts_Users WHERE UserID = @UserID

GO


CREATE PROCEDURE sp_Accounts_GetUserDetailsByEmail
@EmailAddress varchar(255)
AS
    SELECT * FROM Accounts_Users WHERE EmailAddress = @EmailAddress

GO


CREATE PROCEDURE sp_Accounts_GetUserRoles
@UserID int
AS
    SELECT ur.RoleID, r.Description FROM Accounts_UserRoles ur
        INNER JOIN Accounts_Roles r ON ur.RoleID = r.RoleID  WHERE ur.UserID = @UserID

GO



CREATE PROCEDURE sp_Accounts_RemovePermissionFromRole
@RoleID int,
@PermissionID int
AS
    DELETE Accounts_RolePermissions WHERE RoleID = @RoleID and PermissionID = @PermissionID

GO


CREATE PROCEDURE sp_Accounts_RemoveUserFromRole
@UserID int,
@RoleID int
AS
    DELETE Accounts_UserRoles WHERE UserID = @UserID AND RoleID = @RoleID

GO



CREATE PROCEDURE sp_Accounts_TestPassword
@UserID int,
@EncryptedPassword binary(20)
AS
     DECLARE @TempID int
     SELECT @TempID = UserID FROM Accounts_Users WHERE UserID = @UserID AND
    Password = @EncryptedPassword

     IF @TempID IS NULL
    RETURN 0
     ELSE
    RETURN 1

GO


CREATE PROCEDURE sp_Accounts_UpdateRole
@RoleID int,
@Description varchar(50)
AS
    UPDATE Accounts_Roles SET Description = @Description WHERE RoleID = @RoleID

GO


CREATE PROCEDURE sp_Accounts_UpdateUser
@EmailAddress varchar(255),
@Password binary(20),
@FirstName varchar(30),
@LastName varchar(50),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(40),
@State varchar(2),
@ZipCode varchar(10),
@HomePhone varchar(14),
@Country varchar(50),
@UserID int
AS
    UPDATE Accounts_Users SET
        EmailAddress = @EmailAddress,
        Password = @Password,
        FirstName = @FirstName,
        LastName = @LastName,
        Address1 = @Address1,
        Address2 = @Address2,
        City = @City,
        State = @State,
        ZipCode = @ZipCode,
        HomePhone = @HomePhone,
        Country = @Country
    WHERE UserID = @UserID

GO


CREATE PROCEDURE sp_Accounts_ValidateLogin
@EmailAddress varchar(255),
@EncryptedPassword binary(20)
AS
   DECLARE @UserID int

   SELECT @UserID = UserID FROM Accounts_Users WHERE EmailAddress = @EmailAddress
    AND Password = @EncryptedPassword

   IF @UserID != NULL   
    RETURN @UserID
   ELSE
    RETURN -1

GO
Im a SQL Server idiot I know I need a book but can someone help me understand this.

 
Old June 6th, 2004, 11:01 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

Please Check if 'RoleID' column exists in Accounts_RolePermissions table.

Om Prakash
 
Old June 7th, 2004, 07:49 PM
Authorized User
 
Join Date: Nov 2003
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to seanmayhew
Default

Yes there is a RoleID in the Accounts_RolePermissions table

 
Old June 9th, 2004, 08:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What SQL Server user account was used to create the table? Maybe it's not the same SQL Server account as the one you used to create the procedures?

Just as C# and VB.NET use namespaces, SQL Server uses user accounts in a similar way to distinguish the differences between objects created by different users. To fully qualify the object name, you prefix it with the name of the SQL Server user.

Eric
 
Old June 10th, 2004, 10:06 AM
Authorized User
 
Join Date: Nov 2003
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to seanmayhew
Default

Im sorry yes that was the problem my "RoleID" was mistyped as "RoldeID" Thanks!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedures KeviJay VB Databases Basics 1 June 5th, 2008 07:17 AM
stored procedures MunishBhatia SQL Server 2005 4 April 12th, 2008 01:39 AM
STORED PROCEDURES shazia1 SQL Server ASP 7 September 26th, 2007 06:11 AM
Stored Procedures itHighway SQL Server 2000 3 November 23rd, 2005 10:08 AM
Stored Procedures stu9820 Access 3 February 8th, 2004 01:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.