Called to stored procedure through VB.net application not working

I have a VB.Net application that calls the database stored procedure and it supposedly suppose to look for tasks in a table with the Status of "Wait" and then updates it to executing and then generates the report.

However, I have ran the step in Visual Studio but it seems it doesn't to run the store procedure or run the stored procedure with no result .

I have individually ran the stored procedure through SQL Developer to check it and it works, so I don't think it's the problem.

I find that the rsresult never has rows so I am wondering do I need to add another line of code after ExecuteReader ?

Can you all please help?

Below is the function and stored procedure

Public Function SelectGetTasktoExec(ByVal plngCount As Integer, ByVal 
pstrIPAddr As String, ByRef pdicResult As Dictionary) As Boolean
    Dim result As Boolean = False
    Dim blnResult As Boolean
    Dim strCaller As String = ""
    Dim strErrMsg As String = ""
    Dim lngRet As Integer
    Dim rsResult As OracleDataReader = Nothing
    Dim dicItem As Dictionary
    Dim intIndex As Integer

    Try
        m_TranObj.CreateSPCaller("PKG_TD_BATCH_REPORT.SELECT_REPRINT_TASK")
        m_TranObj.AddSPParams("i_task_count", OracleDbType.Decimal, 10, plngCount, ParameterDirection.Input)
        m_TranObj.AddSPParams("i_ipaddr", OracleDbType.Varchar2, 16, pstrIPAddr, ParameterDirection.Input)
        m_TranObj.AddSPParams("ocs_name", OracleDbType.RefCursor, 20, Nothing, ParameterDirection.Output)
        m_TranObj.AddSPParams("o_err_code", OracleDbType.Decimal, 20, lngRet, ParameterDirection.Output)

        If Not m_TranObj.RunSPReturnRS(lngRet, "o_err_code", rsResult) Then
            strErrMsg = "call Pkg_Td_Batch_Report.SELECT_REPRINT_TASK failed."
            Throw New Exception()
        End If

        If lngRet <> 0 Then
            strErrMsg = "Call Pkg_Td_Batch_Report.SELECT_REPRINT_TASK failed,Error code:" & CStr(lngRet)
            Throw New Exception()
        End If

        intIndex = gc_DicFirstKey

        rsResult.Read()
        While rsResult.HasRows()
            dicItem = New Dictionary

            dicItem.Add(gc_KEY_TASK_NO, rsResult("TASK_NO") & "")
            dicItem.Add(gc_KEY_QUEUE_NO, rsResult("QUEUE_NO") & "")
            dicItem.Add(gc_KEY_START_DATE, rsResult("START_DATE") & "")
            dicItem.Add(gc_KEY_END_DATE, rsResult("END_DATE") & "")
            dicItem.Add(gc_KEY_STORAGE_PATH, rsResult("STORAGE_PATH") & "")
            dicItem.Add(gc_KEY_DATA_SOURCE, rsResult("DATA_SOURCE") & "")
            dicItem.Add(gc_KEY_TEMPLATE_NAME, rsResult("TEMPLATE_NAME") & "")
            dicItem.Add(gc_KEY_SOFT_COPY_FORMATS, rsResult("SOFT_COPY_FORMATS") & "")
            dicItem.Add(gc_KEY_SCHEDULED_EXECUTE_DATE, rsResult("SCHEDULED_EXECUTE_DATE") & "")
            dicItem.Add(gc_KEY_HARD_DISTRIBUTION_IND, rsResult("PRINT_IND") & "")
            dicItem.Add(gc_KEY_SOFT_DISTRIBUTION_IND, rsResult("EXPORT_IND") & "")
            dicItem.Add(gc_KEY_RESULT_PATH, rsResult("RESULT_PATH") & "")
            dicItem.Add(gc_KEY_PRINTER_NAME, rsResult("PRINTER_NAME") & "")
            dicItem.Add(gc_KEY_TRACTOR_NO, rsResult("TRACTOR_NO") & "")
            dicItem.Add(gc_KEY_TEMPLATE_NO, rsResult("TEMPLATE_NO") & "")
            dicItem.Add(gc_KEY_DUPLEX_PRINT_IND, rsResult("DUPLEX_PRINT_IND") & "")
            dicItem.Add(gc_KEY_DESCRIPTION, rsResult("DESCRIPTION") & "")
            dicItem.Add(gc_KEY_DEPT_DIVISION_CODE, rsResult("DEPT_DIVISION_CODE") & "")
            dicItem.Add(gc_KEY_SYSDATE, Strings.Format(rsResult("SYSDATE"), gc_FormatDateTime) & "")
            dicItem.Add(gc_KEY_FROM_PAGE, rsResult("FROM_PAGE") & "")
            dicItem.Add(gc_KEY_TO_PAGE, rsResult("TO_PAGE") & "")

            'add end
            pdicResult.Add(intIndex, dicItem)
            intIndex += 1

        End While
        SBL_Error.DebugLog(strCaller, "End")
        blnResult = True

    Catch excep As System.Exception
        blnResult = False
        SBL_Error.ErrorLog(strCaller, strErrMsg & excep.ToString)
        Throw excep
    Finally
        result = blnResult
    End Try
    Return result
End Function

Here is the RunSPReturnRS method:

Public Function RunSPReturnRS(ByRef plngCnt As Integer, ByVal pstrReturnName 
As String, ByRef prsResult As Object) As Boolean
    Dim result As Boolean = False
    Dim blnResult As Boolean
    Dim strCaller As String = ""
    Dim strErrMsg As String = ""
    Dim strMsg As String = ""
    Dim rsresult As String = ""

    Try
        If Not mblnConnected Then
            If Not Connect() Then
                strErrMsg = "Can not open connection!"
            End If
        End If

        prsResult = mCmd.ExecuteReader()

        If prsResult.HasRows Then
            prsResult.Read()
            prsResult = prsResult(0).ToString()
            strMsg = "Batch Date is" + Space(1) + prsResult
        Else
            prsResult = prsResult
        End If

        If pstrReturnName Is "" Then
            plngCnt = mCmd.Parameters(pstrReturnName).Value
        End If

        mCmd.Dispose()
SBL_Error.DebugLog(strCaller, strMsg)
        blnResult = True

    Catch ex As Exception
        SBL_Error.ErrorLog(strCaller, ex.ToString())
        blnResult = False
    Finally
        result = blnResult
    End Try
    Return result

---

There's little to go on here... but... if the stored procedure only returns a single row then you'd never see the result.

You need the rsResult.Read within the While loop:

While rsResult.HasRows
    rsResult.Read
    '  do your processing...
End While

链接地址: http://www.djcxy.com/p/71586.html

上一篇: Prics模块系统从WCF服务内部？

下一篇: 通过VB.net应用程序调用存储过程不起作用