Sqlcommand executenonquery抛出异常system.invalidcast [英] Sqlcommand executenonquery throws exception system.invalidcast

问题描述

我使用存储过程从c＃asp.net将数据插入数据库。我已经使用数据访问类库来插入数据，在最后我在executetenonquery方法的问题中提到了这个错误。在这种方法中，该值也为零。我将发送存储过程和数据访问类库。请纠正我在哪里做错了



我尝试过：



存储过程：

  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 学生其中 StudentId =  @ guidStudentId ）
  BEGIN  
 
  INSERT   INTO 学生（StudentId，StudentName，AccessLevel，EntryDate，EmailAddress，Phone，Pass wordHash，RegisterationsId，CreatedBy，CreatedDate）
  VALUES （ @ guidStudentId ， @ chvStudentName ， @ chvAccessLevel ， @ dateEntryDate ， @ chvEmailAddress ， @ chvPhone ， @ chvPasswordHash ， @ guidRegisterationsId ， @ guidCreatedBy ， @ dateCreatedDate ）
 
  SET   @ chvOutMessage  = ' 成功插入学生表' 
 
  INSERT   INTO 用户（UserId，StudentId，UserName，Password，UserType，I sSuperUser，CreatedBy，CreatedDate）
  VALUES （NEWID（）， @ guidStudentId ， @ chvStudentName ， @ chvPasswordHash ， @ chvAccessLevel ，'  False'， @ guidCreatedBy ， @ dateCreatedDate ）
 
  SET   @ chvOutMessage  = ' 成功插入用户表' 
 
  END  
  ELSE  
  BEGIN  
 
 更新学生 SET  
 ModifiedBy =  @ GUID StudentId ，
 ModifiedDate =  @ dateCreatedDate ，
 IsActive =  0  
  WHERE  IsActive =  1  
  AND  StudentId =  @ guidStudentId  
 
  SET   @ chvOutMessage  = ' 成功更新的学生详情 
 
 更新用户 SET  
 ModifiedBy =  @guidStudentId ，
 ModifiedDate =  @ dateCreatedDate  
  WHERE  StudentId =  @ guidStudentId  
 
  SET   @ chvOutMessage  = ' 成功更新的用户详情 
 
  END  
 
  END  

数据访问库代码：

 public StudentBO InsertOrUpdateStudentDetails（StudentBO objStudentBO）
 {
 SqlConnection con = new SqlConnection（DbBridge.DbConnection）; 
 
尝试
 {
 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; 
} 
 
最后
 {
} 
返回objStudentBO; 
} 

其中私有const字符串INSERT_OR_UPDATE_STUDENT_DETAILS =usp_Students_InsertOrUpdateStudentsDetails;



在int处获取错误k = cmd.ExecuteNonQuery（）;

解决方案

错误告诉您正在尝试将一种类型的数据存储在错误类型的变量/字段中。例如，您正在尝试将字符串单词存储在Int变量中。它没有正确地转换为正确的类型。



你需要调试你的sql并看看发生了什么。我建议运行Sql Profiler来查看正在发送的确切sql。

检查Null值

 string.IsNullOrEmpty（参数）

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();

解决方案

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.

check for Null value

string.IsNullOrEmpty(parameter)

这篇关于Sqlcommand executenonquery抛出异常system.invalidcast的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！