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]
@DateOpened as datetime,
@CaseType as int,
@NewIDOUT varchar(12) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @InvType char(2)
DECLARE @NewID varchar(12)
DECLARE @NextNum char(5)
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
SET @NewIDOUT = @NewID;
END
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");
//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!