Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    54

    Question Unanswered: Problem with VB6 and Oracle 9i ODBC

    I actually got the connection to work by removing the reference to:

    Code:
    cn.Provider = "MSDAORA.1"
    and cleaning up the connection string to be a pure DSN connection with:

    Code:
    cn.connectionstring = "PWD=" & gstrPassword & ";Persist Security Info=True;UID=" & gstrUser & ";DSN=" & gstrDSN
    cn.Open
    The problem now is that I keep getting:

    "Run-time error 3265, ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application."

    ????

    The problem is happening in the function:

    Code:
    Public Function ProcessSales_audit() As Boolean
    'On Error GoTo errhandler
    Dim strSQL As String
    Dim strRec As String
    Dim intseqNum As Integer
    Dim rsdetail As ADODB.Recordset
    Set rsmain = New ADODB.Recordset
    Set rsdetail = New ADODB.Recordset
    Dim iFileNum As Integer
    
    'Get a free file handle
    iFileNum = FreeFile
    
    strSQL = "Select STORE_CD,TERM_NUM,TRN_DT FROM POS_TRN_LN " & _
            " WHERE STORE_CD IN (" & gstrStoresforSQL & ")  AND TRN_DT='" & Format(gstrDate, "dd-mmm-yyyy") & _
            "' AND STAT_CD<>'X' and (void is null or void = 'P')" & _
            " AND LN_TP NOT IN('SUB','TOT','VOD') group by STORE_CD,TERM_NUM,TRN_DT "
    rsmain.Open strSQL, cn, adOpenKeyset, adLockReadOnly
    If Not rsmain.EOF Then
        InitializeAccountArray
        InitializeNameArray
        'If the file is not there, one will be created
        'If the file does exist, this one will
        'overwrite it.
       ' On Error GoTo errfilepath
        Open gstrFilePath For Output As iFileNum
    
       ' On Error GoTo errhandler
        Do While Not rsmain.EOF()
            strSQL = "Select LN_TP,sum(AMT) AMT From POS_TRN_LN" & _
            " where STORE_CD='" & rsmain("STORE_CD") & "' and TERM_NUM='" & _
            rsmain("TERM_NUM") & "' and TRN_DT='" & Format(rsmain("TRN_DT"), "dd-mmm-yyyy") & _
            "' AND STAT_CD<>'X' and (VOID is null or VOID = 'P')" & _
            " and LN_TP not in ('SUB','TOT','VOD') group by LN_TP"
            
            rsdetail.Open strSQL, cn, adOpenStatic, adLockReadOnly
            If Not rsdetail.EOF() Then
                ReInitialise_Qty_Array
                Do While Not rsdetail.EOF()
                    Select Case Trim(UCase(rsdetail("ln_tp")))
                        Case Is = "AMX"
                            garrAmts(CAMEX) = garrAmts(CAMEX) + rsdetail("AMT")
                        Case Is = "ATM"
                            garrAmts(CATM) = garrAmts(CATM) + rsdetail("AMT")
                        Case Is = "CHK"
                            garrAmts(CCHECKS) = garrAmts(CCHECKS) + rsdetail("AMT")
                        Case Is = "CSH"
                            garrAmts(CCASH) = garrAmts(CCASH) + rsdetail("AMT")
                        Case Is = "TCK"
                            garrAmts(CCASH) = garrAmts(CCASH) + rsdetail("AMT")
                        Case Is = "DIS"
                            garrAmts(CDISCOVER) = garrAmts(CDISCOVER) + rsdetail("AMT")
                        Case Is = "GFC"
                            garrAmts(CGIFTS_COUPONS) = garrAmts(CGIFTS_COUPONS) + rsdetail("AMT")
                        Case Is = "ID", "PRO", "TD", "TLD", "VCN", "EMP", "FIR", "IDA", "IDP", "IDS", "MM", "POL", "SEN", "STU", "LNL", "TDD", "TPD"
                            garrAmts(CSALES_DISCOUNTS) = garrAmts(CSALES_DISCOUNTS) - rsdetail("AMT")
                        Case Is = "MED"
                            garrAmts(CCASH) = garrAmts(CCASH) - rsdetail("AMT")
                        Case Is = "MER"
                            garrAmts(CCRS_ISSUED) = garrAmts(CCRS_ISSUED) + rsdetail("AMT")
                        Case Is = "MRR"
                            garrAmts(CCRS_RECIEVED) = garrAmts(CCRS_RECIEVED) + rsdetail("AMT")
                        Case Is = "MSC"
                            garrAmts(CMASTERCARD) = garrAmts(CMASTERCARD) + rsdetail("AMT")
                        Case Is = "PDO"
                            garrAmts(CPAID_OUT) = garrAmts(CPAID_OUT) - rsdetail("AMT")
                        Case Is = "RET"
                            garrAmts(CRETURNS) = garrAmts(CRETURNS) - rsdetail("AMT")
                        Case Is = "SAL"
                            garrAmts(CSALES) = garrAmts(CSALES) - rsdetail("AMT")
                        Case Is = "TAX"
                            garrAmts(CSALES_TAX) = garrAmts(CSALES_TAX) - rsdetail("AMT")
                        Case Is = "VIS"
                            garrAmts(CVISA) = garrAmts(CVISA) + rsdetail("AMT")
                    End Select
                    rsdetail.MoveNext
                Loop
            End If
            rsdetail.Close
            intseqNum = ((Val(rsmain("TERM_NUM") - 1)) * 100) + 1
            For inti = 0 To 14
                strRec = garrAccountNos(inti) & Right(rsmain("STORE_CD"), 3) & ".." & _
                         Format(rsmain("TRN_DT"), "yymmdd") & _
                         ".." & Right(rsmain("STORE_CD"), 2) & _
                         Lpad(rsmain("TERM_NUM"), 4) & ".." & _
                         Lpad(intseqNum, 3) & ".." & _
                         Space(10 - Len(AmtValue(garrAmts(inti)))) & AmtValue(garrAmts(inti)) & _
                         "..POS " & garrNames(inti) & " Store:  " & _
                         Store_Val(rsmain("STORE_CD")) & " Reg:  " & _
                         Right(rsmain("TERM_NUM"), 1) & "  .."
    
                Print #iFileNum, strRec
                intseqNum = intseqNum + 1
            Next
            rsmain.MoveNext
        Loop
        Close iFileNum
    Else
        frmSA.lblProcessing.Caption = ""
        Screen.MousePointer = vbDefault
        MsgBox " No data available for the entered paramters", vbInformation
        ProcessSales_audit = False
        Exit Function
    End If
    rsmain.Close
    ProcessSales_audit = True
    frmSA.lblProcessing.Caption = ""
    Screen.MousePointer = vbDefault
    Exit Function
    errhandler:
        frmSA.lblProcessing.Caption = ""
        Screen.MousePointer = vbDefault
        MsgBox "Errors occured while processing Sales Audit file", vbCritical
        ProcessSales_audit = False
        If rsmain.State = adStateOpen Then
            rsmain.Close
        End If
        If rsdetail.State = adStateOpen Then
            rsdetail.Close
        End If
        Exit Function
    errfilepath:
         frmSA.lblProcessing.Caption = ""
         Screen.MousePointer = vbDefault
         MsgBox " Invalid file name/type, Could not open file - Cannot process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
         ProcessSales_audit = False
         Exit Function
    End Function
    ...the first SQL statement pulls back an array that feeds the second statement. Something in this mechanism is causing the problem though?

  2. #2
    Join Date
    Oct 2003
    Posts
    54

    Question Porblem with VB6 and Oracle 9i ODBC

    I have a VB6 application that was extracting data from an 8i DB but we recently upgraded to Oracle 9i and started having problems with the Oracle ODBC driver (v9.2.0.1.0). We kept getting an error when trying to create the connection to the DB but ther was no Oracle refused to return an error code. The connection orginally looked like:

    Code:
    Public Function OpenDatabaseConnection() As Boolean
    On Error GoTo errhandler
    Dim connectionstring As String
    
        Set cn = New ADODB.Connection
        cn.Provider = "MSDAORA.1"
        cn.connectionstring = "PWD=" & gstrPassword & ";Persist Security Info=True;UID=" & gstrUser & ";DSN=" & gstrDSN
        cn.Open
        OpenDatabaseConnection = True
        Exit Function
    errhandler:
        MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
        OpenDatabaseConnection = False
        Exit Function
    End Function
    I actually got the connection to work by removing the reference to:

    Code:
    cn.Provider = "MSDAORA.1"
    and cleaning up the connection string to be a pure DSN connection with:

    Code:
    cn.connectionstring = "PWD=" & gstrPassword & ";UID=" & gstrUser & ";DSN=" & gstrDSN
    cn.Open

    The problem now is that I keep getting:

    "Run-time error 3265, ADO could not find the object in the collection corresponding to the name or ordinal reference requested by the application."

    ????

    The problem is happening in the function:

    Code:
    Public Function ProcessSales_audit() As Boolean
    'On Error GoTo errhandler
    Dim strSQL As String
    Dim strRec As String
    Dim intseqNum As Integer
    Dim rsdetail As ADODB.Recordset
    Set rsmain = New ADODB.Recordset
    Set rsdetail = New ADODB.Recordset
    Dim iFileNum As Integer
    
    'Get a free file handle
    iFileNum = FreeFile
    
    strSQL = "Select STORE_CD,TERM_NUM,TRN_DT FROM POS_TRN_LN " & _
            " WHERE STORE_CD IN (" & gstrStoresforSQL & ")  AND TRN_DT='" & Format(gstrDate, "dd-mmm-yyyy") & _
            "' AND STAT_CD<>'X' and (void is null or void = 'P')" & _
            " AND LN_TP NOT IN('SUB','TOT','VOD') group by STORE_CD,TERM_NUM,TRN_DT "
    rsmain.Open strSQL, cn, adOpenKeyset, adLockReadOnly
    If Not rsmain.EOF Then
        InitializeAccountArray
        InitializeNameArray
        'If the file is not there, one will be created
        'If the file does exist, this one will
        'overwrite it.
       ' On Error GoTo errfilepath
        Open gstrFilePath For Output As iFileNum
    
       ' On Error GoTo errhandler
        Do While Not rsmain.EOF()
            strSQL = "Select LN_TP,sum(AMT) AMT From POS_TRN_LN" & _
            " where STORE_CD='" & rsmain("STORE_CD") & "' and TERM_NUM='" & _
            rsmain("TERM_NUM") & "' and TRN_DT='" & Format(rsmain("TRN_DT"), "dd-mmm-yyyy") & _
            "' AND STAT_CD<>'X' and (VOID is null or VOID = 'P')" & _
            " and LN_TP not in ('SUB','TOT','VOD') group by LN_TP"
            
            rsdetail.Open strSQL, cn, adOpenStatic, adLockReadOnly
            If Not rsdetail.EOF() Then
                ReInitialise_Qty_Array
                Do While Not rsdetail.EOF()
                    Select Case Trim(UCase(rsdetail("ln_tp")))
                        Case Is = "AMX"
                            garrAmts(CAMEX) = garrAmts(CAMEX) + rsdetail("AMT")
                        Case Is = "ATM"
                            garrAmts(CATM) = garrAmts(CATM) + rsdetail("AMT")
                        Case Is = "CHK"
                            garrAmts(CCHECKS) = garrAmts(CCHECKS) + rsdetail("AMT")
                        Case Is = "CSH"
                            garrAmts(CCASH) = garrAmts(CCASH) + rsdetail("AMT")
                        Case Is = "TCK"
                            garrAmts(CCASH) = garrAmts(CCASH) + rsdetail("AMT")
                        Case Is = "DIS"
                            garrAmts(CDISCOVER) = garrAmts(CDISCOVER) + rsdetail("AMT")
                        Case Is = "GFC"
                            garrAmts(CGIFTS_COUPONS) = garrAmts(CGIFTS_COUPONS) + rsdetail("AMT")
                        Case Is = "ID", "PRO", "TD", "TLD", "VCN", "EMP", "FIR", "IDA", "IDP", "IDS", "MM", "POL", "SEN", "STU", "LNL", "TDD", "TPD"
                            garrAmts(CSALES_DISCOUNTS) = garrAmts(CSALES_DISCOUNTS) - rsdetail("AMT")
                        Case Is = "MED"
                            garrAmts(CCASH) = garrAmts(CCASH) - rsdetail("AMT")
                        Case Is = "MER"
                            garrAmts(CCRS_ISSUED) = garrAmts(CCRS_ISSUED) + rsdetail("AMT")
                        Case Is = "MRR"
                            garrAmts(CCRS_RECIEVED) = garrAmts(CCRS_RECIEVED) + rsdetail("AMT")
                        Case Is = "MSC"
                            garrAmts(CMASTERCARD) = garrAmts(CMASTERCARD) + rsdetail("AMT")
                        Case Is = "PDO"
                            garrAmts(CPAID_OUT) = garrAmts(CPAID_OUT) - rsdetail("AMT")
                        Case Is = "RET"
                            garrAmts(CRETURNS) = garrAmts(CRETURNS) - rsdetail("AMT")
                        Case Is = "SAL"
                            garrAmts(CSALES) = garrAmts(CSALES) - rsdetail("AMT")
                        Case Is = "TAX"
                            garrAmts(CSALES_TAX) = garrAmts(CSALES_TAX) - rsdetail("AMT")
                        Case Is = "VIS"
                            garrAmts(CVISA) = garrAmts(CVISA) + rsdetail("AMT")
                    End Select
                    rsdetail.MoveNext
                Loop
            End If
            rsdetail.Close
            intseqNum = ((Val(rsmain("TERM_NUM") - 1)) * 100) + 1
            For inti = 0 To 14
                strRec = garrAccountNos(inti) & Right(rsmain("STORE_CD"), 3) & ".." & _
                         Format(rsmain("TRN_DT"), "yymmdd") & _
                         ".." & Right(rsmain("STORE_CD"), 2) & _
                         Lpad(rsmain("TERM_NUM"), 4) & ".." & _
                         Lpad(intseqNum, 3) & ".." & _
                         Space(10 - Len(AmtValue(garrAmts(inti)))) & AmtValue(garrAmts(inti)) & _
                         "..POS " & garrNames(inti) & " Store:  " & _
                         Store_Val(rsmain("STORE_CD")) & " Reg:  " & _
                         Right(rsmain("TERM_NUM"), 1) & "  .."
    
                Print #iFileNum, strRec
                intseqNum = intseqNum + 1
            Next
            rsmain.MoveNext
        Loop
        Close iFileNum
    Else
        frmSA.lblProcessing.Caption = ""
        Screen.MousePointer = vbDefault
        MsgBox " No data available for the entered paramters", vbInformation
        ProcessSales_audit = False
        Exit Function
    End If
    rsmain.Close
    ProcessSales_audit = True
    frmSA.lblProcessing.Caption = ""
    Screen.MousePointer = vbDefault
    Exit Function
    errhandler:
        frmSA.lblProcessing.Caption = ""
        Screen.MousePointer = vbDefault
        MsgBox "Errors occured while processing Sales Audit file", vbCritical
        ProcessSales_audit = False
        If rsmain.State = adStateOpen Then
            rsmain.Close
        End If
        If rsdetail.State = adStateOpen Then
            rsdetail.Close
        End If
        Exit Function
    errfilepath:
         frmSA.lblProcessing.Caption = ""
         Screen.MousePointer = vbDefault
         MsgBox " Invalid file name/type, Could not open file - Cannot process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
         ProcessSales_audit = False
         Exit Function
    End Function
    ...the first SQL statement pulls back an array that feeds the second statement. Something in this mechanism is causing the problem though?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •