通过VB.net应用程序调用存储过程不起作用

我有一个调用数据库存储过程的VB.Net应用程序，它应该假设在状态为“等待”的表中查找任务，然后将其更新为执行并生成报告。

但是，我已经在Visual Studio中运行了这一步，但它似乎不运行存储过程或运行存储过程而没有结果。

我已经单独通过SQL Developer运行存储过程来检查它并且它可以工作，所以我不认为这是问题所在。

我发现rsresult从来没有行，所以我想知道是否需要在ExecuteReader之后添加另一行代码？

你能帮忙吗？

以下是函数和存储过程

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

这是RunSPReturnRS方法：

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

---

这里几乎没有...但是...如果存储过程只返回一行，那么你永远不会看到结果。

您需要在While循环中使用rsResult.Read ：

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

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

上一篇: Called to stored procedure through VB.net application not working

下一篇: Troubleshooting SQL Stored Procedure