Retrieving index of combobox in datagridview for update to DB-VBForums
Results 1 to 6 of 6

Thread: Retrieving index of combobox in datagridview for update to DB

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    254

    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!!

    Thank you in advance.
    Chrissy

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,352

    Re: Retrieving index of combobox in datagridview for update to DB

    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.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,579

    Re: Retrieving index of combobox in datagridview for update to DB

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    254

    Re: Retrieving index of combobox in datagridview for update to DB

    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.

    Name:  FullScreenShot.jpg
Views: 31
Size:  9.5 KB

    Will binding still work?

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    96,579

    Re: Retrieving index of combobox in datagridview for update to DB

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Aug 2004
    Location
    Itasca, IL USA
    Posts
    254

    Re: Retrieving index of combobox in datagridview for update to DB

    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
    Name:  Tables.jpg
Views: 26
Size:  19.6 KB

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

    Name:  Data.jpg
Views: 25
Size:  25.3 KB
    Last edited by Chrissy; Apr 20th, 2017 at 11:08 AM. Reason: previewed... got an error and it posted automatically!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.