WPF Datagrid Showing Second DB Table As ComboBox Options

Question

I have added a second table to my database which contains the different options i want to see in a combobox for each row of my datagrid in a new column. Each option has a key associated with it that matches up to a column in my main table, which is a value from 0 to 8. I have 2 problems, one of which i have resolved:

When the grid loads, i want the combobox column to show the [Description] that corresponds to each row's [Status] key. This is working.
I want the user to select the [Description] cell and have a dropdown open all of the possible [Description] options so that he can change the item's status.

For example, if a component goes on hold, the user needs to select that component in the grid and select "On Hold" in the dropdown of the status column.

The status correctly shows, but the dropdown doesn't populate with the 8 options from the CNCComponentStatus table. I am willing to bet i am not filling the dataset with the second table correctly in the SqlDataAdapter. That or i am not correctly using SelectedItem/ItemSource/SelectedValuePath correctly.

Here is a screenshot of the grid Here is my data structure

These are the Status options:

Here's the xaml for the datagrid (only the pertinent parts):

And here's the method that grabs the data:

 Private Sub SelectQuery(ByVal company As String, ByVal status As String, ByVal component As String)

    Dim com As String = "SELECT tmfCNCComponent_threed.[CNCComponentKey]
    ,tmfCNCComponent_threed.[CompanyID]
    ,tmfCNCComponent_threed.[JobNumber]
    ,tmfCNCComponent_threed.[LogNumber]
    ,tmfCNCComponent_threed.[Customer]
    ,tmfCNCComponent_threed.[DueDate]
    ,tmfCNCComponent_threed.[JobLeader]
    ,tmfCNCComponent_threed.[CADProgrammer]
    ,tmfCNCComponent_threed.[Salesperson]
    ,tmfCNCComponent_threed.[CNCProgrammer]
    ,tmfCNCComponent_threed.[ComponentDescription]
    ,tmfCNCComponent_threed.[ComponentFilePath]
    ,tmfCNCComponent_threed.[Material]
    ,tmfCNCComponent_threed.[ComponentSizeX]
    ,tmfCNCComponent_threed.[ComponentSizeY]
    ,tmfCNCComponent_threed.[ComponentSizeZ]
    ,tmfCNCComponent_threed.[QuantityShown]
    ,tmfCNCComponent_threed.[QuantityMirror]
    ,tmfCNCComponent_threed.[UpdateTime]
    ,tmfCNCComponent_threed.[Status]
    ,tmfCNCComponent_threed.[ProgStarted]
    ,tmfCNCComponentStatus_threed.[Description]
    FROM [test_3DimensionalDB].[dbo].[tmfCNCComponent_threed]
    INNER JOIN tmfCNCComponentStatus_threed
    ON tmfCNCComponent_threed.Status = tmfCNCComponentStatus_threed.CNCComponentStatusKey 
    WHERE [ComponentDescription] " & component & " 'trode%' AND [CompanyID]='" & company & "' AND [Status]" & status & "ORDER BY [UpdateTime] DESC"

    Dim Adpt As New SqlDataAdapter(com, con)
    con.Open()
    Dim ds As New DataSet()
    Adpt.Fill(ds, "dbo.tmfCNCComponent_threed")

    dataGrid1.ItemsSource = ds.Tables("dbo.tmfCNCComponent_threed").DefaultView

    con.Close()

    RowCount()
    searchBox.Clear()

End Sub

Thanks for your time, sorry for the extreme detail.

EDIT: Here is how i would attempt to populate the combobox since i am not binding properly. Although i don't quite understand how to show only one column instead of the DefaultView. Also, "StatusCombo" is inaccessible due to it's protection level?

    Dim com2 As String = "SELECT * FROM tmfCNCComponentStatus_threed"
    Dim AdptStatus As New SqlDataAdapter(com2, con)
    AdptStatus.Fill(ds, "dbo.tmfCNCComponentStatus_threed")
    StatusCombo.ItemsSource = ds.Tables("dbo.tmfCNCComponentStatus_threed").DefaultView

15ee8f99-57ff-4f92-890c-b56153 · Accepted Answer

Here's what that combobox should end up looking like, I think. The x:Name property isn't needed. I doubt that IsEditable is needed either.

SelectedValuePath and DisplayMemberPath are strings. They're the names of columns in the data rows that populate the ComboBox (if you populate it with regular C# class instances, they would instead be the names of properties of the item class). You were trying to bind those properties to properties of a viewmodel you don't have, but even if you did have one, it wouldn't have those properties. But the concept of what a binding is and what it does can be weirdly slippery at first.

I almost told you to read the documentation on SelectedValuePath, but the documentation has been revised and is now essentially meaningless. DisplayMemberPath is better.

Second: You should have a viewmodel. The lack of a viewmodel makes everything more difficult. But you don't have a viewmodel.

What you need to do is get what you want from the database, and put it in some kind of collection somewhere that the combobox can be bound to.

Lacking a viewmodel to act as a natural home for that collection, we'll do that by creating a CollectionViewSource in your window's Resources.

And we'll use that to populate the combobox like so:

But our CollectionViewSource is empty. So we'll have to populate it somehow. We'll do that whenever you do the database query stuff.

Dim statusCVS As CollectionViewSource = FindResource("StatusItems")

Dim com2 As String = "SELECT * FROM tmfCNCComponentStatus_threed"
Dim AdptStatus As New SqlDataAdapter(com2, con)
AdptStatus.Fill(ds, "dbo.tmfCNCComponentStatus_threed")

Dim statusRows = ds.Tables("dbo.tmfCNCComponentStatus_threed").Rows
Dim statuses As New List(Of Object)

For Each row As DataRow In statusRows
    statuses.Add(New With {
            .Status = CInt(row("CNCComponentStatusKey")),
            .Description = CStr(row("Description"))
        })
Next

statusCVS.Source = statuses

This would be nicer than the loop, if it works:

Dim statuses = From row In ds.Tables("dbo.tmfCNCComponentStatus_threed")
               .Rows.Cast(Of DataRow)
               Select New With {
                    .Status = CInt(row("CNCComponentStatusKey")),
                    .Description = CStr(row("Description"))
                }

statusCVS.Source = statuses

OK, now we have this part:

    SelectedValue="{Binding Status}"

I'm guessing that test_3DimensionalDB (is your DataGrid actually populating?) must have some column that's a foreign key to the Status id value in the combo, and I'm guessing that it might be called Status.

So what we want to happen is: Say you have two items:

item 0:
    Status = 1
    Description = Dog
item 1:
    Status = 2
    Description = Cat

DisplayMemberPath="Description" means that item 0 will be displayed as "Dog", the value of its Description property. Item 1 will be displayed as "Cat" for the same reason.

SelectedValuePath="Status" means that when the binding sets SelectedValue to 2, the combobox will look through its items collection to find the item that has a Status property equal to 2, and select that item. If we set SelectedValuePath="Fred", it would look for an item that had a property named Fred equal to 2.

Likewise, if the user changes the selection himself, it goes the other way: Let's say the user selects item 0, so the ComboBox consults its own SelectedValuePath, sees "Status", and gets the Status property value (if any) of the selected item. For item 0, that's 1. Then the combobox would assign that value of 1 to its own SelectedValue property.

The Binding on SelectedValue will then receive a notification that SelectedValue has changed, and it will get the new value and update the database row column it's bound to (also named "Status", in this case).

The Binding is an object that sticks around and does stuff. It's not a fancy word for "assignment".

SelectedValue="{Binding Status}"

WPF Datagrid Showing Second DB Table As ComboBox Options

Answers (1)

Related Questions