Click here to Skip to main content
15,893,722 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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:
SQL
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();
Posted
Updated 1-Oct-18 10:25am
v2
Comments
Member 8583441 1-Oct-18 5:16am    
StudentBO is BusinessObject class library
Member 8583441 1-Oct-18 5:27am    
I am unable to find the exact error coming from means the system.invalidtypecast
summiya1 1-Oct-18 9:31am    
yes compare your parameters with SQL date types. check dates parameters as well.

The error is telling you that you are trying to store one type of data in the wrong type of variable/field. For example, you are trying to store a string word in an Int variable. It is not casting properly to the correct type.

You'll need to debug your sql and see what is happening. I would recommend running Sql Profiler to see the exact sql that is being sent.
 
Share this answer
 
Comments
Member 8583441 2-Oct-18 9:32am    
I have debugged the sql query but no use same result
ZurdoDev 2-Oct-18 9:36am    
No one can do this for you. Something in your data is wrong and you have to find it. We can't run it for you.

Start by taking things out of your stored procedure and simplify it down. By process of elimination you can find exactly what is causing the error and then fix it. But YOU have to do it.
Member 8583441 2-Oct-18 9:39am    
Please do something sir for me
ZurdoDev 2-Oct-18 9:43am    
How? I can't run your code. What do you want us to do?
Member 8583441 2-Oct-18 9:44am    
what you require from my side.... I will give it to you
check for Null value
string.IsNullOrEmpty(parameter)
 
Share this answer
 
Comments
Member 8583441 1-Oct-18 9:11am    
Where to place this line
Member 8583441 1-Oct-18 9:21am    
Thanks for the solution you have given me to check whether the parameters is null or empty but the parameters are not empty they are having values...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900