Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Question Unanswered: Updated View from Stored Procedure

    Hi,
    I have created one view which have values of 3 different table in it and now i want to update this view though stored procedure but it's not allowing me to do so it gives below error

    "Msg 4405, Level 16, State 1, Procedure UserMaster, Line 16
    View or function 'v_UserMaster' is not updatable because the modification affects multiple base tables."


    please tell me how can i resolve this error....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please can you post the SQL for the view?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2010
    Posts
    3
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE UserMaster

    @UserId int,
    @EmployeeId int,
    @UserTypeId int,
    @EmployeeName Varchar(255),
    @UserName varchar(50),
    @Password Varchar(50),
    @ContactNo varchar(50),
    @EmailAddress varchar(50),
    @Active bit,
    @UserType varchar(50)

    AS
    BEGIN
    UPDATE v_UserMaster SET
    EmployeeName = @EmployeeName,
    EmailAddress = @EmailAddress,
    UserName = @UserName,
    Password = @Password,
    ContactNo = @ContactNo,
    UserType = @UserType
    WHERE UserId = @UserId And EmployeeId = @EmployeeId

    END
    GO

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's not the view - that is the procedure.
    Short of it is exactly what the error says so with the view definition we might be able to sort things out.

    I really, really hope that isn't storing the user's password in plain text too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    3
    The View looks like
    SELECT dbo.tblUserMaster.UserId, dbo.tblUserMaster.UserName, dbo.tblUserMaster.Active, dbo.tblEmployee.EmployeeId, dbo.tblEmployee.EmployeeName,
    dbo.tblEmployee.EmailAddress, dbo.tblEmployee.ContactNo, dbo.tblUserType.UserType, dbo.tblUserType.UserTypeId, dbo.tblUserMaster.Password
    FROM dbo.tblUserMaster INNER JOIN
    dbo.tblEmployee ON dbo.tblUserMaster.EmployeeId = dbo.tblEmployee.EmployeeId INNER JOIN
    dbo.tblUserType ON dbo.tblUserMaster.UserTypeId = dbo.tblUserType.UserTypeId



    and it does stores the password in plain text

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE UserMaster
    
        @UserId int,
        @EmployeeId int,
        @UserTypeId int,
        @EmployeeName Varchar(255),
        @UserName varchar(50),
        @Password Varchar(50),
        @ContactNo varchar(50),
        @EmailAddress varchar(50),
        @Active bit,
        @UserType varchar(50)
    
    AS
    BEGIN
    
        BEGIN TRY
            
            BEGIN TRANSACTION
            
                UPDATE v_UserMaster SET
                EmployeeName = @EmployeeName,
                EmailAddress = @EmailAddress,
                ContactNo = @ContactNo
                WHERE UserId = @UserId And EmployeeId = @EmployeeId
                
                UPDATE v_UserMaster SET
                UserType = @UserType
                WHERE UserId = @UserId And EmployeeId = @EmployeeId
        
                UPDATE v_UserMaster SET
                Password = @Password,
                UserName = @UserName
                WHERE UserId = @UserId And EmployeeId = @EmployeeId
                
            COMMIT TRANSACTION
            
        END TRY
        BEGIN CATCH
            
            IF @@TRANCOUNT > 1
            BEGIN
                ROLLBACK TRANSACTION
            END
            
        END CATCH
        
    END
    GO
    Storing passwords as plain text is about as a bad a practice as you can get.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •