i am inserting data into the database from c# asp.net using stored procedure. I have used data access class library to insert the data in which at the last i am getting this error mention in the question at executenonquery method. in this method the value is zero also. I will send the stored procedure and data access class library. please correct me where i am doing wrong
What I have tried:
Stored Procedure:
CREATE PROCEDURE [dbo].[usp_Student_InsertOrUpdateStudentDetails]
(
@guidStudentId UNIQUEIDENTIFIER,
@chvStudentName VARCHAR(50),
@chvAccessLevel VARCHAR(50),
@dateEntryDate DATETIME,
@chvEmailAddress VARCHAR(200),
@chvPhone VARCHAR(10),
@chvPasswordHash VARCHAR(MAX),
@guidRegisterationsId UNIQUEIDENTIFIER,
@guidCreatedBy UNIQUEIDENTIFIER,
@dateCreatedDate DATETIME,
@chvOutMessage VARCHAR(1000) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
SET @guidStudentId = NEWID()
SET @dateCreatedDate = CONVERT(varchar(10),GETDATE(),105)
IF NOT EXISTS(SELECT '*' FROM Student where StudentId = @guidStudentId)
BEGIN
INSERT INTO Student(StudentId, StudentName, AccessLevel, EntryDate, EmailAddress, Phone, PasswordHash, RegisterationsId, CreatedBy, CreatedDate)
VALUES (@guidStudentId, @chvStudentName, @chvAccessLevel, @dateEntryDate, @chvEmailAddress, @chvPhone, @chvPasswordHash, @guidRegisterationsId, @guidCreatedBy, @dateCreatedDate)
SET @chvOutMessage = 'SUCCESS INSERTED INTO STUDENT TABLE'
INSERT INTO Users(UserId,StudentId,UserName,Password,UserType,IsSuperUser,CreatedBy,CreatedDate)
VALUES (NEWID(),@guidStudentId,@chvStudentName,@chvPasswordHash,@chvAccessLevel,'False',@guidCreatedBy,@dateCreatedDate)
SET @chvOutMessage = 'SUCCESS INSERTED INTO USERS TABLE'
END
ELSE
BEGIN
UPDATE Student SET
ModifiedBy = @guidStudentId,
ModifiedDate = @dateCreatedDate,
IsActive = 0
WHERE IsActive = 1
AND StudentId = @guidStudentId
SET @chvOutMessage = 'SUCCESSFULLY UPDATED STUDENT DETAILS'
UPDATE Users SET
ModifiedBy = @guidStudentId,
ModifiedDate = @dateCreatedDate
WHERE StudentId = @guidStudentId
SET @chvOutMessage = 'SUCCESSFULLY UPDATED USERS DETAILS'
END
END
Data Access Library code:
public StudentBO InsertOrUpdateStudentDetails(StudentBO objStudentBO)
{
SqlConnection con = new SqlConnection(DbBridge.DbConnection);
try
{
con.Open();
SqlCommand cmd = new SqlCommand(INSERT_OR_UPDATE_STUDENT_DETAILS, con)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@guidStudentId", objStudentBO.StudentId);
cmd.Parameters.AddWithValue("@chvStudentName", objStudentBO.StudentName);
cmd.Parameters.AddWithValue("@chvAccessLevel", objStudentBO.AccessLevel);
cmd.Parameters.AddWithValue("@dateEntryDate", objStudentBO.EntryDate);
cmd.Parameters.AddWithValue("@chvEmailAddress", objStudentBO.EmailAddress);
cmd.Parameters.AddWithValue("@chvPhone", objStudentBO.Phone);
cmd.Parameters.AddWithValue("@chvPasswordHash", objStudentBO.PasswordHash);
cmd.Parameters.AddWithValue("@guidRegisterationsId", objStudentBO.RegisterationsId);
cmd.Parameters.AddWithValue("@guidCreatedBy", objStudentBO.CreatedBy);
cmd.Parameters.AddWithValue("@dateCreatedDate", objStudentBO.CreatedDate);
cmd.Parameters.AddWithValue("@chvOutMessage", OUTMESSAGE_SIZE);
int k = cmd.ExecuteNonQuery();
if (k == 1)
objStudentBO.OutMessage = cmd.Parameters["@chvOutMessage"].Value.ToString();
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return objStudentBO;
}
where private const string INSERT_OR_UPDATE_STUDENT_DETAILS = "usp_Students_InsertOrUpdateStudentsDetails";
Getting error at int k = cmd.ExecuteNonQuery();