Wrong return values using a stored procedure

4 posts, 0 answers
  1. Frank Schürer
    Frank Schürer avatar
    13 posts
    Member since:
    Aug 2009

    Posted 27 Oct 2010 Link to this post

    Hi,

    i am using stored procedures in my application and if i call a specific stored procedure the results are not the same as IN SSMS.
    The weird thing is that only specific columns are not returned.

    This is my code

    Public Shared Function spMEDIENSELEKTIONPREMIUM(ByVal scope As IObjectScope,ByVal iNPUTTABLE As String,ByVal mEDIENGATTUNGEN As String,ByVal sTREUARTEN As String,ByVal eRSCHEINRHYTHMEN As String,ByVal eRSCHEINTAGE As String,ByVal eRFAHRUNGSWERTE As String,ByVal rANG As Nullable(Of SByte)) As IQueryResult
        Dim query As IQuery = scope.GetSqlQuery("[sp_MEDIENSELEKTION_PREMIUM] ?,?,?,?,?,?,?", Nothing, "VARCHAR INPUTTABLE,VARCHAR MEDIENGATTUNGEN,VARCHAR STREUARTEN,VARCHAR ERSCHEINRHYTHMEN,VARCHAR ERSCHEINTAGE,VARCHAR ERFAHRUNGSWERTE,TINYINT RANG")
        Dim res As IQueryResult = query.Execute(New Object() {iNPUTTABLE, mEDIENGATTUNGEN, sTREUARTEN, eRSCHEINRHYTHMEN, eRSCHEINTAGE, eRFAHRUNGSWERTE, rANG})
        Dim count As Integer = res.Count 'executes the query
     
        Return res
    End Function

    Selection = StoredProcedure.spMEDIENSELEKTIONPREMIUM(m_mcScope, InputTable, Chr(34) & MediaTypes & Chr(34), Chr(34) & DistributionTypes & Chr(34), AppearRhythms, AppearDays, ExperienceRatings, 1)
     
    Dim Results As New Collection
     
    For Each obj As Object In Selection
        str = ""
        For i As Integer = LBound(obj) To UBound(obj)
            If i <> 0 Then
                str &= obj(i) & "|"
            End If
        Next
        Results.Add(str, obj(0))
    Next

    The obj-Variable contains the correct number of columns returned by the stored procedure, but they are always null.

    This is the result if i call the stored procedure in query analyzer
    63741 576 Prima Sonntag (Aschaffenburg)
    63826 4675
    Stadtzeitung (Aschaffenburg)
    ID      MID     MNAME
    3741    576     Newspaper1
    3826    4675    Newspaper2

    This is what i get inside my application

    schaffenburg)
    ID      MID     MNAME
    3741    null    null
    3826    null    null

    Any help would be really appreciated.
    Thank you.
    Public Shared FunctiospMEDIENSELEKTIONSTANDARD(ByVal scope As IObjectScope,ByVal sELEKTIONSTYP As Nullable(Of Integer),ByVal sUCHTEXT_MEDIUM As String,ByVal sUCHTEXT_GEBIET As String,ByVal mEDIENGATTUNGEN As String,ByVal sTREUARTEN As String,ByVal eRSCHEINRHYTHMEN As String,ByVal eRSCHEINWEISEN As String,ByVal eRFAHRUNGSWERTE As String,ByVal gEBIETSEBENE As String,ByVal gEBIETSFILTER As String) As IQueryResult
            Dim query As IQuery = scope.GetSqlQuery("[sp_MEDIENSELEKTION_STANDARD] ?,?,?,?,?,?,?,?,?,?", Nothing, "INTEGER SELEKTIONSTYP,VARCHAR SUCHTEXT_MEDIUM,VARCHAR SUCHTEXT_GEBIET,VARCHAR MEDIENGATTUNGEN,VARCHAR STREUARTEN,VARCHAR ERSCHEINRHYTHMEN,VARCHAR ERSCHEINWEISEN,VARCHAR ERFAHRUNGSWERTE,VARCHAR GEBIETSEBENE,VARCHAR GEBIETSFILTER")
      
    Public Shared Function spMEDIENSELEKTIONPREMIUM(ByVal scope As IObjectScope,ByVal iNPUTTABLE As String,ByVal mEDIENGATTUNGEN As String,ByVal sTREUARTEN As String,ByVal eRSCHEINRHYTHMEN As String,ByVal eRSCHEINTAGE As String,ByVal eRFAHRUNGSWERTE As String,ByVal rANG As Nullable(Of SByte)) As IQueryResult
        Dim query As IQuery = scope.GetSqlQuery("[sp_MEDIENSELEKTION_PREMIUM] ?,?,?,?,?,?,?", Nothing, "VARCHAR INPUTTABLE,VARCHAR MEDIENGATTUNGEN,VARCHAR STREUARTEN,VARCHAR ERSCHEINRHYTHMEN,VARCHAR ERSCHEINTAGE,VARCHAR ERFAHRUNGSWERTE,TINYINT RANG")
        Dim res As IQueryResult = query.Execute(New Object() {iNPUTTABLE, mEDIENGATTUNGEN, sTREUARTEN, eRSCHEINRHYTHMEN, eRSCHEINTAGE, eRFAHRUNGSWERTE, rANG})
        Dim count As Integer = res.Count 'executes the query
     
        Return res
    End Function

  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 01 Nov 2010 Link to this post

    Hello Frank Schürer,

     In order to work with out parameters you will have to write some additional code in the method that executes your stored procedure. For example if you have an out parameter that represents a category id you will have to do something similar to that:

    public object[] TenMostExpensiveProducts(refint? categoryID)
            {
                SqlParameter parameterCategoryID = newSqlParameter("CategoryID", OpenAccessType.Int32, ParameterMode.InOut);
                List<SqlParameter> sqlParameters = newList<SqlParameter>()
                {
                    parameterCategoryID
                };
      
                IDictionary<string, object> outParameters = newDictionary<string, object>();
      
                object[] queryResult = this.ExecuteStoredProcedure<object>("'Ten Most Expensive Products' ?", sqlParameters,outoutParameters, categoryID);
                categoryID = (int?)outParameters["CategoryID"];
                returnqueryResult;
            }


    Best wishes,
    Petar
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Frank Schürer
    Frank Schürer avatar
    13 posts
    Member since:
    Aug 2009

    Posted 02 Nov 2010 Link to this post

    Hello Petar,

    i believe i didn't express myself very well.

    The problem does not have to do with specific out parameters. It's just an iqueryresult of a stored procedure.

    If i just insert a select * from tablename where ... at the end of the stored procedure everything is working fine. I get all the columns that are inside of the table irregardless how many. And i do not have to write specific code for this different result.

    But if i work with the following select statement at the end of the stored procedure i am experiencing the implied problem.

    EXEC('select ' + @OUTPUTTABLE + '.PLZ, ' + @OUTPUTTABLE + '.MEDIUM_ID AS MID, MEDIEN.MEDIUM_NAME AS MNAME From ' + @OUTPUTTABLE + ' LEFT JOIN MEDIEN ON ' + @OUTPUTTABLE + '.MEDIUM_ID = MEDIEN.MEDIUM_ID')

    I get the PLZ-column correctly but the columns MID and MNAME are both Null in every record that is returned.

    Do i have anything to declare in order to get the complete results of the stored procedure?
  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 04 Nov 2010 Link to this post

    Hello Frank Schürer,

     I suggest we continue our conversation in the support ticked you opened. I will post here once the issue is resolved for the rest of the community to benefit should someone encounters the same problem.

    Kind regards,
    Petar
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top