Populate a VB combo box from a MS Access database row

Question

Im trying to add in a column entire row into a combobox. When i try it either shows only the last row of the column or it shows only the last row 35 times.

What am i missing? this is driving me crazy. i apologize for the commented lines, i was experimenting with some different coding.

Option Strict On
Imports System.Data.OleDb
Imports ShadowLogin.GlobalVariables
Public Class DeptNewSch

Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
    Me.Close()
    ShadowMain.Show()
End Sub

Private Sub DeptNewSch_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim CourseArray(35, 2) As String

    Using cnnOLEDB = New OleDbConnection(strConnectionString)
        Dim NewSchDS As DataSet
        Dim NewSchTables As DataTableCollection
        Dim NewSchsource1 As New BindingSource

        NewSchDS = New DataSet
        NewSchTables = NewSchDS.Tables

        Using cmd As New OleDbCommand("SELECT ClassSection.CourseTitle AS Course, ClassSection.SectionNumber AS [Section Number], ClassSection.ScheduleNumber AS [Schedule Number], Course.ShortTitle AS [Course Title], Course.Units, ClassSection.Type AS Format, ClassSection.TimeSlot AS [Days / Time], ClassSection.RoomID AS Location, Faculty.[First Name], Faculty.[Last Name], Classroom.MaxCapacity AS Seats FROM [Classroom], [ClassSection], [Faculty], [Course] WHERE (ClassSection.FRedID = Faculty.FRedID) AND Course.CourseTitle = ClassSection.CourseTitle AND Classroom.RoomID = ClassSection.RoomID AND ClassSection.ScheduleYear=@Year AND ClassSection.Semester=@Semester;", cnnOLEDB)
            cmd.Parameters.Add("@Year", OleDbType.VarWChar).Value = Convert.ToInt32(strYear.ToString())
            cmd.Parameters.Add("@Semester", OleDbType.VarWChar).Value = strSemester.ToString()

            Using NewSchDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                NewSchDA.Fill(NewSchDS, "New Semester") 'Change items to your database name
                Dim view As New DataView(NewSchTables(0))
                NewSchsource1.DataSource = view
                dgNewSch.DataSource = view
                Me.dgNewSch.Columns("Course").Width = 60
                Me.dgNewSch.Columns("Section Number").Width = 50
                Me.dgNewSch.Columns("Schedule Number").Width = 60
                Me.dgNewSch.Columns("Course Title").Width = 190
                Me.dgNewSch.Columns("Units").Width = 40
                Me.dgNewSch.Columns("Format").Width = 70
                Me.dgNewSch.Columns("Days / Time").Width = 100
                Me.dgNewSch.Columns("Location").Width = 70
                Me.dgNewSch.Columns("First Name").Width = 80
                Me.dgNewSch.Columns("Last Name").Width = 80
                Me.dgNewSch.Columns("Seats").Width = 50
            End Using
        End Using
    End Using

    'Connect to Database and get the registration information
    Using cnnOLEDB = New OleDbConnection(strConnectionString)


        ' Query the classschedule table for start of semester datas
        Using cmdOleDB = New OleDbCommand("SELECT [CourseTitle], [ShortTitle],[Units] FROM [Course] WHERE rownumber =@row", cnnOLEDB)
            cnnOLEDB.Open()
            Using rdrOLEDB = cmdOleDB.ExecuteReader
                While rdrOLEDB.Read
                    Dim l As Integer

                    For l = 0 To 35
                        'cmdOleDB.Parameters.Add("@row", OleDbType.VarWChar).Value = l
                        CourseArray(l, 0) = rdrOLEDB.Item(0).ToString
                        CourseArray(l, 1) = rdrOLEDB.Item(1).ToString
                        CourseArray(l, 2) = rdrOLEDB.Item(2).ToString
                    Next




                    ' CboFormat.Items.Add()
                    ' CboDayTime.Items.Add()
                    ' CboLocation.Items.Add()
                    ' CboName.Items.Add()
                End While
            End Using
        End Using
    End Using

    For z As Integer = 0 To 35

        CboCourse.Items.Insert(z, CourseArray(z, 0).ToString)

    Next

End Sub

End Class

As you can guess this is the code that ive been working on. Any help would be great if what im trying to do is at all possible.

Is the loop anticipating precisely 35 records? Depending on the data, the CBO could be bound to a List containing the rows read like with the DGV — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 12:21
...a `List(Of Course)` or just about anything would be better than a 2D array. [ See example](http://stackoverflow.com/a/34164458/1070452) — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 12:33
so are you saying that its better to just write in the individual lines of courses? (sorry still haven't slept yet) — James Swan, Dec 09 '15 at 14:30
No, I'd load the data from the db, but I would use something other than an array to store the data. The link shows someone doing pretty much what you are, and an alternative. Also, expecting exactly 35 Courses is pretty bad. — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 14:33
Appreciate the help and i will look into the List(Of Course) link you sent me. The project is a shadow system with limited data that we were given, so since we are not creating new courses it has a set limit of 35 courses — James Swan, Dec 09 '15 at 14:35
Doesnt matter. It is just as easy to do it right (in a loop). Anticipating exaclly 35 is what caused the dupes — Ňɏssa Pøngjǣrdenlarp, Dec 09 '15 at 14:38

score 0 · Accepted Answer · answered Dec 09 '15 at 11:04

0

Your 0 to 35 loop runs each time you read a record in the datasource.

While rdrOLEDB.Read
                    Dim l As Integer

                    For l = 0 To 35
                        'cmdOleDB.Parameters.Add("@row", OleDbType.VarWChar).Value = l
                        CourseArray(l, 0) = rdrOLEDB.Item(0).ToString
                        CourseArray(l, 1) = rdrOLEDB.Item(1).ToString
                        CourseArray(l, 2) = rdrOLEDB.Item(2).ToString
                    Next
Name.Items.Add()
                End While

answered Dec 09 '15 at 11:04

nicomp

4,344
4
27
60

is there a way to do the loop without recording over the previous data? – James Swan Dec 09 '15 at 11:07
Why do you need the loop?Just increment l each pass through the While loop. – nicomp Dec 09 '15 at 11:09
i should ask if theres a better way to do this? – James Swan Dec 09 '15 at 11:11
It's OK but I'd add the items directly to the Names object from inside the While loop rather than build an array of strings. – nicomp Dec 09 '15 at 11:13
Im getting the course name to populate the combo box from a database, so if the database is updated then this cbo should be updated with the new course name. (apologize if this sounds weird, been at this all day and my brain is turning to mush) – James Swan Dec 09 '15 at 11:16
I understand what you're doing, I don't think you need to build an array of strings: just add to the combo box inside the While loop. – nicomp Dec 09 '15 at 11:30

Populate a VB combo box from a MS Access database row

1 Answers1