?
Solved

ORACLE Connections using ORAOLEDB...

Posted on 2011-04-28
11
Medium Priority
?
1,289 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,


0
Comment
Question by:nike_golf
  • 5
  • 4
  • 2
11 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35486898
I don't see that you actually connected to the database.

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

Author Comment

by:nike_golf
ID: 35486955
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35487074
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

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 13

Author Comment

by:nike_golf
ID: 35487092
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35487112
Im confused why the code keeps changing. :)

Also, please show the exception info.
0
 
LVL 13

Author Comment

by:nike_golf
ID: 35487260
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,

0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35487761
Msdora is MSofts data provider for Oracle.  That might not understand begintrans.  Can you not just remove the begin/end and just issue a commit?

I always suggest native Oracle drivers when connecting to an Oracle database.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 1000 total points
ID: 35488717
It must be in autocommit mode, using an implicit transaction.

The docs http://download.oracle.com/docs/cd/B10501_01/win.920/a95498/using.htm say:

Local Transactions
OraOLEDB supports the ITransactionLocal interface for explicit transactions. By default, OraOLEDB is in an autocommit mode, meaning that each unit of work done on the database is automatically or implicitly committed. With the use of ITransactionLocal interface, consumers may explicitly start a transaction for a particular session, allowing a unit of work to be explicitly committed or aborted by the consumer.

However, I cannot see how to disable that.
0
 
LVL 13

Author Comment

by:nike_golf
ID: 35491185
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35491377
>>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.
0
 
LVL 13

Author Comment

by:nike_golf
ID: 35492115
OK, cool.

VB,
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question