Retrieving index of combobox in datagridview for update to DB

Hello,

Most of my experience in in VB6... I am trying to convert to VB.NET. I have visual studio 2013.

I have a datagridview with multiple comboboxes. The idea if for the user to select values for all combos and then click the "Save" button to update the database.

I am only storing the numeric equivalent which refers to the index. I cannot figure out how to retrieve the index of the selection in each cell. I was thinking somehow I could use ENUM but can't figure out how to use that either.

Attachment 146835

The database field "percent_type" holds a value of 0, 1 or 2 which corresponds to the choices in the drop down list.

I highlighted the code that refers to my problem area.

Code:

---

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim db As New DataAccess.sqlDataBase("hp_accting")
        Dim dbReader As New DataAccess.sqlDataBase("hp_accting")

        Dim xColumn As Integer = 1

        Try
            Do While xColumn < dtgAllocation.ColumnCount()

                For xAllocation As Integer = 0 To 13
                    Dim colcmb As DataGridViewColumn = DirectCast(dtgAllocation.Columns(xColumn), DataGridViewColumn)
                    Dim cmb As DataGridViewComboBoxCell = DirectCast(dtgAllocation.Rows(xAllocation).Cells(xColumn), DataGridViewComboBoxCell)
 
                    Dim readerCheck As SqlClient.SqlDataReader = dbReader.GetRecords("tax_owner_allocations", "*", "allocation_code = " &
                    xAllocation.ToString & " AND ocode ='" & colcmb.Name.Trim.ToString & "' AND entity_num = '" & cmbEntity.Text.Substring(0, 8).Trim &
                    "' AND projection_year = '" & cmbYear.Text & "'")

                    If readerCheck.HasRows Then
                        db.UpdateRecordField("UPDATE tax_owner_allocations SET percent_type = " & cmb.Value & " WHERE allocation_code = " &
                            xAllocation.ToString & " AND ocode ='" & colcmb.Name.Trim.ToString &
                            "' AND entity_num = '" & cmbEntity.Text.Substring(0, 8).Trim.ToString & "' AND projection_year = '" & cmbYear.Text.ToString & "'")
                    Else
                        db.InsertRecord("tax_owner_allocations", "ocode, entity_num, projection_year, allocation_code, percent_type", "'" & colcmb.Name.Trim &
                                        "', '" & cmbEntity.Text.Substring(0, 8).Trim.ToString & "', '" & cmbYear.Text & "'," & xAllocation.ToString & ", " & cmb.RowIndex.ToString)
                    End If

                    readerCheck.Close()
                    colcmb.Dispose()
                    cmb.Dispose()
                    readerCheck = Nothing
                Next

                xColumn += 1

            Loop
            db = Nothing

            MsgBox("Update was successful!", MessageBoxButtons.OK, "Update Complete")

        Catch ex As Exception

            MsgBox("An error occured while updating this record.", MsgBoxStyle.OkOnly, "Update Cancelled")

        End Try

    End Sub

---

I have scoured the internet and tried many different things... this is my last resort... please help!! :confused:

Thank you in advance.
Chrissy

You can't get the SelectedIndex directly without adding an Event Handler (as far as I know).

Here is some old code of mine, it doesn't get the SelectedIndex but uses the Selectedvalue,

Code:

---

    Private Sub LOT_CHRGDataGridView_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles LOT_CHRGDataGridView.EditingControlShowing

        Dim cb As ComboBox = TryCast(e.Control, ComboBox)
        If cb IsNot Nothing Then
            Dim editingComboBox As ComboBox = DirectCast(e.Control, ComboBox)
            ' Remove an existing event-handler, if present, to avoid
            ' adding multiple handlers when the editing control is reused.
            RemoveHandler editingComboBox.SelectedIndexChanged, _
                New EventHandler(AddressOf editingComboBox_SelectedIndexChanged)
            ' Add the event handler.
            AddHandler editingComboBox.SelectedIndexChanged, _
                New EventHandler(AddressOf editingComboBox_SelectedIndexChanged)
        End If

    End Sub
    Private Sub editingComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim dr As DataRow()
        Dim dgvc As DataGridViewCell = TryCast(Me.LOT_CHRGDataGridView.CurrentCell, DataGridViewCell)

        Dim comboBox1 As ComboBox = CType(sender, ComboBox)
        Dim cb As ComboBox = TryCast(comboBox1, ComboBox)
        If cb IsNot Nothing Then
            If dgvc IsNot Nothing Then
                If comboBox1.SelectedValue IsNot Nothing Then
                    dr = Me.MillingDataDataSet.CHARGES.Select("desc='" & comboBox1.SelectedValue.ToString & "'")
                    If dr.GetUpperBound(0) <> -1 Then
                        Me.LOT_CHRGDataGridView.CurrentRow.Cells("code").Value = dr(0)("code").ToString
                        GetChargePoundsAndAmount(dr(0)("code").ToString)
                    End If
                End If
            End If
        End If
    End Sub

---

I don't have time right now to clean it up but you should be able to figure it out. From this added event you could store the index in a Form level variable for use later. Instead of this you could create a Dictionary to hold the ComboBox values and corresponding index then look the index up using the combobox value just before you save the data.

One thing I would like to say is I'm not sure what your doing is a good idea. What happens if in the future for some unforeseen reason you have to add or remove an item from the combobox list, that would make all the stored index numbers wrong.

You appear to be going about this all wrong. Most of this should happen automatically, if you have set everything up correctly.

You should start with two related tables in your database. Let's take a simple example like gender. You would have a Gender table with GenderId and Name columns and then you would have a Person table with a GenderId column that is a foreign key to the Gender table.

You can then query your database and populate a DataTable with your Person data. If you bind that DataTable to a DataGridView, you will end up with a text box column with the header text "GenderId" and you will see the numerical GenderId values in the column. What you can do instead is add a combo box to your grid and bind it to a second DataTable containing the data from the Gender table. If you set up the bindings properly, the user will then see the values from the Name column of the Gender table in the grid. The appropriate Name value will be automatically selected for each Person record. When the user selects a Gender Name value in a combo box, the corresponding GenderId will automatically be pushed to the Person record in that row. You don't have to do anything.

What is actually happening is that the data-binding system is moving data back and forth between the Value property of the cell, which contains a GenderId from the Person record, and the SelectedValue property of the ComboBox control, which contains the GenderId from a Gender record. When the grid is populated with Person records, the appropriate Gender record is selected based on the corresponding GenderId values and the Name displayed. When the user starts editing a cell, a ComboBox control is created and embedded in the cell. The Value of the cell is assigned to the SelectedValue of the control and the corresponding Name is displayed. If the user changes the selection then the SelectedValue property changes. When the user ends the editing session, the ComboBox control is removed from the cell and its SelectedValue property is assigned to the Value of the cell.

After all that, all you have to do is save the DataTable bound to the grid and all the correct GenderId values will be saved to the database without you having to write ANY code to interact with ANY ComboBox control. You might like to follow the CodeBank link in my signature below and check out my thread on Adding A ComboBox Column To A DataGridView.

Thank you Jmcilhinney... The concept just seems so foreign to me! I know I need to change the way I think about coding with .Net versus vb6, but it is difficult for me.

I will take a look at the link and give it a try.

A bit more info... The grid gets populated based on selections made. I attached a screen shot that shows what my form looks like before being populated. Also, the number of columns and the header name for the columns is dynamic based on the selections made.

Attachment 146869

Will binding still work?

If you have several tables whose data you want to display in the grid and you want to change which is displayed, you can unbind, clear the Columns collection and then bind again.

I am still confused how to do this with the data I need to show and update.

Here is the query for all the information I need to show in the grid.

Code:

---

select p.name, t.ocode, t.Percent_Type, a.percent_name, ta.Allocation_Code, ta.Allocation_Namefrom partner p,
Ownership o LEFT OUTER JOIN Tax_Owner_Allocations t on o.ocode = t.ocode
LEFT OUTER JOIN Tax_Allocation ta on t.Allocation_Code = ta.Allocation_Code
LEFT OUTER JOIN Tax_Allocation_Percent a on t.Percent_Type = a.percent_ID
and o.Entity_Num = '10670'
where p.ocode = o.ocode and o.Tax_Projection = '2015-3'
order by ocode, ta.Allocation_Code

---

Attachment 146873

This is how I need the data to be displayed... The column names are the fields p.name and t.ocode.

Attachment 146875