InvalidCastException trying to return result of scalar function in OA

Thread is closed for posting
3 posts, 0 answers
  1. Lisa
    Lisa avatar
    16 posts
    Member since:
    Nov 2013

    Posted 28 Apr 2014 Link to this post

    I added a stored procedure to my OA data model and generated a domain method for it. The stored procedure returns an Integer result.
    Here is the auto generated code:

    Public FunctionSpGetCurrentWeight(ByVal fillStation As System.Nullable(Of System.Char)) As Int64 Implements IWWModelUnitOfWork.SpGetCurrentWeight
           Dim returnValue As Integer
           Return SpGetCurrentWeight(fillStation, returnValue)
    End Function

    Public Function SpGetCurrentWeight(ByVal fillStation As System.Nullable(Of System.Char), ByRef returnValue As Integer) As Int64 Implements IWWModelUnitOfWork.SpGetCurrentWeight
                  Dim parameterReturnValue AsNew OAParameter()
                  parameterReturnValue.Direction= ParameterDirection.ReturnValue
                  parameterReturnValue.ParameterName= "parameterReturnValue"

                  Dim parameterFillStation As New Telerik.OpenAccess.Data.Common.OAParameter
                  parameterFillStation.ParameterName = "FillStation"
                  If fillStation.HasValue Then
                         parameterFillStation.Value= fillStation.Value
                         parameterFillStation.DbType= DbType.String
                         parameterFillStation.Value= DBNull.Value
                  End If
                  Dim queryResult As Int64= Me.ExecuteScalar(Of Int64)("[dbo].[spGetCurrentWeight]", CommandType.StoredProcedure, parameterFillStation,

                  If parameterReturnValue.Value Is DBNull.Value Then
                         returnValue= -1
                         returnValue= DirectCast(parameterReturnValue.Value, Integer)
                  End If      

                  Return queryResult
           End Function

    I created the following function to get the result:

    Imports WonderWareDataModel
    Module WWDataLookups

        Public Function GetWeightFromWonderware(strBay As String) As Int64
            Dim lngWeight As Int64 = -999
                Using model As New WonderWareDataModel.WWModel               
                       lngWeight = model.SpGetCurrentWeight(strBay)
                End Using
            End Try
            Return lngWeiight
        End Function
    End Module

    WHenever I call my function  GetWeightFromWonderware in my asp page codebehind, an error is thrown in the generated code SpGetCurrentWeight. It is an InvalidCastException at the line Dim queryResult As Int64=Me.ExecuteScalar(Of Int64)("[dbo].[spGetCurrentWeight]",CommandType.StoredProcedure, parameterFillStation, parameterReturnValue).

    Can anyone spot what the error is. The sp does return an integer value for the "strBay" parameter that I am testing with.

    Any help would be greatly appreciated. 

  2. Lisa
    Lisa avatar
    16 posts
    Member since:
    Nov 2013

    Posted 30 Apr 2014 in reply to Lisa Link to this post

    Still struggling with this...Manually modified the generated function to return an object instead of an int and was able to see that the sp was returning a value of 0.  Modified again to return a double and it still worked. Does not work as an int though even though the tooltip over the returned value say " 0 (int)".
    I can live with assigning it as a double instead of an integer even though I don't understand why. However the returned value should be 3264, not 0.  I thought perhaps it was returning the error code so I modified the sp to also set an output parameter and then regenerated my model function to pick that up instead. It still gets 0 as the return parameter and 0 as the output parameter. I know for a fact, the sp is not returning zero.
    So why can't I get the correct value back? What am I doing wrong? Or is there a bug in open access?
  3. Kaloyan Nikolov
    Kaloyan Nikolov avatar
    118 posts

    Posted 01 May 2014 Link to this post

    Hello Lisa,

    The value returned by the generated domain method is meant to return the number of affected records. In your case I would suppose you need to map the stored procedure with ResultType: None (see the attached image). 

    You can access your return value with a overload of the domain method with an out parameter named returnValue, like the code below:
    Using context = New EntitiesModel1()
        Dim returnCode As Integer
    End Using

    You can see the above store procedure mapping in the attached sample project.

    I hope this helps. Should you have any further questions do not hesitate to get back to us. 

    Kaloyan Nikolov
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top