[RESOLVED] [2008] ODBC Oracle read

[BULK]

Hi
Don't cut me into pieces for not beeing so clear in this subject. ODBC is new to me so I hope that I can get some help here. I'm trying to to connect to a Oracle database and read the content to a textbox. This is my code:

Dim conn As OdbcConnection
        Dim connectionString As String
        Try
            connectionString = "DSN=***;Driver={Microsoft ODBC for Oracle};Server=***.com;Uid=Bulk;Pwd=pwd;"
            conn = New OdbcConnection(connectionString)
            conn.Open()
            TextBox1.AppendText("Connection Open" & vbCrLf)

            Dim dbCommand As System.Data.OracleClient.OracleCommand
            Dim dbDataReader As System.Data.OracleClient.OracleDataReader
            dbDataReader = dbCommand.ExecuteReader()

            Do While dbDataReader.Read()
                TextBox1.AppendText(dbDataReader(0).ToString & vbCrLf)
            Loop

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

Connection is ok, (I think) Connection Open shows in textbox, and no errors. But I get a nullexeption on the reader, it has not been assign a value. Don't know what to do next.

[Hamish]

You never set the CommandText property for the dbCommand object, I think that's why it can't return a DataReader object when you do:

dbDataReader = dbCommand.ExecuteReader()

Strange that it won't give an error on that line though.

[BULK]

Hi
Well I get an error on that line. I still dont know how to read and display into textbox.

[Hamish]

Ah, I see the problem now. You only declare dbCommand, you never instantiate it. Try this:

Dim dbCommand As New System.Data.OracleClient.OracleCommand("SELECT * FROM <YourTableName>", conn)

That way you declare it, instantiate it and set the Connection and CommandText properties all at once.

[BULK]

I get this:

Error 1 Value of type 'System.Data.Odbc.OdbcConnection' cannot be converted to 'Oracle.DataAccess.Client.OracleConnection'.

[GaryMazzone]

You need to be consistent in you peices. You declare and use ODBC connection and then try and use an Oracle.DataReader. You need to use the ODBC object completely if you want to go that way.

But I know this is none of my business, but why are you using ODBC data connections the Oracle client stuff will perform better.

[BULK]

Hi
I guess of no reason, but I have a ODBC client installed. I'm new to this and any connection could be used, I think.

[GaryMazzone]

I would use the Oracle Client name space unless you are planing on making this DB independent. You will get better performance using the DB providers client.

[BULK]

Ok, thank's for the advice. Still trying to make it this way, for a while. This should work I guess, but I have problem with my table name.

Dim conn As OdbcConnection
        Dim connectionString As String
        Try
            connectionString = "DSN=***;Driver={Microsoft ODBC for Oracle};Server=***.com;Uid=Bulk;Pwd=pwd;"
            conn = New OdbcConnection(connectionString)
            conn.Open()
            TextBox1.AppendText("Connection Open" & vbCrLf)

              Dim cmd As System.Data.Odbc.OdbcCommand
            Dim odbcomand As String
            Dim reader As System.Data.Odbc.OdbcDataReader = Nothing

            odbcomand = ("SELECT * FROM <My Table>")
            cmd = New OdbcCommand(odbcomand, conn)
            reader = cmd.ExecuteReader()
            Do While reader.Read()
                TextBox1.AppendText(reader.Item(0) & "  -  " & reader.Item(1) & "  -  " & reader.Item(2) & vbCrLf)
            Loop
            reader.Close()
            cmd.Dispose()
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

[GaryMazzone]

What is the problem..... What is the table name in Oracle?

[BULK]

The name of the table name I'm trying to display is found out from EXCEL.
In EXCEL I import external data, new database query. From the query wizard I can see the tablenames in a textbox, for example TEST. This table name is what I'm trying to use.

[Hamish]

If the table is called TEST then you should use:

            odbcomand = ("SELECT * FROM TEST")

[BULK]

Tnank's. Should be close now, now I get that table or view does not exist.

[GaryMazzone]

That seems pretty informative.... The table test does not exsist in the database to the user does not have permissions to view the table.

[BULK]

Ok, but I have access when import via EXCEL.

[GaryMazzone]

Do you the Oracle client on you machine? If so connect using the same username/password as in the connect string and attempt to select from the table using SQL*Plus or SQLDeveloper

[Hamish]

I don't know much about Oracle, but in SQL you can use Windows authentication or SQL autentication. Getting the data with Excel would be using Windows authentication (unless you specify another account in the process), while your connection string specifies a user account and password that make me suspect you're using some sort of integrated security here.
Could that be the problem?

[BULK]

When I import to EXCEL i connect with the same username/password as in the connection string, in the connection string I use Driver={Microsoft ODBC for Oracle};. In the query wizard I can see the tablenames, but theres a + sign infront of the name, I must click here to show the actual table. This table can then be imported to EXCEL.

[BULK]

Ok, I guess that I have to learn the commands I don't know much yet. What I can say is that this command will show all the metadata tables.

odbcomand = ("select * from dict")

This is working, with my code.