| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-27-10, 05:48
|
|
Registered User
|
|
Join Date: Aug 2010
Posts: 3
|
|
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.... 
|
|
08-27-10, 05:57
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Please can you post the SQL for the view?
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
08-27-10, 06:13
|
|
Registered User
|
|
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
|
|
08-27-10, 06:27
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
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:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
08-27-10, 06:32
|
|
Registered User
|
|
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 
|
|
08-27-10, 06:41
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
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:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Updated View from Stored Procedure
