ExecuteReader require an open and available Connection error connecting to an Oracle8i database

Well, for me, the union of Visual Basic and Oracle8i has not been a pretty one to say the least.

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_23879323.html

The link above is a bit of history with me and this project which may have some bearing on my problem. As mentioned in that link, I finally got my connection string to work. Now I am getting an error on my ExecureReader function. The exact error is

ExecuteReader requires an open and available Connection. The connection's current state is closed.

The code is listed below. I cannot figure out why it is closed. I have tried searching but to no avail, so I decided to ask the experts. If I cannot get this to work I may just ditch Visual Basic all together and do it in another language, probably Java.

NOTE: Our database provider will only grant me read only access to the database. This is going to be a report generating program and nothing more.
Dim results As OdbcDataReader
        Dim connection As New Odbc.OdbcConnection
        Dim sConnString As String = "Driver={Microsoft ODBC for Oracle};Server=cutacms;Uid=uid;Pwd=pwd;"
        Dim cmd As OdbcCommand = connection.CreateCommand
        connection = New Odbc.OdbcConnection(sConnString)
        cmd.CommandText = "SELECT * FROM ORDH WHERE ORDER_NBR = 12345"
        connection.Open()
        results = cmd.ExecuteReader
        startDate.Text = results("ORDER_NBR").ToString
        results.Close()
        connection.Close()

Open in new window

rolltide0Asked:
Who is Participating?
 
ajexpertConnect With a Mentor Commented:
Try This
Dim results As OleDbDataReader
        Dim connection As New OleDb.OleDbConnection
        Dim sConnString As String = "Provider=MSDAORA.1;Data Source=cutacms;Persist Security Info=True;User ID=uid;Password=pwd;"
      connection.Open()  
      Dim cmd As OleDbCommand = connection.CreateCommand
        connection = New OleDb.OleDbConnection(sConnString)
        cmd.CommandText = "SELECT * FROM ORDH WHERE ORDER_NBR = 6025595"
          results = cmd.ExecuteReader
        startDate.Text = results("ORDER_NBR").ToString
        results.Close()
        connection.Close()

Open in new window

0
 
ajexpertCommented:
This link could be helpful to you
http://www.startvbdotnet.com/ado/oracle.aspx
0
 
rolltide0Author Commented:
OK based on that link I modified the code to what is listed below. I am still getting the same error. I know the connection string is working because if I change the password I get a invalid login error. I change it back I go back getting the connection is closed error on the executeReader statement.
Dim results As OleDbDataReader
        Dim connection As New OleDb.OleDbConnection
        Dim sConnString As String = "Provider=MSDAORA.1;Data Source=cutacms;Persist Security Info=True;User ID=uid;Password=pwd;"
        Dim cmd As OleDbCommand = connection.CreateCommand
        connection = New OleDb.OleDbConnection(sConnString)
        cmd.CommandText = "SELECT * FROM ORDH WHERE ORDER_NBR = 6025595"
        connection.Open()
        results = cmd.ExecuteReader
        startDate.Text = results("ORDER_NBR").ToString
        results.Close()
        connection.Close()

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
fanopoeCommented:
try
...
results = cmd.ExecuteReader
results.read()
...
0
 
rolltide0Author Commented:
Adding results.read() won't help because results.ExecuteReader is where the exception is being thrown. I tried it just to make sure, but no go.
0
 
rolltide0Author Commented:
On that arrangement, I get a "The connectionString property has not been initialized" error on the connection.Open() line
0
 
rolltide0Author Commented:
I am starting to wonder if there is a compatibility issue here.
0
 
ajexpertCommented:
You can test your connection in this way
Dim results As OleDbDataReader
        Dim connection As New OleDb.OleDbConnection
        Dim sConnString As String = "Provider=MSDAORA.1;Data Source=cutacms;Persist Security Info=True;User ID=uid;Password=pwd;"
      connection.Open()  
      
If connection.State = ConnectionState.Open then
        Messagebox.show("Connection Established")
Else
Messagebox.show("Connection Failed")
Thanks,
Ajay M

Open in new window

0
 
rolltide0Author Commented:
Great idea. However it confirms that the connection is being established.  At least we know for sure that much is working. However for some reason it is closing before getting to the next statement, apparently.
0
 
fanopoeCommented:
no compatibility problem, I use 8i all day long. Do you need to include a reference to MSDAORA in your project?
0
 
rolltide0Author Commented:
I wouldn't think so. I mean the connection is opening at least on the connection.Open() statement. Apparently it is just not staying open.
0
 
rolltide0Author Commented:
Any further suggestions before I scrap the thing and move to a different platform?
0
 
fanopoeCommented:
have you verified all your user permissions on the db? Create session, select on said table, etc...?
0
 
ajexpertCommented:
0
 
rolltide0Author Commented:
"have you verified all your user permissions on the db? Create session, select on said table, etc...?"

Well I can't change if even if I didn't have access because I am only allowed read only access. I can connect and pull data from within excel and have written several apps in Java all successfully with no problem. Since they still work, I have to assume the permissions are still there.

Aj, I'll try that example in that next link. I am going to assume you can modify that for oledb connections (that example is for sqlexpress not oracle).
0
 
rolltide0Author Commented:
I couldn't really make heads or tails with that link. It appeared to do something I'm not trying to do or either I do not have enough VB experience to make up what the syntax is saying. I tried binding a dataset and that worked. I guess I could try using that to build the  queries I need to run. The only problem is I have never been able to do that successfully, which is why I have aways used manual connections (I feel I have more control over that anyways). I'm going to try a few more things and if they doesn't work I guess I'll have no choice but to try a different platform. If you can think of any thing else feel free to comment.

I just cannot fathom why it can successfully connect to the database yet not be connected. Something just doesn't add up.
0
 
ajexpertCommented:
Do you expect only one row ( or column ) to be returned from your query?
0
 
rolltide0Author Commented:
Yes and No. I got a series of 5 queries that will need to be run throughout this project, some will have 1 row returned one will have as many as several hundred returned.
0
 
rolltide0Author Commented:
I got it. I posted on another forum just to give it a fresh set of eyes, and wow what power 1 word has. Connection was being initialized twice wiping some of the info needing to connect. To recap the following did the trick

1) changing connection types as mentioned by aj earlier in this thread to ole from odbc
2) in the line Dim connection As New OleDb.OleDbConnection, remove the word "new" and use the line connection = New OleDb.OleDbConnection(sConnString) to declare the connection variable.

Thanks again for the assistance

0
All Courses

From novice to tech pro — start learning today.