WPF DataGrid ComboBoxColumn绑定数据无法更新数据库求助

Hey there, let's figure out why your database isn't updating when you select a new status in the ComboBox. I've gone through your code and spotted a couple of key issues causing this behavior—let's break them down and fix them step by step:

1. Your SqlDataAdapter lacks update command support #

Right now, you're filling the DataTable with your query, but you haven't set up the adapter to generate update/insert/delete commands. Without this, even if the DataTable's rows change, those changes won't get sent back to the database.

2. The static Description column doesn't auto-refresh #

Your initial query joins the main table with the status table to pull in the Description—but this is a one-time value. When you update the Status key, the Description column in the main DataTable won't automatically update to match the new status.

3. Minor binding tweaks for reliability #

Using anonymous types for your status list can cause subtle type-matching issues, and IsSynchronizedWithCurrentItem="True" on the ComboBox isn't necessary here (it can interfere with per-row bindings).

Fixes to Implement #

First: Add Update Command Support to Your Adapter

Modify the code where you fill your main DataTable to include a SqlCommandBuilder—this automatically generates the commands your adapter needs to sync changes back to the database:

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] FROM [test_3DimensionalDB].[dbo].[tmfCNCComponent_threed] WHERE [ComponentDescription] " & component & " 'trode%' AND [CompanyID]='" & company & "' AND [Status]" & status & "ORDER BY [UpdateTime] DESC"

Dim Adpt As New SqlDataAdapter(com, con)
' Add this line to auto-generate update commands
Dim commandBuilder As New SqlCommandBuilder(Adpt) 

con.Open()
Dim ds As New DataSet()
Adpt.Fill(ds, "dbo.tmfCNCComponent_threed")
dataGrid1.ItemsSource = ds.Tables("dbo.tmfCNCComponent_threed").DefaultView
con.Close()

Then add a save mechanism (like a button click event) to push changes to the database:

Private Sub btnSaveChanges_Click(sender As Object, e As RoutedEventArgs)
    Try
        con.Open()
        ' Sync DataTable changes back to the database
        Adpt.Update(ds.Tables("dbo.tmfCNCComponent_threed"))
        MessageBox.Show("Changes saved successfully!")
    Catch ex As Exception
        MessageBox.Show($"Error saving changes: {ex.Message}")
    Finally
        con.Close()
    End Try
End Sub

Second: Fix Status Description Auto-Refresh

Instead of joining the status table in your main query, use a value converter to dynamically fetch the description based on the Status key. This ensures the description updates automatically when the status changes.

First, create the converter class:

Public Class StatusToDescriptionConverter
    Implements IValueConverter

    Private _statusTable As DataTable

    Public Sub New(statusTable As DataTable)
        _statusTable = statusTable
    End Sub

    Public Function Convert(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.Convert
        If value IsNot DBNull.Value Then
            Dim statusKey = CInt(value)
            Dim statusRow = _statusTable.Select($"CNCComponentStatusKey = {statusKey}").FirstOrDefault()
            If statusRow IsNot Nothing Then
                Return statusRow("Description").ToString()
            End If
        End If
        Return String.Empty
    End Function

    Public Function ConvertBack(value As Object, targetType As Type, parameter As Object, culture As Globalization.CultureInfo) As Object Implements IValueConverter.ConvertBack
        Throw New NotImplementedException()
    End Function
End Class

Update your status list loading code to use the DataTable directly and add the converter to resources:

con.Open()
Dim statusCVS As CollectionViewSource = FindResource("StatusItems")
Dim com2 As String = "SELECT * FROM tmfCNCComponentStatus_threed"
Dim AdptStatus As New SqlDataAdapter(com2, con)
AdptStatus.Fill(ds, "tmfCNCComponentStatus_threed")
' Use the DataTable directly for ComboBox items
statusCVS.Source = ds.Tables("tmfCNCComponentStatus_threed").DefaultView

' Create and register the converter
Dim statusConverter As New StatusToDescriptionConverter(ds.Tables("tmfCNCComponentStatus_threed"))
Me.Resources.Add("StatusToDescriptionConverter", statusConverter)
con.Close()

Third: Clean Up XAML Binding

Update your DataGrid column to use the converter for display and remove unnecessary settings:

<CollectionViewSource x:Key="StatusItems"/>
<DataGridTemplateColumn x:Name="StatusColumn" Header="Status" Width="*" IsReadOnly="False">
    <DataGridTemplateColumn.CellTemplate>
        <DataTemplate>
            <!-- Use converter to show dynamic status description -->
            <TextBlock Text="{Binding Path=Status, Converter={StaticResource StatusToDescriptionConverter}}"/>
        </DataTemplate>
    </DataGridTemplateColumn.CellTemplate>
    <DataGridTemplateColumn.CellEditingTemplate>
        <DataTemplate>
            <ComboBox x:Name="StatusCombo" 
                      SelectedValuePath="CNCComponentStatusKey" 
                      DisplayMemberPath="Description" 
                      SelectedValue="{Binding Status, UpdateSourceTrigger=PropertyChanged}" 
                      ItemsSource="{Binding Source={StaticResource StatusItems}}" 
                      IsEditable="True"/>
        </DataTemplate>
    </DataGridTemplateColumn.CellEditingTemplate>
</DataGridTemplateColumn>

Important Notes #

• SQL Injection Risk: Your current query uses string concatenation for component, company, and status—this is a major security vulnerability. Switch to parameterized queries immediately to protect your database.
• Connection Best Practices: Use Using blocks for database connections and adapters to ensure proper resource disposal.

内容的提问来源于stack exchange，提问作者knutter539