Link to home
Start Free TrialLog in
Avatar of arunab
arunab

asked on

Connect to ORACLE from VB

Hi,
I migrated SQL database (WINDOWS 2000) to ORACLE 8i running on SUN SOLARIS.
The front end was written in VB.
When i tried to connect to ORACLE database (remote) it fails, but some how it succedds after couple of failures.Again it fails. But i can connect to the database successfully by SQL PLUS with out any problems.
The error it gives when it fails is as follows.
"DB ACCESS -  oracle error occured.But error message cannot be retrieved from oracle."
AND  MICROSOFT VISUAL BASIC
"RunTime-error 3709
The connection cannot be used to perform this operation.It is either closed or invalid in this context"

PLEASE ADVICE ME ON THIS.

Thank You
Regards
Aruna.
Avatar of arunab
arunab

ASKER

PLEASE ADVICE SOON AS POSSIBLE.
can u show me the connect string and the Query u given ?

Roshmon
if u r using ODBC the problem is with ur connect string & make sure that ur settings r properly adjusted 2 the new oracle database.

otherwise u need 2 change the code.

Avatar of arunab

ASKER

Roshmon, I have sent the functions used to connect to ORACLE db

Oracle sid / Oracle service = ESL
User Id = billing   Password = billing.

Arunab

-----------------------------

'Function for ORACLE Databases
Public Function DBConnectORACLE(ByVal ServerName As String, ByVal UserName As String, ByVal Password As String) As ADODB.Connection
 
   Dim err As Error
   
   On Error GoTo Err_handler
   
   Set cnBilling = New ADODB.Connection
   
   'Define connection parameters
   With cnBilling
       .Provider = "MSDAORA"
       .Properties("Data Source").Value = ServerName
       .Properties("User ID").Value = UserName
       .Properties("Password").Value = Password
       .Open
   End With
     
   'Passing connection to called procedure
   Set DBConnectORACLE = cnBilling
   
   Exit Function
'Handling all errors
Err_handler:
    For Each err In cnBilling.Errors
       MsgBox err.Description
    Next
   
End Function


Public Function AssignDB() As ADODB.Connection
   'This function to connect to database
   Dim objDBConnect As DBAccess.DBConnect
       
   On Error GoTo Err_handler
   
   Set objDBConnect = New DBAccess.DBConnect
   Set AssignDB = objDBConnect.DBConnectORACLE("ESL", "billing", "billing")
   Exit Function
   
Err_handler:
MsgBox "Connection to Database is failed ...!", vbCritical, "Connecting to Database"

End Function

--------------------------------

Folowing is the function used to connect (ie go into the program by giving userid/password which is not the user id/password used to loginng into ORACLE)

-------------------------------------

Public Function UserLogin(ByVal strparam1 As String, ByRef strparam2 As String) As ADODB.Recordset
  'strparam1 as User ID and strparam2 as Password
   Dim rsLOGIN As ADODB.Recordset
   Dim strPassword As String
   Dim intStatus As Integer
   Dim sSQL As String
   
   'Connect to the database
   Set objdbaccess = New DBAccess.DBAssign
   Set cnUSERS = objdbaccess.AssignDB
       
   'Check whether user has entered the userid
   If strparam1 = "" Then
       
       intStatus = 4
       
   'Check whether user has entered the password
   ElseIf strparam2 = "" Then
       
       intStatus = 4
       'Insert the log entry
       sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg_Date,lg_Time) VALUES ( 'Logged on failed - Password is blank','" & strparam1 & "','" & Date & "','" & Time & "')"
       cnUSERS.Execute sSQL
   Else
       Set rsLOGIN = New ADODB.Recordset
               
       'Check whether user exist in the database
       rsLOGIN.Open "SELECT * FROM Users WHERE Users.UserID ='" & strparam1 & "'", cnUSERS, adOpenStatic, adLockReadOnly
   
       If Not rsLOGIN.EOF Then
       
           Set objencdec = New PasswordProtection.EncryptDecrypt
           
           'Check whether password is correct
           If (strparam2 = objencdec.DecryptString(rsLOGIN!UPassword, "password")) Then
               'Insert the log entry
               sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg_Date,lg_Time) VALUES ( 'Logged on Successfully','" & rsLOGIN!UserID & "','" & Date & "','" & Time & "')"
               cnUSERS.Execute sSQL
               
               Set UserLogin = rsLOGIN
               intStatus = 3
           Else
               
               intStatus = 2
               'Insert the log entry
               sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg_Date,lg_Time) VALUES ( 'Logged on failed - Invalid Password','" & rsLOGIN!UserID & "','" & Date & "','" & Time & "')"
               cnUSERS.Execute sSQL
           End If
       Else
           
           intStatus = 1
           'Insert the log entry
           sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg_Date,lg_Time) VALUES ( 'Logged on failed - Invalid User','" & strparam1 & "','" & Date & "','" & Time & "')"
           cnUSERS.Execute sSQL
       End If
   
   End If
   
  'Pass the status using reference parameter
  strparam2 = CStr(intStatus)
     
End Function
------------------------------------------------
Avatar of arunab

ASKER

Panikv,
What are the other setting to be used in VB code to connect to ORACLE other than ORACLE SERVICE/SID .userid/password and the ODBC DSN name.
I have installed ORACLE client in the client machine..do i need to change in SQLNET and TNSNAMES files.?

Thank You.

Arunab.
ASKER CERTIFIED SOLUTION
Avatar of Roshan Davis
Roshan Davis
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi arunab,

try this. down load OraReg.exe from

support.microsoft.com/support/ kb/articles/Q264/0/12.asp

and run it. it will modify necessary registry entries that are required
for ado to connect oracle 8i from win2k client. it worked for me.
hopefully it will work for you also.

trnarahari