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.
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.
can u show me the connect string and the Query u given ?
Roshmon
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.
otherwise u need 2 change the code.
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").Va lue = 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.DBConnectORAC LE("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_T ime) 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.Encrypt Decrypt
'Check whether password is correct
If (strparam2 = objencdec.DecryptString(rs LOGIN!UPas sword, "password")) Then
'Insert the log entry
sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg _Date,lg_T ime) 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_T ime) 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_T ime) 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
-------------------------- ---------- ---------- --
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").Va
.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.DBConnectORAC
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
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.Encrypt
'Check whether password is correct
If (strparam2 = objencdec.DecryptString(rs
'Insert the log entry
sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg
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
cnUSERS.Execute sSQL
End If
Else
intStatus = 1
'Insert the log entry
sSQL = "INSERT INTO Log_Events (lg_Description,lg_User,lg
cnUSERS.Execute sSQL
End If
End If
'Pass the status using reference parameter
strparam2 = CStr(intStatus)
End Function
--------------------------
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi arunab,
try this. down load OraReg.exe from
support.microsoft.com/supp ort/ 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
try this. down load OraReg.exe from
support.microsoft.com/supp
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
ASKER