[RESOLVED] Add and populate Combobox into Datagridview

Hello,

Here is what I am trying to do:

• Populate a datagridview with data from the database

• Use 1st column in the returned data to populate a combobox column in the datagridview

• Allow user to change the selection of the combobox and write the update back to the database

I am populating the DGV using a datatable... here is my code. I am familiar with displaying data in the grid, but not much else. I can get the combobox column to show up with the selections, but how can I populate the combobox using the data being pulled into the datagrid? Do I have to populate the datagridview row by row and programatically "select" the value from the dropdown that matches the data?

Code:

---

        Dim rsLeads As New SQLControl

            rsLeads.ExecQuery("SELECT u.salesperson_name, rtrim(Lead_ID) 'Lead_ID', rtrim(infogroup_id) InfoGroup_Id, rtrim(Lead_status) 'Lead Status', rtrim(Company_Name) 'Company Name', rtrim(mailing_address) Address,
                            rtrim(Mailing_City) + ', ' + rtrim(Mailing_State) State, l.date_created 'Date Created' FROM Leads l , users u WHERE u.User_Id = l.User_ID")

            If rsLeads.HasException Then
                MsgBox(rsLeads.Exception, MsgBoxStyle.OkOnly, "Error Loading Leads")
            End If

            If rsLeads.RecordCount = 0 Then
                MsgBox("No leads found.", MsgBoxStyle.OkOnly, "No Records")
                Exit Sub
            End If

            DgvLeads.DataSource = rsLeads.DBDT

            Dim Sql As New SQLControl
            Sql.ExecQuery("SELECT rtrim(salesperson_name) xName, user_id FROM Users Order by salesperson_name ")

            Dim cmb As New DataGridViewComboBoxColumn()
            cmb.HeaderText = "Assign User"
            cmb.Name = "cmb"
            cmb.DataSource = Sql.DBDT
            cmb.ValueMember = "user_id"
            cmb.DisplayMember = "xName"

            DgvLeads.Columns.Insert(1, cmb)

---

This is how my data looks. Basically I want the salesperon column to be populated in the "Assign User" column.
Attachment 192916

I have tried multiple variations and can't seem to get what I am looking for.

Thank you in advance for any help you can offer.

UPDATE:
I rearranged my code to this and it works EXCEPT I get an error every time it updates the combobox.

Code:

---

            Dim Sql As New SQLControl
            Sql.ExecQuery("SELECT rtrim(salesperson_name) xName, user_id FROM Users Order by salesperson_name ")

            Dim cmb As New DataGridViewComboBoxColumn()
            cmb.HeaderText = "Assign User"
            cmb.Name = "cmb"
            cmb.DataSource = Sql.DBDT
            cmb.ValueMember = "user_id"
            cmb.DisplayMember = "xName"

'I added this line of code that corresponds to the name in my datatable when populating
            cmb.DataPropertyName = "xName"

            DgvLeads.Columns.Insert(0, cmb)

            Dim rsLeads As New SQLControl

            'rsLeads.AddParam("@user", globalUserID)
            rsLeads.ExecQuery("SELECT rtrim(salesperson_name) xName, rtrim(Lead_ID) 'Lead_ID', rtrim(infogroup_id) InfoGroup_Id, rtrim(Lead_status) 'Lead Status', rtrim(Company_Name) 'Company Name', rtrim(mailing_address) Address,
                            rtrim(Mailing_City) + ', ' + rtrim(Mailing_State) State, l.date_created 'Date Created' FROM Leads l , users u WHERE u.User_Id = l.User_ID" + sqlFilter)

            If rsLeads.HasException Then
                MsgBox(rsLeads.Exception, MsgBoxStyle.OkOnly, "Error Loading Leads")
            End If

            If rsLeads.RecordCount = 0 Then
                MsgBox("No leads found.", MsgBoxStyle.OkOnly, "No Records")
                Exit Sub
            End If

            DgvLeads.DataSource = rsLeads.DBDT

---

This is the error I get
Attachment 192917

I was able to resolve on my own. Here is the final code in case anyone else has this same issue. Now I need to find an easy way to write back to the database when the combobox is changed.

Code:

---

            Dim Sql As New SQLControl
            'Querey to populate the collection of the combobox
            Sql.ExecQuery("SELECT rtrim(salesperson_name) xName, user_id FROM Users Order by salesperson_name ")

            'Create and add datasource from above query
            Dim cmb As New DataGridViewComboBoxColumn()
            cmb.HeaderText = "Assign User"
            cmb.Name = "cmb"
            cmb.DataSource = Sql.DBDT
            cmb.ValueMember = "user_id"
            cmb.DisplayMember = "xName"
            cmb.DataPropertyName = "xSales"

            DgvLeads.Columns.Insert(0, cmb)

            Dim rsLeads As New SQLControl

            'The column user_id in the query to populate the datagridview needs to be the same as the datatype as the ValueMember in the combobox.
            rsLeads.ExecQuery("SELECT l.user_id xSales, rtrim(Lead_ID) 'Lead_ID', rtrim(infogroup_id) InfoGroup_Id, rtrim(Lead_status) 'Lead Status', rtrim(Company_Name) 'Company Name', rtrim(mailing_address) Address,
                            rtrim(Mailing_City) + ', ' + rtrim(Mailing_State) State, l.date_created 'Date Created' FROM Leads l , users u WHERE u.User_Id = l.User_ID" + sqlFilter)

            If rsLeads.HasException Then
                MsgBox(rsLeads.Exception, MsgBoxStyle.OkOnly, "Error Loading Leads")
            End If

            If rsLeads.RecordCount = 0 Then
                MsgBox("No leads found.", MsgBoxStyle.OkOnly, "No Records")
                Exit Sub
            End If

            DgvLeads.DataSource = rsLeads.DBDT

---

You may or may not benefit from it now but you might like to check out my CodeBank thread on this subject:

https://www.vbforums.com/showthread....iew&highlight=