error:the insert failed 'coz the Procedure or function spInseruserRoles has too many arguments
here's the c# code:[DAL, the code is based on a 3 tier architecture]
sqlconnection.......;[the basic connection ]
SqlParameter objParam;
objParam = new SqlParameter("@RoleName", SqlDbType.VarChar);
objParam.Value = (RoleName);
objCommand.Parameters.Add(objParam);
objParam = new SqlParameter("@UserID", SqlDbType.Int);
//objparam.Direction = ParameterDirection.Output;
objParam.Value = Convert.ToInt32(UserID);
objCommand.Parameters.Add(objParam);
objCon.Open();
objCommand.ExecuteNonQuery();
Th sql storedproc:
alter PROCEDURE [dbo].[spInsertUserRoles]
(
@RoleName varchar(50),
@UserID INT
--@Result INT OUTPUT
)
-- WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
Declare @RoleID int
SELECT @RoleID = RoleID FROM dbo.Roles WHERE RoleName like @RoleName
IF NOT EXISTS (SELECT 1 FROM dbo.UserRoles WHERE RoleID = @RoleID and UserID = @UserID )
BEGIN
INSERT dbo.UserRoles
(
RoleID , UserID
)
VALUES
(
@RoleName ,@UserID
)
--SELECT @Result = 1
END
ELSE
-- SELECT @Result = 0
SET NOCOUNT OFF
end
|
| Author: Sravya 04 Dec 2008 | Member Level: Gold | Rating:  Points: 1 |
Visit the blog, it will helps you alot
www.codesnippetforyou.blogspot.com
thanks and regards
|
| Author: Pradeep Iyer 04 Dec 2008 | Member Level: Diamond | Rating: Points: 2 |
Hi,
Here in the INSERT statement, you are only passing two values while you have three fields in your table.
The number of fields should match exactly with that in the table.
Regards,
Pradeep Y
Regards,
Pradeep Iyer
|
| Author: Mohsin Mehmood 04 Dec 2008 | Member Level: Silver | Rating: Points: 3 |
I think this portion of your sp code is causing the problem
INSERT dbo.UserRoles
(
RoleID , UserID
)
VALUES
(
@RoleName ,@UserID
)
You are Inserting @RoleName in RoleID column and datatype
of RoleID may be set to Int.
Hope this Helps
Mohsin Mehmood
|