Updating SQL database table using WPF datagrid

I’m new to WPF and I’m trying to have a datagrid automatically update a table in a local SQL database when a user edits it.

I feel like I’m close as I’ve gotten the insert to work, but I can’t figure out the update(or the delete, but I haven’t looked into that much yet).

  • MAMP: Adding ODBC or SQL Server support
  • Linq To Sql using AttachAll. DuplicateKeyException
  • Unpivot Data Scenario to Solve?
  • How to find which columns don't have any data (all values are NULL)?
  • SQL to move rows up or down in two-table arrangement
  • How to get a case sensitive version of a collation in SQL Server?
  • I’m using a table adapter for the grid which calls a stored procedure for the update command. My problem is that when I call the update command for it in the CurrentCellChanged event, it is not passing in the ID parameter for the stored procedure, so the update fails.

    Here is the relevant code behind for the WPF xaml.vb page:

    Dim oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("AARP_Conn").ToString)
    ' construct the dataset
    Dim dataset As New AARPDataSet
    ' use a table adapter to populate the Customers table
    Dim adapter As New AARPDataSetTableAdapters.tblRiskTiersTableAdapter
    
    Private Sub _grdRiskTiers_CurrentCellChanged(sender As Object, e As EventArgs) Handles _grdRiskTiers.CurrentCellChanged
        'this line gets error: upRiskTier expects parameter @ID, which was not supplied.
        adapter.Update(dataset.tblRiskTiers)
    End Sub
    
    
    Private Sub RiskTiersForm_Initialized(sender As Object, e As EventArgs) Handles Me.Initialized
    
            adapter.Fill(dataset.tblRiskTiers)
    
            ' use the dataset  as the DataContext for this Window
            Me.DataContext = dataset.tblRiskTiers.DefaultView
    
    End Sub
    

    Here is the datagrid markup:

    <DataGrid x:Name="_grdRiskTiers" AutoGenerateColumns="False" ItemsSource="{Binding}" HorizontalAlignment="Left" Margin="45,20,0,0" VerticalAlignment="Top" Height="199" Width="192" Grid.ColumnSpan="2">
                <DataGrid.Columns>
                    <DataGridTextColumn Binding="{Binding ID}" ></DataGridTextColumn>
                    <DataGridTextColumn Binding="{Binding LowTierCreditScore}" Header="Low Tier Credit Score"></DataGridTextColumn>
                </DataGrid.Columns>
    </DataGrid>
    

    And here is the stored procedure for updating:

    CREATE PROCEDURE [dbo].[upRiskTier]
        @ID as int,
        @NewLowTierCreditScore as int
    AS
    IF EXISTS(SELECT * FROM tblRiskTiers WHERE LowTierCreditScore = @NewLowTierCreditScore) BEGIN
        DELETE FROM tblRiskTiers WHERE ID = @ID
    END ELSE BEGIN
        UPDATE tblRiskTiers SET LowTierCreditScore = @NewLowTierCreditScore
        WHERE ID = @ID
    END
    

    How can I get the update command to pass in the ID to the stored procedure?

    UPDATE: The following updated code resolved my issue

    Private Sub RiskTiersForm_Initialized(sender As Object, e As EventArgs) Handles Me.Initialized
    
    adapter.Fill(dataset.tblRiskTiers)
    
    Dim command = New SqlCommand("[dbo].[upRiskTier]")
    command.CommandType = System.Data.CommandType.StoredProcedure
    command.Connection = oConn
    command.Parameters.Add("@ID", System.Data.SqlDbType.Int, 5, "ID")
    command.Parameters.Add("@NewLowTierCreditScore", System.Data.SqlDbType.Int, 5, "LowTierCreditScore")
    adapter.Adapter.UpdateCommand = command
    ' use the Customer table as the DataContext for this Window
    Me.DataContext = dataset.tblRiskTiers.DefaultView
    
    End Sub
    

  • wpf : how to show a data set in a grid view?
  • Trouble updating my datagrid in WPF
  • How to display data from related tables in a WPF datagrid
  • One Solution collect form web for “Updating SQL database table using WPF datagrid”

    This error ‘this line gets error: upRiskTier expects parameter @ID, which was not supplied.’ mean – your adapter doesn’t configured properly.. There aren’t relations between column and parameters in your adapter.UpdateCommand…

    In next example show how to set relations between columns in DataTable and command parameters. Check your code where you configure SqlDataAdapter.

       // Create the UpdateCommand.
        command = new SqlCommand(
            "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
            "WHERE CustomerID = @oldCustomerID", connection);
    
        // Add the parameters for the UpdateCommand.
        command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
        command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
        SqlParameter parameter = command.Parameters.Add(
            "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
        parameter.SourceVersion = DataRowVersion.Original;
    
        adapter.UpdateCommand = command;
    

    In your case – your command should be

    command = new SqlCommand("[dbo].[upRiskTier]");
    command.CommandType = CommandType.StoredProcedure;
    

    More detailed you can see in article on MSDN

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.