Problem Connecting to Oracle 9i
I have this older VB6 application that I don't have the time to convert to VS2005 right now. I have the Oracle ODBC client installed and the DSN are created as system DSN's. They are not using the "Microsoft ODBC for Oracle" driver, instead they are using the actual Oracle driver. Ive tried several connection strings including:
Code:
cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"
cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"
I keep getting the error handler from my function:
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 = "Provider=OraOLEDB.Oracle.1;Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
'cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
'cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
'cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"
cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"
cn.Open
OpenDatabaseConnection = True
Exit Function
errhandler:
MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
OpenDatabaseConnection = False
Exit Function
End Function
...any ideas what the proper connection string is?
Re: Problem Connecting to Oracle 9i
I SQL* Net installed on the machine and an entry in the TNSNames file made for the database? What is the exact error number and message coming back?
Re: Problem Connecting to Oracle 9i
It's strange, I've tracked this down to something with the Oracle Client we are using. When I go back a version to v8.1.7.0 this works with no problem, however we are updating our databases and the new client v9.2.0.1.0 this appears to cause the problem. We are careful to install and uninstall using the Oracle universal installer and delete the old version completely as well.
Once the new version is installed we reboot, then create the DSN names in the MS ODBC administrator, using the new Oracle driver as the connection driver. We can connect to the database with several 3rd party tools like WinSQL and Quest TOAD as well as SQL Plus, but whenever we run these older VB apps they will not connect. I think it has something to do with either the connection string or the provider ("MSDAORA.1")?
As for an error, when I place a breakpoint on the message box in the debug window I get:
Code:
?error
Oracle error occurred, but error message could not be retrieved from Oracle.
?????
Re: Problem Connecting to Oracle 9i
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?
