VB OracleDataReader

I am selecting a single row that will return a single value. I need to assign that value to a variable of type integer. How can I do this?
The code below is crashing at:
Dim dr As OracleDataReader = myCmd.ExecuteReader()
with this error:
Connection must be open for this operation

I know I just dont have the syntax right because the connection object is correct right before that line is executed.
Dim intDocID As Integer = Nothing
        Dim myConn As Global.Oracle.DataAccess.Client.OracleConnection = Nothing
        Dim myCmd As OracleCommand = Nothing
        Dim strQuery As String = "SELECT DOC_ID FROM DOCUMENTS WHERE DISPLAY_NAME = '1a' 
 
        Try
            Dim strConnect As String = ConfigurationManager.ConnectionStrings("MYDB").ConnectionString
            myConn = New OracleConnection(strConnect)
            myCmd = New OracleCommand(strQuery, myConn)
            myCmd.CommandType = CommandType.Text
            Dim dr As OracleDataReader = myCmd.ExecuteReader() 
            dr.Read()
 
            intDocID = dr.GetInt16("doc_id").ToString()
        Catch ex As Exception
            Dim statusMessage As String = "DATABASE ERROR retrieving data - " + ex.Message
            Throw New ApplicationException(statusMessage)
        Finally
 
            If Not myCmd Is Nothing Then
                myCmd.Dispose()
            End If
 
            If Not myConn Is Nothing Then
                myConn.Close()
                myConn.Dispose()
            End If
        End Try

Open in new window

bertino12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
myConn = New OracleConnection(strConnect)
myConn.Open()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alainbrydenCommented:
You never called myConn.Open(), this is likely the cause of the specific error message you got.

--
Alain
0
bertino12Author Commented:
Well I did it like below in another function and it works fine.
 Dim myDT As DataTable = Nothing
        Dim myConn As Global.Oracle.DataAccess.Client.OracleConnection = Nothing
        Dim myCmd As OracleCommand = Nothing
        Dim myDA As OracleDataAdapter = Nothing
        Dim strQuery As String = "SELECT D.DOC_ID, D.DOC_TYPE, D.DISPLAY_NAME, D.SUMMARY, D.HTML_DOC, D.SORTCODE " & _
            "FROM DOCUMENTS D " 
        Try
            Dim strConnect As String = ConfigurationManager.ConnectionStrings("myDB").ConnectionString
            myConn = New OracleConnection(strConnect)
 
            myCmd = New OracleCommand(strQuery, myConn)
 
            myCmd.CommandType = CommandType.Text
 
            myDA = New OracleDataAdapter(myCmd)
            myDT = New DataTable("FindResults")
 
            myDA.Fill(myDT)
 
        Catch ex As Exception
            Dim statusMessage As String = "DATABASE ERROR retrieving Find data (""Find By Text"") - " + ex.Message
            Throw New ApplicationException(statusMessage)
        Finally
 
            If Not myCmd Is Nothing Then
                myCmd.Dispose()
            End If
 
            If Not myConn Is Nothing Then
                myConn.Close()
                myConn.Dispose()
            End If
        End Try

Open in new window

0
VBRocksCommented:
If you are only returning a single value from 1 row, then use the ExecuteScalar method:

    myCmd.Connection.Open()

    Dim myValue As String = myCmd.ExecuteScalar()

    myCmd.Connection.Close()

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.