[RESOLVED] Add and populate Combobox into Datagridview

[Chrissy]

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?

         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.


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.

            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

[Chrissy]

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.

            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

[jmcilhinney]

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=