Combobox with values from database won't display passed value from Datagridview row even if the-CodePudding

I have a Combobox1 in a form that loaded with values from database. The values loaded perfectly into the Combobox1. In the process of updating the info of a student, I have a row in the datagridview that has a button "Edit", when I click the "Edit" button, it shows up a form with textboxes and comboboxes that loaded with all the values from the Datagridview row, pertaining to that specific student. In the form load event, I loaded data from database going to that Combobox1. When the form shows, the Combobox1 display the first item from the database query, not the value from the Datagridview that I selected to pass to the Combobox1 even if they are they same as the item from the query.

Here is the code for fetching data

 Public Sub LoadSections(cb As ComboBox)

        Try
            sql = "SELECT ID, section_name FROM slm_sections WHERE school_id = @SCHOOLID ORDER BY section_name ASC"
            dbconnect()
            conn.Open()

            cmd = New MySqlCommand(sql, conn)
            cmd.Parameters.AddWithValue("@SCHOOLID", My.Settings.SchoolID)

            Dim adptr As New MySqlDataAdapter(cmd)
            Dim table As New DataTable()

            adptr.Fill(table)
            cb.DataSource = New BindingSource(table, Nothing)
            cb.DisplayMember = "section_name"
            cb.ValueMember = "ID"

            cmd.Dispose()
            adptr.Dispose()
            conn.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

In passing values from datagridview to the Combobox1, here is my code

            Dim frm2 As New FormAddNewStudent
            With frm2

                frm2.Text = "Update Student Info"

                LoadGradeLevels(.CbGradeLevel)
                LoadSections(.CbSection)
                LoadStrands(.CbStrand)

                .TxtLastName.Text = StudentList.SelectedCells(5).Value.ToString
                .TxtFirstName.Text = StudentList.SelectedCells(6).Value.ToString
                .TxtMiddleName.Text = StudentList.SelectedCells(7).Value.ToString

                .TxtLRN.Text = StudentList.SelectedCells(8).Value.ToString

                .CbGender.SelectedItem = StudentList.SelectedCells(9).Value.ToString
                .CbGradeLevel.SelectedItem = StudentList.SelectedCells(10).Value.ToString
                .CbSection.SelectedItem = StudentList.SelectedCells(11).Value.ToString
                .CbStrand.SelectedItem = StudentList.SelectedCells(12).Value.ToString
                .BtnSave.Text = "Update"



            End With
            frm2.ShowDialog()

The Studentlist is the Datagridview, the StudentList.SelectedCells(1).Value.ToString is where the data that I need to be loaded into the Combobox1.

CodePudding user response：

It was going well up to the point where you started digging data out of the DataGridView using its selected cells collection- at that point the "code is using data binding; it performs manipulation of the data via the data model" broke down.

I think the simplest thing to do would be to arrange all the controls on your edit form so they are bound to the same bindingsource X, and then pass the bindingsource Y that the grid is bound to, to the second form and bind X to Y. Pictorially what we'll arrange looks like:

The main form will have a grid, bound to a BS, bound to a datatable. The second form will have controls bound to a BS bound to the main form's BS. The combo on the edit form gets its list items from a separate data source, and uses them to alter the ID of the student's section in the main table

Writing binding code is repetitive and fairly boring, so I'll give a quick intro on how to get the windows form designer to do it, and I'll throw in a bit that makes writing your SQLs/getting stuff out of/saving to a database easier too:

(Advance note, the process here will only work properly on .net framework; if your project is .net core you'll struggle because data binding design is quite broken/incomplete in net core )

Add a DataSet type of file to the project
Open it, right click the surface and choose Add TableAdapter
Set the connection string up , put the query that pulls your student data, finish the wizard. From now on I'll refer to this as the "Student" datatable (I don't know what it's called in your db)
Right click the surface again, Add TableAdapter, same conn str, query of SELECT ID, section_name FROM slm_sections WHERE school_id = @SCHOOLID ORDER BY section_name ASC and finish. I'l lrefer to this as the Sections datatable
Go to a new form (to avoid disturbing your existing code) and open the Data Sources window (View Menu, Other windows)
Drag the Student node to the form. Some things appear in the tray, header and a grid on the form
Make a new form
Drop the Student node down in Data Sources and change it from Grid to Details
Change the Section (under Student) from textbox to combobox
Drag the Student node to the form, multiple controls appear all bound to the same bindingsource
Drag the Section node to the form, but delete the grid it makes - we don't need it, but we do need a couple other things it makes
On the combo:
- Set DataSource to SectionBindingSource
- Set Display/Value member appropriately
- Expand (DataBindings) at the top of the proeprty grid and look at what is binded for Text - make the same thing in SelectedValue and then right click Text and choose Reset (the Text binding is erroneous, it needs to be SelectedValue that is bound to Students.SectionID)
Go to code view on this form 2
Remove the fill codes in the Load event - we don't need them because we will reuse the data the main form downloaded
Put a constructor that we can send a BindingSource into:

    Public Sub New(toUse As BindingSource)

        InitializeComponent()

        StudentsBindingSource.DataMember = Nothing
        StudentsBindingSource.DataSource = toUse
    End Sub

In the main form change the code that shows the Edit form so that it sends the StudentBindingSource from the main form into the edit form:

    Dim f as New ...WhateverForm(StudentsBindingSource) 
    f.Show()

The edit form will receive the bindingsource and bind its own bindingsource to it, chaining the two together. This means the controls on the edit form will read data from/push data into the dataset/datatable/bindingsource form the main form, so there is a single source of data that both grid and controls read from and edit

Now, you don't have to do all this, making a dataset and tableadapters - the binding part doesnt rely on those, it's just that writing binding code by drag drop from the data sources window is easy

You could look at the latter part of the advice above, make a constructor in the eidt form that accepts a BS, pass a BS, and in that constructor you can set the Section combo of the edit form to have a binding like:

Me.SectionComboBox.DataBindings.Add(New Binding("SelectedValue", toUse, "SectionId", True))

Plus setting the other stuff (datasource/disp/val) to an existing list of data (you clearly know how to get some sections from the DB) which means the edit form's combo will be directly bound to the BS from the main form. It doesn't matter so much if theyre chained or direct; I chained them with the stuff the forms designer created because it's easier than rebinding every control on the form to the toUse bindingsource passed form the main