Error converting data type varchar to bigint ocurring the asp.net app but not in the SP or Function...any ideas ?

[George Lewycky]

I am trying to pass in 5 values to validate against from reading a text file being read in using XML.
Then record by record pass the 5 parms and receive a string of errors as an OUTPUT parm if any per record and then into the gridview for the user to pre-view validation errors up front using the function through the SP.


The Stored Proc (which calls the function) works fine as show below.
Along with the Function (standalone) !!

1. SP_PrevalidSchoolFunction '02488','26357910AA','26357915GG','W$','YY',''
2. SELECT dbo.Function_ValidateSchoolParms('02488','26357910AA','26357915GG','W$','YY')


BUT: when running in the ASP 3.5 app it fails running the SP with:
{"Error converting data type varchar to bigint."}


Public Sub PrevalidSchoolCodeRecords(ByVal SCHOOL_CODE As String, ByVal STARTING_MC_SERIAL_NO As String, ByVal ENDING_MC_SERIAL_NO As String, ByVal DOE_CODE As String, ByVal SEMESTER_CODE As String, ByVal ERROR_MESSAGE As String)
' Public Function PrevalidSchoolCodeRecords(ByVal SCHOOL_CODE As String, ByVal STARTING_MC_SERIAL_NO As String, ByVal ENDING_MC_SERIAL_NO As String, ByVal DOE_CODE As String, ByVal SEMESTER_CODE As String, ByVal ERROR_MESSAGE As String)
' PrevalidSchoolCodeRecords(ByVal CSchoolCodeRecordsR As String, ByVal error_message As String)
' Public Sub PrevalidSchoolCodeRecords(ByVal SCHOOL_CODE As String, ByVal STARTING_MC_SERIAL_NO As String, ByVal ENDING_MC_SERIAL_NO As String, ByVal DOE_CODE As String, ByVal SEMESTER_CODE As String, ByVal ERROR_MESSAGE As String)
Try
If cn.State <> ConnectionState.Open Then
cn.Open()
End If

cmd.CommandText = "SP_PrevalidSchoolFunction"
cmd.Parameters.Clear()
cmd.CommandTimeout = 0
cmd.Parameters.AddWithValue("@SCHOOL_CODE", SCHOOL_CODE)
cmd.Parameters.AddWithValue("@STARTING_MC_SERIAL_NO", STARTING_MC_SERIAL_NO)
cmd.Parameters.AddWithValue("@ENDING_MC_SERIAL_NO", ENDING_MC_SERIAL_NO)
cmd.Parameters.AddWithValue("@DOE_CODE", DOE_CODE)
cmd.Parameters.AddWithValue("@SEMESTER_CODE", SEMESTER_CODE)
cmd.Parameters.AddWithValue("@ERROR_MESSAGE", "") ' out variable

cmd.ExecuteScalar() <----------- varchar/int exception !!!

CloseConnection(cn)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Sub



Msg 245, Level 16, State 1, Procedure SP_PrevalidSchoolFunction, Line 15
Conversion failed when converting the varchar value ' Starting Serial Number must be numeric | Ending Serial Number must be numeric | Invalid Semester Code | Invalid DOE Card Type | ' to data type int.




After alot of researching I found cases ranging from datatypes changing upon the concatenation of the error messages to casting the parms as varchars in the function directly but no luck whatsoever...
-----------------------------------------------------------------------------------
https://social.technet.microsoft.com/Forums/en-US/f7161566-aea6-46b7-b86b-1bab110abe28/conversion-failed-when-converting-varchar-to-int?forum=transactsql

https://answers.yahoo.com/question/index?qid=20070209114659AAR24ai
The problem is that you are trying to concatenate the results of a hard-coded string and the return of a "subroutine"
and not the return of a "function." Change "Sub expenses()" to "Function expenses() as Double". do the same for
"sub meals50()" and change to "Function meals50() as Double".

The point is that the "Expression does not produce a value" is correct: subroutines do not return values
(unless you are passing parameters to them and having the parameters passed "ByRef").




https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors

to suggesting casting the parms as varchars when calling the function as:
https://www.linkedin.com/groups/Identifying-Dealing-Special-Characters-in-86080.S.217816491

set @ERROR_MESSAGE_RETURN = Faremedia.dbo.Function_ValidateSchoolParms(cast(@SCHOOL_CODE as varchar),cast(@STARTING_MC_SERIAL_NO as varchar),cast(@ENDING_MC_SERIAL_NO as varchar),cast(@DOE_CODE as varchar),cast(@SEMESTER_CODE as varchar))

--set @ERROR_MESSAGE_RETURN = Faremedia.dbo.Function_ValidateSchoolParms(@SCHOOL_CODE,@STARTING_MC_SERIAL_NO,@ENDING_MC_SERIAL_NO,@DOE_CODE,@SEMESTER_CODE)


Any ideas, suggestions, references would be appreciated

Thanks

George

[Erland Sommarskog]

Post by George Lewycky
I am trying to pass in 5 values to validate against from reading a text
file being read in using XML.
Then record by record pass the 5 parms and receive a string of errors as
an OUTPUT parm if any per record and then into the gridview for the
user to pre-view validation errors up front using the function through
the SP.

...

Post by George Lewycky
Msg 245, Level 16, State 1, Procedure SP_PrevalidSchoolFunction, Line 15
Conversion failed when converting the varchar value

Please post the code for the procedure. As it says line 15, there is
apparently a problem in the procedure.

Also, be aware of that the sp_ prefix is reserved for system stored
procedures, and SQL Server first looks for these in the master database.

[George Lewycky]

USE [FareMedia]
GO
/****** Object: StoredProcedure [dbo].[SP_PrevalidSchoolMCRangesParm] Script Date: 02/19/2015 11:49:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_PrevalidSchoolFunction]( @SCHOOL_CODE varchar( 15 ) ,
@STARTING_MC_SERIAL_NO varchar( 15 ) ,
@ENDING_MC_SERIAL_NO varchar( 15 ) ,
@DOE_CODE varchar( 2 ) ,
@SEMESTER_CODE varchar( 2 ),
@ERROR_MESSAGE VARCHAR(3000) OUTPUT )
AS
BEGIN
SET NOCOUNT ON
DECLARE @ERROR_MESSAGE_RETURN as varchar(3000)

-- https://www.linkedin.com/groups/Identifying-Dealing-Special-Characters-in-86080.S.217816491

set @ERROR_MESSAGE_RETURN = Faremedia.dbo.Function_ValidateSchoolParms(cast(@SCHOOL_CODE as varchar),cast(@STARTING_MC_SERIAL_NO as varchar),cast(@ENDING_MC_SERIAL_NO as varchar),cast(@DOE_CODE as varchar),cast(@SEMESTER_CODE as varchar))

--set @ERROR_MESSAGE_RETURN = Faremedia.dbo.Function_ValidateSchoolParms(@SCHOOL_CODE,@STARTING_MC_SERIAL_NO,@ENDING_MC_SERIAL_NO,@DOE_CODE,@SEMESTER_CODE)

SET @ERROR_MESSAGE = @ERROR_MESSAGE_RETURN
SELECT CONVERT(VARCHAR(3000),@ERROR_MESSAGE)
--RETURN cast(@ERROR_MESSAGE as varchar(max))

END;
-- SP_PrevalidSchoolFunction '02488','26357910AA','26357915GG','W$','YY','' ----- SP OK
-- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','26357910AA','26357915GG','W$','YY') ---- FUNCTION OK

[George Lewycky]

USE [FareMedia]
GO
/****** Object: UserDefinedFunction [dbo].[Function_ValidateSchoolParms] Script Date: 02/19/2015 15:54:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Function_ValidateSchoolParms]( @SCHOOL_CODE varchar( 15 ) ,
@FROM_RANGE varchar( 15 ) ,
@TO_RANGE varchar( 15 ) ,
@DOE_CODE varchar( 2 ) ,
@SEMESTER_CODE varchar( 2 ))
RETURNS varchar( 4000 )
AS
BEGIN

DECLARE
@DocHandle int;

DECLARE
@SCHOOLCODE varchar( 15 );

DECLARE
@DOECODE varchar( 2 );
DECLARE
@SEMESTERCODE varchar( 2 ) ,
@TEXTFILENAME varchar( 200 );
DECLARE
@UPLOADDATE datetime;
DECLARE
@MSG varchar( 4000 ) = '';
DECLARE
@IDX AS integer;
DECLARE
@ErrorMessage nvarchar( 4000 );
DECLARE
@ErrorSeverity int;
DECLARE
@ErrorState int;
DECLARE
@ErrorFlag bit = 0;
DECLARE
@SchoolCodeOther bigint;

-------added Lewycky Jan 2015 to accomodate all records identified instead of just first
DECLARE
@ERROR_MESSAGE_CONCAT varchar( 4000 ) = ''; ---- concatenate each error to store as single record
DECLARE
@ERROR_MESSAGE_COUNT smallint = 0; ---- tally count while looping each record counting total errors per incoming record

DECLARE
@ERROR_MESSAGE_RETURN varchar( 2000 ) = '';

DECLARE
@RangeErrorFlag bit = 0; --- added to override some conditions once the from and/or are deemed nonumeric

SET @ErrorFlag = 0;
SET @RangeErrorFlag = 0;

-------added Lewycky Jan 2015 to accomodate all records identified instead of just first
SET @ERROR_MESSAGE_CONCAT = ''; ---- concatenate each error to store as single record
SET @ERROR_MESSAGE_COUNT = 0; ---- tally count while looping each record counting
----- total errors per incoming record

--SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' error ' + ' | '
--SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1

BEGIN
BEGIN
IF ISNUMERIC( @SCHOOL_CODE )
<>
1
BEGIN

SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' School Code must be numeric ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;

END;

IF ISNUMERIC( @FROM_RANGE )
<>
1
BEGIN
SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' Starting Serial Number must be numeric ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;
SET @RangeErrorFlag = 1;
END;

IF ISNUMERIC( @TO_RANGE )
<>
1
AND @ErrorFlag = 0
BEGIN
SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' Ending Serial Number must be numeric ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;
SET @RangeErrorFlag = 1;
END;
END;
BEGIN
IF NOT EXISTS( SELECT *
FROM SEMESTER_CODES
WHERE SEMESTER_CODE
=
@SEMESTER_CODE )
BEGIN
SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' Invalid Semester Code ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;
END;


IF NOT EXISTS( SELECT *
FROM SCHOOL_CARD_TYPES
WHERE SUBSTRING( DOE_CARD_TYPE ,1 ,2 )
=
@DOE_CODE ) ------ why varchar 5
BEGIN
SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' Invalid DOE Card Type ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;
END;
END;


IF @RangeErrorFlag
=
0
BEGIN
IF CONVERT( bigint ,@TO_RANGE )
=
0
OR CONVERT( bigint ,@FROM_RANGE )
=
0
BEGIN
SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' Serial number range contains zeros ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;
END;
END;


IF @RangeErrorFlag
=
0
BEGIN
IF EXISTS( SELECT *
FROM SCHOOL_CODE_MC_RANGES --_UPLOAD_TEST
WHERE SCMR_SCHOOL_CODE
=
CONVERT( bigint ,@SCHOOL_CODE )
AND CONVERT( bigint ,@FROM_RANGE )BETWEEN SCMR_STARTING_MC_SERIAL_NO AND SCMR_ENDING_MC_SERIAL_NO
OR SCMR_SCHOOL_CODE
=
CONVERT( bigint ,@SCHOOL_CODE )
AND CONVERT( bigint ,@TO_RANGE )BETWEEN SCMR_STARTING_MC_SERIAL_NO AND SCMR_ENDING_MC_SERIAL_NO )
BEGIN
SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' This range of serial numbers has already been uploaded for school code = ' + CONVERT(varchar(10),@SCHOOLCODE) + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;
END;
END;


IF @RangeErrorFlag
=
0
BEGIN
IF EXISTS( SELECT *
FROM SCHOOL_CODE_MC_RANGES --_UPLOAD_TEST
WHERE SCMR_SCHOOL_CODE
!=
CONVERT( bigint ,@SCHOOL_CODE )
AND CONVERT( bigint ,@FROM_RANGE )BETWEEN SCMR_STARTING_MC_SERIAL_NO AND SCMR_ENDING_MC_SERIAL_NO
OR SCMR_SCHOOL_CODE
!=
CONVERT( bigint ,@SCHOOL_CODE )
AND CONVERT( bigint ,@TO_RANGE )BETWEEN SCMR_STARTING_MC_SERIAL_NO AND SCMR_ENDING_MC_SERIAL_NO )
BEGIN
SET @SchoolCodeOther = ( SELECT TOP 1 scmr_school_code
FROM SCHOOL_CODE_MC_RANGES --_UPLOAD_TEST
WHERE SCMR_SCHOOL_CODE
!=
CONVERT( bigint ,@SCHOOL_CODE )
AND CONVERT( bigint ,@FROM_RANGE )BETWEEN SCMR_STARTING_MC_SERIAL_NO AND SCMR_ENDING_MC_SERIAL_NO
OR SCMR_SCHOOL_CODE
!=
CONVERT( bigint ,@SCHOOL_CODE )
AND CONVERT( bigint ,@TO_RANGE )BETWEEN SCMR_STARTING_MC_SERIAL_NO AND SCMR_ENDING_MC_SERIAL_NO );

--select @SchoolCodeOther

SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' This range of serial numbers has already been uploaded for school code = ' + CONVERT( varchar(10) ,@SchoolCodeOther ) + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;


END;
END;


IF @RangeErrorFlag
=
0
BEGIN
IF @TO_RANGE
<
@FROM_RANGE
BEGIN

SET @ERROR_MESSAGE_CONCAT = @ERROR_MESSAGE_CONCAT + ' Ending serial number is > starting serial number ' + ' | ';
SET @ERROR_MESSAGE_COUNT = @ERROR_MESSAGE_COUNT + 1;

END;
END;


-------------------------------------------------------------------------------------------------
-- Margaret: when is SCHOOL_CODE_DUPLICATE not zero ???? not in use ?????
-- Margaret: when is SCHOOL_CODE_DUPLICATE not zero ???? not in use ?????
-- Margaret: when is SCHOOL_CODE_DUPLICATE not zero ???? not in use ?????
-------------------------------------------------



IF LEN( @ERROR_MESSAGE_CONCAT )
1
BEGIN
--SELECT @ERROR_MESSAGE_RETURN , @ERROR_MESSAGE_CONCAT;
SET @ERROR_MESSAGE_RETURN = CAST( @ERROR_MESSAGE_CONCAT AS varchar( 4000 ));
END;


-- IF @ERROR_MESSAGE_COUNT
-- =
-- 0
--or LEN( @ERROR_MESSAGE_CONCAT )
-- =
-- 0 OR @ERROR_MESSAGE_CONCAT is null
IF LEN(@ERROR_MESSAGE_CONCAT) = 0
BEGIN
--SELECT @ERROR_MESSAGE_RETURN , @ERROR_MESSAGE_CONCAT;
SET @ERROR_MESSAGE_RETURN = CAST('NONE' AS VARCHAR); -- CAST( @ERROR_MESSAGE_CONCAT AS varchar( max ));


END;
RETURN @ERROR_MESSAGE_RETURN;
END;



END;
--SELECT @ERROR_MESSAGE_RETURN , @ERROR_MESSAGE_CONCAT;




--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','2635791000','26357915T0','W$','YY')
--- Faremedia.dbo.Function_ValidateSchoolParms '02488','26357910AA','26357915GG','W$','YY'
-- SP_PrevalidSchoolMCRangesParm '02488','26357910AA','26357915GG','W$','YY',''

--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('024T8','26357910AA','26357915GG','CT','02') --ok
--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('024T8','26357910AA','26357915GG','00','00') --ok inv doe
--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','2635791000','2635791500','CT','02') -- all ok
--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','2635791000','2635791500','00','CT') -- sem & doe ok
--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','2635791000','2635791500','CT','00') -- all ok

--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','2635791000','2635791500','00','00') -- inv doe

--- SELECT Faremedia.dbo.Function_ValidateSchoolParms('02488','2635791012','2635791012','W$','YY') -- sem & doe ok


-- GRANT EXECUTE ON Function_ValidateSchoolParms TO Public

[Erland Sommarskog]

<>
1

This is the problem. isnumeric() returns 1 if the value can be converted
to *any* numeric datatype.

For instance, these calls bomb (when I comment out the check on @DOE_CODE
and @SEMESTER_CODE, since I don't have those tables).

SELECT dbo.[Function_ValidateSchoolParms]('2.1', '1.2', '1.2', 'AA', 'AA')
SELECT dbo.[Function_ValidateSchoolParms]('', '5E0', '$2', 'AA', 'AA')

If you are on SQL 2012 or later, use try_convert which returns NULL if
conversion fails.

Else use this test:

ltrim(rtrim(@val)) NOT LIKE '%[^0-9]%' AND @val LIKE '%[0-9]%'

That is @val must not include any values outside the range 0-9 and it
must include at least one digit.

Unfortunately, the error is reported at the point where the function is
call and not the place in the function where it fails.

[George Lewycky]

I'm taking your suggestion and removed the SP refix and I'm testing it now