Filling a combo box

[brews]

Have successfully filled a combo box using either a collection or listof. However, having trouble with one particular combo box that does not play nice. the code using list of:

    Private Sub LoadProfCbo()   'Load cboProfession
        Dim CP As New CProfession
        Dim obj As Object
        obj = frmFind.lstDisplay.SelectedItem
        CP = CType(obj, CProfession)
        Me.cboProfession.Items.Clear()
        Dim pCol As New Collection
        dataMgr.ProfessionLoad(pCol)
        For Each CP In pCol
            cboProfession.Items.Add(CP)
        Next CP
        pCol = Nothing
    End Sub

    Public Function ProfessionLoad(ByRef c As Collection) As Collection
        da = New OleDbDataAdapter
        Dim dr As DataRow
        Dim ds As New DataSet
        Dim CP As CProfession
        cmd = New OleDbCommand("Select ProfessionID, Profession From Profession", cn)
        da.SelectCommand = cmd
        da.Fill(ds, "Profession")  'jumps out here and loads the main screen that holds the combo box. no error msg. 
        Try
            For Each dr In ds.Tables("Profession").Rows
                CP = New CProfession
                CP.Profession = CStr(dr("Profession"))
                CP.ProfessionID = CInt(dr("ProfessionID"))
                c.Add(CP)
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return c
    End Function

When this was used to capture the error:

            Dim myReader As OleDbDataReader
            myReader = cmd.ExecuteReader
            Do While myReader.Read
                CP = New CProfession
                CP.ProfessionID = CInt(myReader("ProfessionID").ToString)
                CP.Profession = CStr(myReader("Profession"))
                lp.Add(CP)

            Loop
            Return lp
            Exit Function
        Catch e As OleDbException
            Dim errorMessages As String = ""
            Dim i As Integer
            For i = 0 To e.Errors.Count - 1
                errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
                               & "Message: " & e.Errors(i).Message & ControlChars.Cr _
                               & "NativeError: " & e.Errors(i).NativeError & ControlChars.Cr _
                               & "Source: " & e.Errors(i).Source & ControlChars.Cr 
            Next i
            MsgBox(errorMessages)
        End Try

The message is no value given for one or more required parameters, source is Jet database engine.

I'm at a loss. Any help would be great. Thanks.

 

[ZmrAbdulla]

    Private Sub LoadProfCbo()   'Load cboProfession
        Dim CP As New CProfession
        Dim obj As Object
        obj = frmFind.lstDisplay.SelectedItem
        CP = CType(obj, CProfession)
        Me.cboProfession.Items.Clear()
        Dim pCol As New Collection
        dataMgr.ProfessionLoad(pCol)
        For Each CP In pCol
          [COLOR=red]  cboProfession.Items.Add(CP.Profession)[/color]
        Next CP
        pCol = Nothing
    End Sub

Zameer Abdulla

 

[brews]

da.Fill(ds, "Profession")  'jumps out here and loads the main screen that holds the combo box. no error msg.

If it jumps out here and does not get to adding to the collection, then it will never fill the combo box.

FYI, CP is an instance of the class CProfession. Have to add both the ID and Name not just the name as suggested.

 

[Bluejay07]

I noticed two things to check.
1. Has the connection been set for 'cn'?
(Related to this, do you have proper permissions to access the data (whether its SQL, Oracle, Access, etc.))
2. Move the try to above the 'cmd = ...' line so that if there is an error, it gets trapped.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ZmrAbdulla]

I was able to fill the dataset and load the combo

   Public Function ProfessionLoad(ByRef c As Collection) As Collection
        Dim cn As New OleDbConnection(GetAccessMDBConnectionString("E:\Profession.mdb"))
        Dim cmd As New OleDbCommand
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter
        Dim dr As DataRow
        Dim ds As New DataSet
        Dim CP As CProfession
        cmd = New OleDbCommand("Select ProfessionID, Profession From Profession", cn)
        da.SelectCommand = cmd
        da.Fill(ds, "Profession")
        Try
            For Each dr In ds.Tables("Profession").Rows
                CP = New CProfession
                CP.Profession = CStr(dr("Profession"))
                CP.ProfessionID = CInt(dr("ProfessionID"))
                c.Add(CP)
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return c
    End Function

GetAccessMDBConnectionString()

is a function that returns a connectionstring.

Additionally I tried the collection to a datagridview

    Private Sub LoadProfCbo()   'Load cboProfession
        Dim CP As New CProfession
        Me.cboProfession.Items.Clear()
        Dim pCol As New Collection
        ProfessionLoad(pCol)
        Me.DataGridView1.DataSource = pCol
        For Each CP In pCol
            cboProfession.Items.Add(CP.Profession)
        Next CP
    End Sub

All working perfect.


But simple solution is

    Public Function ProfessionLoad() As DataTable
        Dim cn As New OleDbConnection(GetAccessMDBConnectionString("E:\Profession.mdb"))
        Dim cmd As New OleDbCommand
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter
        Dim ds As New DataSet
        cmd = New OleDbCommand("Select ProfessionID, Profession From Profession", cn)
        da.SelectCommand = cmd
        da.Fill(ds, "Profession")
        Return ds.Tables(0)
    End Function


   Private Sub LoadProfCbo()   'Load cboProfession
        Dim dt As DataTable
        dt = ProfessionLoad()
        Me.DataGridView1.DataSource = dt

        With Me.cboProfession
            .DisplayMember = dt.Columns(1).ToString
            .ValueMember = dt.Columns(0).ToString
            .DataSource = dt
        End With
      
    End Sub

Zameer Abdulla

 

[brews]

Thanks for the responses. Put the try ahead of cmd =

        Try
            da.SelectCommand = cmd
            cmd = New OleDbCommand("SELECT ProfessionID, Profession FROM Profession ORDER BY Profession", cn)
            If cn.State = ConnectionState.Closed Then cn.Open()
            da.Fill(ds, "Profession")

as soon as the cursor hits da.Fill(ds, "Profession") the next step is to open the main form. Why, don't know. There is no error msg. Doesn't get that far.

This type of procedure works elsewhere but why it is not working here? don't know.

 

[ZmrAbdulla]

I am unable to reproduce the error.

Try placing a messagebox after

da.Fill(ds, "Profession")

Also to try replace the entire form with a new one. I had some experience of forms behaving weird.


Zameer Abdulla

 

[brews]

Thanks for all the suggestions. This works

    Public Function ProfessionLoad(ByRef lp As List(Of CProfession)) As List(Of CProfession)
        da = New OleDbDataAdapter
        Dim dr As DataRow
        Dim ds As New DataSet
        Dim CP As CProfession
        Try
            If cn.State = ConnectionState.Closed Then cn.Open()
            cmd = New OleDbCommand("Select ProfessionID, Profession From Professions ORDER BY Profession", cn)
            da.SelectCommand = cmd
            da.Fill(ds, "Professions")
            For Each dr In ds.Tables("Professions").Rows
                CP = New CProfession
                CP.Profession = CStr(dr("Profession"))
                CP.ProfessionID = CInt(dr("ProfessionID"))
                lp.Add(CP)
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return lp
    End Function