Populating a ComboBox with info from a Oracle Database

[Papa_Don]

Hi Group,

I'm attempting to pull info from our database to populate a combo-box in VB.net (Visual Studio Express 2015). Here's my first attempt at the connection:

Dim conn As New Oracle.DataAccess.Client.OracleConnection()
conn.ConnectionString = oraDB

From here I'm not sure how to pull the data into the combobox. I do have my code written to find the information:

Dim sql As String = "select dw01.RP_ID" _
                    & "from donacwil.RATE_PLANS_SSB   dw01" _
                    & "where dw01.PROP_ID in ('" & propID & "')" _
                    & "And dw01.DW_RCRD_STS_CD in ('A')" _
                    & "Order by dw01.RP_ID"

Does anyone know what to do from here?

Thanks for your help.

Don

[ddanbe]

Some googling could help here: Example
I tried "populate combobox vb.net from database"

[Papa_Don]

ddanbe,

I'm begining to think that part of the issue is my connection string. While I did get it from our database team, I've either entered it wrong or I simply don't know how to connect to it correctly. If you're familiar with these things, here's what I've written:

Try
            Dim conn As New Oracle.DataAccess.Client.OracleConnection("DWHPRD_ADHOC=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=comp.corp.star)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=ADHOC))) ; User ID=xxx;Password=xxx;")
            conn.Open()
            Dim sql As String = "select dw01.RP_ID" _
                                & "from donacwil.RATE_PLANS_SSB   dw01" _
                                & "where dw01.PROP_ID in ('" & propID & "')" _
                                & "And dw01.DW_RCRD_STS_CD in ('A')" _
                                & "Order by dw01.RP_ID"
            Dim cmd As New Oracle.DataAccess.Client.OracleCommand
            cmd.Connection = conn
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            Dim dr As Oracle.DataAccess.Client.OracleDataReader = cmd.ExecuteReader()
            dr.Read()
            Debug.Print(dr.Read)
            While (dr.Read())
                Debug.Print(dr.GetString(0))
                cmbxRatePlans.Items.Add(dr.GetString(0))
            End While
            conn.Close()
            conn = Nothing
        Catch ex As Exception
            Throw ex
        End Try

The first thing I'd really like to do is to ensure I'm connecting correctly and the query is being run. I've attempted a "Debug.Print" to see if I'm getting any data, but I've got nothing to return to the output box. Of course it may be that I've got a wrong connection string.

I've been up and down Google to find an answer. I had found several prior to creating this thread but nothing has worked. The above is my latest iteration. Thoughts?

As always, thanks for your help.

Don

[jwshepherd]

from What I can Tell, You are only pulling one column that looks like it just list the reportID's or the PropertyID. I usually use a datatable to pull information going in to a combo box, so I can Have one Column to display( displaymember) and the ID Column "hidden" as the Value member.

But the first step is to find out if we are pulling data or not. I have pulled this code from a working MySQL database project. It should at least popup a message box and give you an error somewhere in there.

 Private Sub LoadComboBox(propID As Integer)
        Try
            Dim sql As String = "select dw01.RP_ID" _
                                 & "from donacwil.RATE_PLANS_SSB   dw01" _
                                 & "where dw01.PROP_ID in ('" & propID & "')" _
                                 & "And dw01.DW_RCRD_STS_CD in ('A')" _
                                 & "Order by dw01.RP_ID"
            Dim D As DataTable = GetDatatable(sql)
            cmbxRatePlans.datasource = D

        Catch ex As Exception
            MsgBox("Error. " & vbCrLf & ex.InnerException.Message.ToString, MsgBoxStyle.Critical)
        End Try
    End Sub
    Private Function GetConnected() As Oracle.DataAccess.Client.OracleConnection
        Dim mconn As New Oracle.DataAccess.Client.OracleConnection("DWHPRD_ADHOC=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=comp.corp.star)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=ADHOC))) ; User ID=xxx;Password=xxx;")
        If mconn.State <> ConnectionState.Open Then
            Try
                mconn.Open()
            Catch ex As Exception
                MsgBox("Database Connection Failed. " & vbCrLf & ex.InnerException.Message.ToString, MsgBoxStyle.Critical)
            End Try
        End If
        Return mconn
    End Function
Private Function GetDatatable(sql As String As DataTable ) as Datatable
        Dim mconn As Oracle.DataAccess.Client.OracleConnection = GetConnected()
        Dim DAT As New Oracle.DataAccess.Client.OracleDataAdapter(sql, mconn)
        Dim DS As New DataSet
        CType(DS, System.ComponentModel.ISupportInitialize).EndInit()
        Try
            DAT.Fill(DS)
            Return DS.Tables(0)
        Catch e As Oracle.DataAccess.Client.OracleException
            MsgBox("Query Failed. " & vbCrLf & e.InnerException.Message.ToString, MsgBoxStyle.Critical)
            Return Nothing
        Finally
            mconn.Close()
        End Try
    End Function

[Papa_Don]

@jwshepard,

My apologies for the delay in getting back to you. I do appreciate the code snippet. Unfortunately I can't tell if it's working or not. I am getting a couple of errors in my output box. These are:

1) Exception thrown: 'System.ArgumentException' in Oracle.DataAccess.dll
2) Exception thrown: 'System.NullReferenceException' in OracleConnections.exe

I'm researching these now in an effort to fix this. If you know something about setting up the Oracle Client Database, feel free to chime in. Currently I'm lost (LOL... sort of).

Again, thanks for your assistance.