将数据类型varchar转换为int时出错 [英] Error converting datatype varchar to int

问题描述

在我的数据层中，我有一个方法NewCaseNumber来使用存储过程创建一个系统编号。我得到错误将数据类型varchar转换为int。我的caseType表有SP-ComplexCrimes。但是我只能检索SP部分。

这个storedProc生成2015SP00001。我测试了它并且在db中执行得很好

In my data layer, I have a method NewCaseNumber to create a system-gen number using stored proc. I get error converting datatype varchar to int. My caseType table has SP-ComplexCrimes. But Im only retreiving the SP portion.
This storedProc generates 2015SP00001. I tested it and it executes fine in db

ALTER PROCEDURE [dbo].[GetNewCaseNumber]  
	-- Add the parameters for the stored procedure here
	@DateOpened as datetime,
	@CaseType as int,
	@NewIDOUT varchar(12)	OUTPUT

AS
BEGIN
SET NOCOUNT ON;

	-- Declare the return variable here
	DECLARE @InvType char(2)
	DECLARE @NewID varchar(12)
	DECLARE @NextNum char(5)

	-- Add the T-SQL statements to compute the return value here
	SELECT @InvType = left(Description,2)
		FROM CaseTypeList
		WHERE ID = @CaseType

	SELECT @NextNum = right('00000' + cast(isnull(max(cast(right(i.InvestigationNum,5) as int)),0) + 1 as varchar(5)),5)
		FROM Investigation i inner join CaseTypeList c on i.CaseType = c.ID
		WHERE substring(i.InvestigationNum,5,2) = @InvType and left(i.InvestigationNum,4) = Year(@DateOpened)

	SELECT @NewID = cast(Year(@DateOpened) as char(4)) + @InvType + @NextNum 

	-- Return the result of the function
	SET @NewIDOUT = @NewID;
END

在我的DAL中，错误来自ExecuteScalar行

In my DAL, error is from the ExecuteScalar line

public static string GetNewCaseNumber(DateTime DateOpened, Int32 caseType, Int32 NewIDOUT)
	{
		Database db = DatabaseFactory.CreateDatabase();
		DbCommand dbCommand = db.GetStoredProcCommand("GetNewCaseNumber");

		db.AddInParameter(dbCommand, "@DateOpened", DbType.DateTime, DateTime.Now.Date);
		db.AddInParameter(dbCommand, "@CaseType", DbType.Int32, caseType);
		db.AddOutParameter(dbCommand, "@NewIDOUT", DbType.Int32, NewIDOUT);
		
	return (string)db.ExecuteScalar(dbCommand);
	
	}

修改为以下

Modified to below

public static string GetNewCaseNumber(DateTime DateOpened, Int32 caseType, Int32 NewIDOUT)
	{
        int Result;

        Database db = DatabaseFactory.CreateDatabase();
        DbCommand dbCommand = db.GetStoredProcCommand("GetNewCaseNumber");

	  db.AddInParameter(dbCommand, "@DateOpened", DbType.DateTime, DateTime.Now.Date);
	  db.AddInParameter(dbCommand, "@CaseType", DbType.Int32, caseType);
	  db.AddOutParameter(dbCommand, "@NewIDOUT", DbType.Int32, NewIDOUT);

        db.ExecuteNonQuery(dbCommand);

        Result = (Int32)db.GetParameterValue(dbCommand, "@NewIDOUT");

//新错误：并非所有代码路径都返回值



我需要帮助正确完成，同事建议使用ExecuteNonQuery，但我需要将其作为字符串2015SP00001返回。任何帮助表示赞赏，或者只是指出我正确的方向。谢谢！

//New error: Not all code paths return a value

I need help doing it properly, co-worker suggested using ExecuteNonQuery, but I need to return it as a string 2015SP00001. Any help is appreciated, or just point me in the right direction. Thank you!

推荐答案

如果 AddInParameter 和 AddOutParameter，则看不到实际的实现看起来问题出在

Not seeing the actual implementations if AddInParameter and AddOutParameter it looks like the problem is in

db.AddOutParameter(dbCommand, "@NewIDOUT", DbType.Int32, NewIDOUT);

应该是

Should it be

db.AddOutParameter(dbCommand, "@NewIDOUT", DbType.String, NewIDOUT);

同样适用对于你的方法的参数，它可能应该被标记为out。

The same applies to the parameters of your method and it probably should be marked as out.

我替换了结果行，返回输出并指定了返回数据的大小，并且它有效。

return（string）db.GetParameterValue（dbCommand，@ NewIDOUT）;

I replaced the result line with returning the output and specified the size of return data, and it worked.
return (string)db.GetParameterValue(dbCommand, "@NewIDOUT");

这篇关于将数据类型varchar转换为int时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！