CODEWITHSUNDEEP
sql-serverwpfvb.netdatagrid
rgorr
rgorr

Reputation: 380

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).

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

Upvotes: 2

Views: 2693

Answers (1)

Anton
Anton

Reputation: 748

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

Upvotes: 4

Related Questions