We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ORACLE Connections using ORAOLEDB...

Medium Priority
1,473 Views
Last Modified: 2012-06-21
I'm having a problem using the OraOLEDB to INSERT records into a table using the following syntax. I want to utilize the "PwdChgDlg" of the driver.

conn.ConnectionString = "Provider=OraOLEDB.Oracle;PwdChgDlg=1;Data Source=" & ORACLE_Database

            sql = "INSERT INTO " & tablename & " VALUES(" & Parseda & Parsedb & ")"
            Debug.Print sql
            conn.BeginTrans
            conn.Execute sql
            conn.CommitTrans

I get an error at .BeginTrans...? I believe I need to use .command and ExecuteNonQuery() but can't find any syntax except for VB.net.

Can anyone help?

NG,


Comment
Watch Question

Top Expert 2009

Commented:
I don't see that you actually connected to the database.

Try adding a "conn.Open" after you set the connection string.

Author

Commented:
Sorry I missed that when posting the question.... here is the complete connection


Set conn = New ADODB.connection
conn.CursorLocation = adUseClient

conn.ConnectionString = "Provider=OraOLEDB.Oracle;PwdChgDlg=1;Data Source=" & ORACLE_Database

sql = "INSERT INTO " & tablename & " VALUES(" & Parseda & Parsedb & ")"
Debug.Print sql
conn.BeginTrans
conn.Execute sql
conn.CommitTrans

conn.ConnectionTimeout = 30
conn.Properties("Prompt") = adPromptAlways
conn.Open
Top Expert 2009

Commented:
The ordering of the statements are wrong. You must open the connection first, before beginning a transaction or executing SQL.

Something like this:

conn.ConnectionString = "Provider=OraOLEDB.Oracle;PwdChgDlg=1;Data Source=" & ORACLE_Database
conn.ConnectionTimeout = 30
conn.Properties("Prompt") = adPromptAlways
conn.Open
...
conn.BeginTrans
conn.Execute sql
conn.CommitTrans

Open in new window

Author

Commented:
and another typo... the connection is open before trying to run the query, sorry.

Set conn = New ADODB.connection
conn.CursorLocation = adUseClient

conn.ConnectionString = "Provider=OraOLEDB.Oracle;PwdChgDlg=1;Data Source=" & ORACLE_Database

conn.ConnectionTimeout = 30
conn.Properties("Prompt") = adPromptAlways
conn.Open

sql = "INSERT INTO " & tablename & " VALUES(" & Parseda & Parsedb & ")"
Debug.Print sql
conn.BeginTrans
conn.Execute sql
conn.CommitTrans
Top Expert 2009

Commented:
Im confused why the code keeps changing. :)

Also, please show the exception info.

Author

Commented:
The first time was an incomplete posting and the second time was user error (mine) when posting since this comes from multiple sub()'s, sorry :(...

This is the essential connection and SQL INSERT being run.


Set conn = New ADODB.connection
conn.CursorLocation = adUseClient

conn.ConnectionString = "Provider=OraOLEDB.Oracle;PwdChgDlg=1;Data Source=" & ORACLE_Database

conn.ConnectionTimeout = 30
conn.Properties("Prompt") = adPromptAlways
conn.Open

sql = "INSERT INTO " & tablename & " VALUES(" & Parseda & Parsedb & ")"
Debug.Print sql
conn.BeginTrans
conn.Execute sql
conn.CommitTrans


I'm beginning to get the feeling that OLEDB is meant for VB.Net only and not VB6...

When debugging the code it seems to error at "conn.BeginTrans".

This is the error.

?err.Number
-2147168237
?err.Description
Cannot start more transactions on this session.


Using the following driver all was good.

conn.connectionstring = "Provider=MSDAORA;Data Source=" & ORACLE_Database

NG,

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks for the posts.

Ultimately the solution was to comment out the lines below.

Set conn = New ADODB.connection
conn.CursorLocation = adUseClient

conn.ConnectionString = "Provider=OraOLEDB.Oracle;PwdChgDlg=1;Data Source=" & ORACLE_Database

conn.ConnectionTimeout = 30
conn.Properties("Prompt") = adPromptAlways
conn.Open

sql = "INSERT INTO " & tablename & " VALUES(" & Parseda & Parsedb & ")"
Debug.Print sql
'conn.BeginTrans      <===== Commented out
conn.Execute sql
'conn.CommitTrans      <===== Commented out


"I always suggest native Oracle drivers when connecting to an Oracle database."

What drivers are you referring, those that are installed with the client? Do they allow for user level setting of password expirations (PwdChgDlg) or the like?


screenshot.23.jpg
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>What drivers are you referring, those that are installed with the client?

I was on mobile (tiny screen) when I posted that.  I apologize.
I keyed in on "Provider=MSDAORA" and didn't scroll back up to see you were using "Provider=OraOLEDB.Oracle"

OraOLEDB is the native Oracle driver.

Author

Commented:
OK, cool.

VB,
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.