[RESOLVED] System.IndexOutOfRangeException

[gwboolean]

I am not sure whether this should be under .NET or Database.

I am attempting to populate a combobox from a table contained in the database being used for a project.

The query is no problem. It recovers the 8 records currently residing in the table lkpDept.

The For/Next code I obtained from a reference source and I do not fully understand its workings. When the method is executed I get an exception, "System.IndexOutOfRangeException: 'Cannot find table 0.'"
From the line, For Each...

    Private Sub GetDeptList()
        MyQuery = "Department ComboBox query"
        cboOwner.Items.Clear()
        MasterBase.MasterBaseQuery("SELECT colDept FROM lkpDept")
        If RecordCount > 0 Then
            For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows
                cboOwner.Items.Add(r("colDept"))
            Next
            cboOwner.ValueMember = "colDept"
            cboOwner.DisplayMember = "colDept"
            cboOwner.SelectedIndex = -1
        ElseIf MasterBase.Exception <> "" Then
            MsgBox(MasterBase.Exception)
        End If
    End Sub

I surmise that there might be a problem with ListDataSet, but I have no idea what that might be.

ListDataSet is defined in the MasterBaseConn class as shown below. All I can think of is that I have somehow done something wrong in making the dataset, but I do not know what that could be.

    Public Class MasterBaseConn
        Public MasterBaseConnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MasterBase4.0.accdb;") 'Database Connection
        Public ListCommand As New OleDbCommand
        Public ListAdapter As OleDbDataAdapter
        Public ListTable As DataTable
        Public ListDataSet As DataSet
        Public Params As New List(Of OleDbParameter)
        Public Exception As String
        Public Sub MasterBaseQuery(SetQuery As String)
            RecordCount = 0
            Exception = ""
            Try
                MasterBaseConnection.Open() 'Open connection
                ListCommand = New OleDbCommand(SetQuery, MasterBaseConnection) 'Database Command
                Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command
                Params.Clear() 'Clear params list
                ListDataSet = New DataSet
                ListTable = New DataTable
                ListAdapter = New OleDbDataAdapter(ListCommand)
                RecordCount = ListAdapter.Fill(ListTable)
            Catch ex As Exception
                Exception = ex.Message
                MsgBox(ex.Message + vbLf + vbCrLf + MyQuery + " failed to execute.")
            End Try
            If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
        End Sub
        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New OleDbParameter(Name, Value)
            Params.Add(NewParam)
        End Sub
    End Class

[.paul.]

IndexOutOfRangeExceptions occur when you try to access an index in a collection that doesn’t exist…

Examples are…

Dim myList As New List(Of String)
MsgBox(myList(0)) ' doesn’t exist

myList.Add("one")
myList.Add("two")
myList.Add("three")

MsgBox(myList(3))) ' doesn’t exist

[wes4dbt]

There is nothing in the code you posted that adds any datatables to the ListDataSet.

[gwboolean]

The content that I obtained this method for populating the combobox did not have anything that I could see that directly tied ListDataSet directly to the table. They were able to work the method flawlessly. Admittedly the content was for an SQLce database, but I thought I had covered all of that for the ACCESS database I am using.

So what is it that I should do to cover this? Does one do some type of fill command like with ListAdapter?

[.paul.]

RecordCount = ListAdapter.Fill(ListTable)
ListDataSet.Tables.Add(ListTable)

[TysonLPrice]

Silly question...should the loop start at 1?

[.paul.]

Silly question...should the loop start at 1?

For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows

This was the problem… ListDataSet didn’t contain any tables

[gwboolean]

RecordCount = ListAdapter.Fill(ListTable)
ListDataSet.Tables.Add(ListTable)

That got me there Paul. I added the line,

ListDataSet.Tables.Add(ListTable)

to my connection class (MasterBaseConn).

After removing the combobox.ValueMember and the combobox.DisplayMember from the GetDeptList() routine the for Each Next loop ran exactly as expected and the combobox was able to do it's job. Apparently, with caveats.

The combobox is handled using the routine GetDeptList(), shown below.

    Private Sub GetDeptList()
        MyQuery = "Department ComboBox query"
        cboDept.Items.Clear()
        MasterBase.MasterBaseQuery("SELECT colDept FROM lkpDept")
        If RecordCount > 0 Then
            For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows
                cboDept.Items.Add(r("colDept"))
            Next
            cboDept.DataSource = MasterBase.ListDataSet
            'cboDept.ValueMember = "colDept"
            'cboDept.DisplayMember = "colDept"
            cboDept.SelectedIndex = -1
        ElseIf MasterBase.Exception <> "" Then
            MsgBox(MasterBase.Exception)
        End If
    End Sub

This above routine is called from SetState()

    Private Sub SetState(ByVal appstate As String)
#Region "Color Pallet"


#End Region
#Region "Default Form Properties"
        Text = "Employee Record"
#End Region
#Region "Default Button Properties"
        btnEdit.Enabled = True
        btnSave.Enabled = True
        btnScan.Enabled = False
        btnCancel.Enabled = True
        btnClose.Enabled = True
        btnHelp.Enabled = False
#End Region
#Region "Default Control Properties"
        pnlRecord.Enabled = True
        lblStaffID.Enabled = False
        txtFirst.ReadOnly = False
        txtMiddle.ReadOnly = False
        txtLast.ReadOnly = False
        txtTitle.ReadOnly = False
        txtEnd.ReadOnly = False
        cboDept.Enabled = True
        cboDept.Sorted = True
        GetDeptList()
        txtDetail.ReadOnly = False
        rdoActive.Enabled = True
        rdoObsolete.Enabled = True
#End Region
        Select Case appstate
#Region "Case Add"
            Case "Add"
                btnEdit.Enabled = False
                btnClose.Enabled = False
                cboDept.SelectedIndex = -1
#End Region
#Region "Case Edit"
            Case "Edit"
                btnEdit.Enabled = False
                btnClose.Enabled = False
#End Region
#Region "Case View"
            Case "View"
                btnCancel.Enabled = False
                btnSave.Enabled = False
                txtFirst.ReadOnly = True
                txtMiddle.ReadOnly = True
                txtLast.ReadOnly = True
                txtTitle.ReadOnly = True
                txtEnd.ReadOnly = True
                cboDept.Enabled = False
                txtDetail.ReadOnly = True
                rdoActive.Enabled = False
                rdoObsolete.Enabled = False
#End Region
        End Select
    End Sub

After I created the new record and used the combobox, I closed that form and returned to the list form, which now showed the newly created record in DGVList on the form. I clicked the record in the DGVList which opened the record form to that record and it was here that the error occurred when SetState() was called.

I read the exception as telling me that the combobox setting is a one time thing and cannot be recreated. That does not seem right to me. If you can tell me where/what I am still doing wrong you could save what little hair I have left, after already pulling most of it out over this.

[.paul.]

Private Sub GetDeptList()
    MyQuery = "Department ComboBox query" ' ???
    cboDept.Items.Clear()
    MasterBase.MasterBaseQuery("SELECT colDept FROM lkpDept")
    cboDept.ValueMember = "colDept"
    cboDept.DisplayMember = "colDept"
    cboDept.DataSource = MasterBase.ListDataSet.Tables(0)
    cboDept.SelectedIndex = -1
End Sub

[gwboolean]

MyQuery is nothing but a variable used for additional content in an exception message, should the query fail. Kind of makes it easy for me to know exactly which query failed. It has nothing to do with the combobox. Still trying to figure out why I can only use this when I add a new record and not when I am opening a record to either view or edit.

[gwboolean]

OK, I can get this to almost work, but I am just unable to completely clean this up. To be able to make it work at all, I have found that I have to be very careful where and when I populate the combobox. I have no idea why. So here is where this is currently at.

Currently, everything in the query works correctly and the rows are created and contain the data from lkpDept. When GetDeptList() is run with the three lines noted below REM'd what occurs is that the ComboBox displays, "System.Data.DataViewManagerListItemTypeDescription". This I would expect with the ValueMember, DisPlayMember not being declared.

However, if the three properties are declared (the REM removed), I get a System.ArgumentException stating, "Cannot bind to the new display member."

Any suggestions as to where I am going wrong and how I should approach this?

    Private Sub GetDeptList()
        cboDept.Items.Clear()
        MyQuery = "Department ComboBox query"
        MasterBase.MasterBaseQuery("SELECT colDept FROM lkpDept")
        If RecordCount > 0 Then
            For Each r As DataRow In MasterBase.ListDataSet.Tables(0).Rows
                cboDept.Items.Add(r("colDept"))
            Next
            cboDept.DataSource = MasterBase.ListDataSet
            'cboDept.ValueMember = "colDept"
            'cboDept.DisplayMember = "colDept"
            'cboDept.Sorted = True
        ElseIf MasterBase.Exception <> "" Then
            MsgBox(MasterBase.Exception)
        End If
    End Sub

[gwboolean]

Well, I just resolved the whole thing. However, it requires that I fess up to being not the sharpest tool in the shed.

Bottom line is that everything works exactly as required if all four of the combobox properties are deleted from GetDeptList(). I missed eliminating the DataSource property.

I think I understand why this is. My guess is that the combobox is already populated.