I'm trying to insert data in my db. this is the code im using
SqlCommand cmd = new SqlCommand("dbo.UsersInsert", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.Int);
cmd.Parameters.AddWithValue("@UserFirstName", FirstName.Text);
cmd.Parameters.AddWithValue("@UserMiddleName", MiddleName.Text);
cmd.Parameters.AddWithValue("@UserLastName", LastName.Text);
etc...
It is giving me error
Procedure or function 'UsersInsert' expects parameter '@UserID', which was not supplied.
on cmd.ExecuteNonQuery(); line.
Here, My UserID is auto-generated in my Database. How do I resolve this?
Stored Procedure
@UserID INT OUTPUT,
@UserFirstName VARCHAR (50),
@UserMiddleName VARCHAR (50),
@UserLastName VARCHAR (50), etc...
AS
SET NOCOUNT ON
DECLARE @ReturnValue int
BEGIN
SELECT @ReturnValue = 0
INSERT [Users]
(
[UserFirstName]
,[UserMiddleName]
,[UserLastName] etc...
values (
@UserFirstName,
@UserMiddleName,
@UserLastName, etc.. )
IF (@@Error <> 0) GOTO ERROR_HANDLER
GOTO OnExit
END
You should make it an ParameterDirection.Output
SqlParameter userIdParam = new SqlParameter("@UserID", SqlDbType.Int);
userIdParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(userIdParam);
This way you don't have to supply a value