February 19, 2015 at 4:30 pm
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://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)
(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
February 19, 2015 at 7:35 pm
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