ComboBox shows the first item when loaded into new form VB.Net-CodePudding

I have two forms which are connected, the first one has a datagridview(DGV) that will show list of staff.

When I double click the row the data from the FGV will transfer into second form for user to edit the data. Each column from the DGV will insert at textbox combobox etc.

But the combobox on second form are using database when I double click the DGV; the others is fine but for combobox it will show the first data in database.

This is the code for the double click on the first form:

Private Sub DataGridView1_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick

    Dim frmEdit As New Edit

    frmEdit.lblEENo.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString
    frmEdit.txtName.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString
    frmEdit.txtAge.Text = DataGridView1.CurrentRow.Cells(3).Value.ToString
    frmEdit.lblAgeCategory.Text = DataGridView1.CurrentRow.Cells(4).Value.ToString
    frmEdit.txtGender.Text = DataGridView1.CurrentRow.Cells(5).Value.ToString
    frmEdit.cbEthnicity.Text = DataGridView1.CurrentRow.Cells(6).Value.ToString
    frmEdit.cbGrade.Text = DataGridView1.CurrentRow.Cells(7).Value.ToString
    frmEdit.cbCategory.Text = DataGridView1.CurrentRow.Cells(8).Value.ToString
    frmEdit.cbDepartment.Text = DataGridView1.CurrentRow.Cells(9).Value.ToString
    frmEdit.cbPosition.Text = DataGridView1.CurrentRow.Cells(10).Value.ToString
    frmEdit.txtReporting.Text = DataGridView1.CurrentRow.Cells(11).Value.ToString
    frmEdit.DateTimePicker1.Value = DataGridView1.CurrentRow.Cells(12).Value.ToString
    frmEdit.DateTimePicker2.Value = DataGridView1.CurrentRow.Cells(13).Value.ToString

    If DataGridView1.CurrentRow.Cells(14).Value.ToString = "Y" Then
        frmEdit.rbYes.Checked = True
    ElseIf DataGridView1.CurrentRow.Cells(14).Value.ToString = "N" Then
        frmEdit.rbNo.Checked = True
    End If

    frmEdit.cbStatus.Text = DataGridView1.CurrentRow.Cells(15).Value.ToString
    frmEdit.txtNote.Text = DataGridView1.CurrentRow.Cells(16).Value.ToString
    frmEdit.lblMody.Text = tslblLoginAs.Text

    frmEdit.ShowDialog()
    load_data()
End Sub

This is second form's code for connecting to the database table for the combobox:

Private Sub Edit_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim frmMasterList As New MasterStaff

    Dim poscmd As New SqlCommand("SELECT * from MasterPositionList", conn)
    Dim posadapt As New SqlDataAdapter(poscmd)
    Dim postable As New DataTable
    posadapt.Fill(postable)
    cbPosition.DataSource = postable
    cbPosition.DisplayMember = "PositionName"

    Dim depcmd As New SqlCommand("SELECT * from MasterDepartmentList", conn)
    Dim depadapt As New SqlDataAdapter(depcmd)
    Dim deptable As New DataTable
    depadapt.Fill(deptable)
    cbDepartment.DataSource = deptable
    cbDepartment.DisplayMember = "DeparmentName"
End Sub

CodePudding user response：

I am wondering why you need a separate form when you could edit the data directly in the DGV. When you think of it, all the data you're trying to edit is already found in the DGV (or should exist in its datasource). You can even have combo boxes inside the DGV, with their own bound datatables. Here is an example that shows how it could be done.

In Edit_Load there is unnecessary repetition. You load the same dataset twice. You could reuse it or even load it at startup and keep it in cache if it's not changing during application runtime.

Plus, rather than fetch values from the DGV, it would better to fetch the selected datarow instead. For this you just need the record ID... There is no need to access UI attributes, use the underlying datatable instead.

You shouldn't be using index numbers. Imagine the mess and potential for bugs if you want to insert columns or move columns. Also, the user probably can reorder columns in the DGV by drag & drop... thus your logic is ruined and the program will not behave like it should.

Anyway, to answer your issue: you've loaded a datatable, now all you have to do is bind it to the combo (which you did). You have used the DataSource and DisplayMember attributes. You could use SelectedValue to preselect a value in the list like this:

With cbPosition
    .DataSource = postable
    .DisplayMember = "PositionName"
    .ValueMember = "PositionName"
    .SelectedValue = "The text value that comes from the DGV"
End With

As long as the value from the DGV is present in the datatable this should work. Again, I think you should simplify your UI. All the data could be edited in-place in the DGV.

CodePudding user response：

The form load event fires too late to populate the combobox. The event is raised almost immediately when you create the form instance in the DataGridView1_CellDoubleClick() method, but the event handler can't actually run until control is returned to the messaging loop, and there's no chance for that to happen until the frmEdit.ShowDialog() line, which is after you have set the combobox values. Therefore the Load event runs after you have populated your desired value and will overwrite that work.

To fix this, move that Load code to the constructor, just after the InitializeComponent() method.

Additionally, since you want to select from the items provided by the database you should look at using the SelectedItem or SelectedValue property, instead of the Text property.

Finally, it's really poor practice to reuse the same connection object throughout an application or form. Yes, database connections are expensive objects to create and manage, but the ADO.Net library already handles this for you. The SqlConnection object you work with in code is a light-weight wrapper over a pool of the more-expensive actual raw connections. When you try to reuse one SqlConnection, you gain efficiency in the cheap thing while losing efficiency in the more expensive thing.

Instead, create (and promptly Dispose()!) a new SqlConnection object every time you need to use the database, and think about reducing connections by reducing round-trips to the database. In this case, you can combine the two SELECT statements into a single string and fill two DataTables in the same database trip:

Private Sub New ' Replaces the current Edit_Load() method
    InitializeComponent()

    'Using block will make sure the connection is closed, **even if an exception is thrown**
    ' Do NOT(!) try to reuse the connection. Only reuse the connection string.
    Using conn As New SqlConnection(connString), _
          ' Two SELECT queries in one string
          cmd As New SqlCommand("SELECT * from MasterPositionList;SELECT * from MasterDepartmentList", conn), _
          da As New DataAdapter(cmd)

        Dim results As New DataSet
        da.Fill(results)
        cbPosition.DisplayMember = "PositionName"
        cbDepartment.DisplayMember = "DeparmentName"
        cbPosition.DataSource = results.Tables(0) 'Filled two tables in one database call
        cbDepartment.DataSource = results.Tables(1)
    End Using
End Sub