Connect to ORACLE from VB

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.

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
------------------------------------------------

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.