Link to home
Start Free TrialLog in
Avatar of Millkind
MillkindFlag for Afghanistan

asked on

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
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Millkind

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Im going to go a different route with it.  Do an edit area dependent on the selected row. Suggestions for points on this one?