Combo Drop Box in datagrid loaded with data

I know how to add a combo drop box to my datagrid but how do i populate the data grid with data from a table so that the cell of the combo box is already populated with the data from the table but can be edited.  I have some code done that creates the table but doesn't add the data from the table.

                    ''edit/delete items in a department order guide
                    ''loaddatagrid("SELECT ourorderguide.ID, ourorderguide.Company, thierorderguide.ItemDescription, ourorderguide.AccountName, ourorderguide.Location, ourorderguide.NotInDepartment, thierorderguide.LastUpdated FROM thierorderguide INNER JOIN ourorderguide ON thierorderguide.ItemNumber = ourorderguide.ItemNumber WHERE (((ourorderguide.DepartmentName)= '" & lbDepartmentName.SelectedItem.ToString & "'))")
                    dsadditems = New DataSet
                    Dim DT As New DataTable
                    With DT.Columns
                        .Add("ID")
                        .Add("Company")
                        .Add("ItemDescription")
                        ''account name will be added later
                        ''loction will be added here later
                        .Add("NotInDeparment")
                        .Add("LastUpdated")
                    End With
                    ''copy toable to dataview
                    dsadditems.Tables.Add(DT)
                    dgadditem.DataSource = dsadditems.Tables(0)
                    dvdataview = New DataView(dgadditem.DataSource)
                    dgadditem.Columns.Clear()
                    dgadditem.DataSource = dvdataview
                    'add a location drop box
                    Dim ds As New DataSet
                    Dim datest As New OleDb.OleDbDataAdapter("Select ID,departmentcode from departments where department = '" & lbDepartmentName.SelectedItem.ToString & "'", mainform.dbconnection)
                    datest.Fill(ds)
                    Dim departmentID As Integer = ds.Tables(0).Rows(0).Item(0)
                    ds = New DataSet
                    datest = New OleDb.OleDbDataAdapter("Select location from locations where departmentID = " & departmentID, mainform.dbconnection)
                    datest.Fill(ds)
                    If ds.Tables(0).Rows.Count < 1 Then
                        MsgBox("There are no locations for this deparment.")
                        Dim jmb As New DataGridViewComboBoxColumn()
                        jmb.HeaderText = "Location Name"
                        jmb.Name = "LocationName"
                        jmb.Items.Clear()
                        dgadditem.Columns.Insert(4, jmb)
                    Else
                        Dim jmb As New DataGridViewComboBoxColumn()
                        jmb.HeaderText = "Location Name"
                        jmb.Name = "LocationName"
                        jmb.MaxDropDownItems = ds.Tables(0).Rows.Count
                        For Each dr As DataRow In ds.Tables(0).Rows
                            jmb.Items.Add(dr.Item(0))
                        Next
                        dgadditem.Columns.Insert(4, jmb)
                    End If
                    '' add account name drop box
                    Dim accountnamecmb = New DataGridViewComboBoxColumn()
                    accountnamecmb.HeaderText = "Account Name"
                    accountnamecmb.Name = "AccountName"
                    accountnamecmb.MaxDropDownItems = 4
                    accountnamecmb.Items.Add("Food Purchases")
                    accountnamecmb.Items.Add("Paper & Chemical")
                    accountnamecmb.Items.Add("Equipment & Furniture")
                    accountnamecmb.Items.Add("Smallwares")
                    dgadditem.Columns.Insert(3, accountnamecmb)
                    ''setup datagrid
                    dgadditem.ReadOnly = False
                    dgadditem.Columns(0).ReadOnly = True
                    dgadditem.Columns(1).ReadOnly = True
                    dgadditem.Columns(2).ReadOnly = True
                    dgadditem.Columns(6).ReadOnly = True
                    dgadditem.AllowUserToAddRows = False
                    ''fill the datagrid with our data
                    daeditdeleteorderguide = New OleDb.OleDbDataAdapter("SELECT ourorderguide.ID, ourorderguide.Company, thierorderguide.ItemDescription, ourorderguide.AccountName, ourorderguide.Location, ourorderguide.NotInDepartment, thierorderguide.LastUpdated FROM thierorderguide INNER JOIN ourorderguide ON thierorderguide.ItemNumber = ourorderguide.ItemNumber WHERE (((ourorderguide.DepartmentName)= '" & lbDepartmentName.SelectedItem.ToString & "'))", mainform.dbconnection)
                    daeditdeleteorderguide.Fill(dsadditems)
                    dgadditem.DataSource = dsadditems.Tables(0)
                    dvdataview = New DataView(dgadditem.DataSource)
                    dgadditem.DataSource = dvdataview
MillkindAsked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
Can you show a screen shot of your form?
0
 
CodeCruiserCommented:
You seem to be creating a combobox column and filling it. What code is executing if you step through the code? Trying adding the combobox column after populating the grid.
0
 
MillkindAuthor Commented:
All of the code executes, but it created no table.  I changed the code to what you said now it creates the table with duplicates of the comboboxes and it does not fill them with the appropriate data.

                    ''edit/delete items in a department order guide
                    ''loaddatagrid("SELECT ourorderguide.ID, ourorderguide.Company, thierorderguide.ItemDescription, ourorderguide.AccountName, ourorderguide.Location, ourorderguide.NotInDepartment, thierorderguide.LastUpdated FROM thierorderguide INNER JOIN ourorderguide ON thierorderguide.ItemNumber = ourorderguide.ItemNumber WHERE (((ourorderguide.DepartmentName)= '" & lbDepartmentName.SelectedItem.ToString & "'))")
                    dsadditems = New DataSet
                    Dim DT As New DataTable
                    With DT.Columns
                        .Add("ID")
                        .Add("Company")
                        .Add("ItemDescription")
                        ''account name will be added later
                        ''loction will be added here later
                        .Add("NotInDeparment")
                        .Add("LastUpdated")
                    End With
                    ''fill the datagrid with our data
                    daeditdeleteorderguide = New OleDb.OleDbDataAdapter("SELECT ourorderguide.ID, ourorderguide.Company, thierorderguide.ItemDescription, ourorderguide.AccountName, ourorderguide.Location, ourorderguide.NotInDepartment, thierorderguide.LastUpdated FROM thierorderguide INNER JOIN ourorderguide ON thierorderguide.ItemNumber = ourorderguide.ItemNumber WHERE (((ourorderguide.DepartmentName)= '" & lbDepartmentName.SelectedItem.ToString & "'))", mainform.dbconnection)
                    daeditdeleteorderguide.Fill(dsadditems)
                    dgadditem.DataSource = dsadditems.Tables(0)
                    dvdataview = New DataView(dgadditem.DataSource)
                    dgadditem.DataSource = dvdataview
                    'add a location drop box
                    Dim ds As New DataSet
                    Dim datest As New OleDb.OleDbDataAdapter("Select ID,departmentcode from departments where department = '" & lbDepartmentName.SelectedItem.ToString & "'", mainform.dbconnection)
                    datest.Fill(ds)
                    Dim departmentID As Integer = ds.Tables(0).Rows(0).Item(0)
                    ds = New DataSet
                    datest = New OleDb.OleDbDataAdapter("Select location from locations where departmentID = " & departmentID, mainform.dbconnection)
                    datest.Fill(ds)
                    If ds.Tables(0).Rows.Count < 1 Then
                        MsgBox("There are no locations for this deparment.")
                        Dim jmb As New DataGridViewComboBoxColumn()
                        jmb.HeaderText = "Location Name"
                        jmb.Name = "LocationName"
                        jmb.Items.Clear()
                        dgadditem.Columns.Insert(4, jmb)
                    Else
                        Dim jmb As New DataGridViewComboBoxColumn()
                        jmb.HeaderText = "Location Name"
                        jmb.Name = "LocationName"
                        jmb.MaxDropDownItems = ds.Tables(0).Rows.Count
                        For Each dr As DataRow In ds.Tables(0).Rows
                            jmb.Items.Add(dr.Item(0))
                        Next
                        dgadditem.Columns.Insert(4, jmb)
                    End If
                    '' add account name drop box
                    Dim accountnamecmb = New DataGridViewComboBoxColumn()
                    accountnamecmb.HeaderText = "Account Name"
                    accountnamecmb.Name = "AccountName"
                    accountnamecmb.MaxDropDownItems = 4
                    accountnamecmb.Items.Add("Food Purchases")
                    accountnamecmb.Items.Add("Paper & Chemical")
                    accountnamecmb.Items.Add("Equipment & Furniture")
                    accountnamecmb.Items.Add("Smallwares")
                    dgadditem.Columns.Insert(3, accountnamecmb)
                    ''setup datagrid
                    dgadditem.ReadOnly = False
                    dgadditem.Columns(0).ReadOnly = True
                    dgadditem.Columns(1).ReadOnly = True
                    dgadditem.Columns(2).ReadOnly = True
                    dgadditem.Columns(6).ReadOnly = True
                    dgadditem.AllowUserToAddRows = False
0
 
MillkindAuthor Commented:
Im going to go a different route with it.  Do an edit area dependent on the selected row. Suggestions for points on this one?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.