SP & Function don't give "Error converting data type varchar to bigint" but once in the ASP.NET app they do

  • 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...

    -----------------------------------------------------------------------------------

    (1)

    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").

    (2)

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

    (3)

    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

  • Quick thought, the reason for passing parameters as string works in SQL is the implicit type casting it performs. This does not exist in VB/C# and therefore the data type of the variables must match the datatype of the parameters.

    😎

    As an example, let's say you are passing a value of zero (0), the SplParameterCollection expects (here in hex) 0x00000000 but when using the string value of "0", it is getting 0x00000030.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply