asked on

Getting error "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows"

Hi experts, pleease help
My datasource is an mdf file on my machine
I just have a simple form with textboxes and datagridview and buttons to add, delete, edit and save records

Im able to add and save with no problem
When I click on delete, the row gets deleted on the form but when I then click save, I get the error
"Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

I have primary key setup and tableadapter is configured, advanced options are all checked (although 'refresh' automatically unchecks itself for some reason), fill, return and create dbdirectmethods are checked

One thing I cant find is the acceptchangesduringfill property. I dont know if I even need to tamper with it.
Can someone please help

A screenshot of my tableadapter props is included

Thanks!

Public Class ProjectLogForm
 
    Private WithEvents aBindingSource As BindingSource
    Private aDataSet As ProjLogDatabaseDataSet
    Private aProjLogDataTier As ProjectLogDataTier
    Private addingBoolean As Boolean = False
    Private editingBoolean As Boolean = False
    'Private previousSelectedIndex As Integer
 
    Private Sub ProjectLogForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Try
 
            'instantiate data tier
            aProjLogDataTier = New ProjectLogDataTier
            aDataSet = aProjLogDataTier.getProjLogData
 
            'declare binding source
            aBindingSource = New BindingSource
            With aBindingSource
                .DataSource = aDataSet
                .DataMember = "ProjLogTable"
                .Sort = "ProjID"
 
                'get correct count of rows in the dataset
                .MoveFirst()
                .MoveLast()
            End With
 
            'bind controls
            With Me
 
                'controls are locked on initial form load
                .lockControls(True)
                .StartDateDateTimePicker.Enabled = False
                .EndDateDateTimePicker.Enabled = False
 
                'projid combobox
                With .ProjIDComboBox
                    .DisplayMember = "ProjID"
                    .DataSource = aBindingSource
                    .DataBindings.Add("text", aBindingSource, "ProjID", False, DataSourceUpdateMode.Never, Nothing)
                End With
 
                'projname textbox
                .ProjNameTextBox.DataBindings.Add("text", aBindingSource, "ProjName")
 
                'gisp textbox
                .GISPTextBox.DataBindings.Add("text", aBindingSource, "GISP")
 
                'bind data grid
                .ProjLogTableDataGridView.DataSource = aBindingSource
 
            End With
 
        Catch ex As Exception
 
            Throw ex
 
        End Try
 
    End Sub
 
    Private Sub addButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addButton.Click
 
        With Me
 
            'specify that its an adding operation
            .addingBoolean = True
 
            'controls are unlocked
            .lockControls(False)
            .StartDateDateTimePicker.Enabled = True
            .EndDateDateTimePicker.Enabled = True
 
            'combobox is set for data input
            .setProjIDComboboxEditing()
 
            'disable navigation, delete and edit buttons
            .lockNavigationButtons(False)
            .saveButton.Enabled = True
            .editButton.Enabled = False
 
            With .aBindingSource
 
                'end any edits
                .EndEdit()
 
                'add record
                .AddNew()
 
            End With
 
            .ProjIDComboBox.Focus()
 
            'display adding in the status bar
            .ToolStripStatusLabel1.Text = "Adding..."
 
            'If .ProjIDComboBox.SelectedIndex <> -1 Then
            '    ' Save the index of the new record for later navigation.
            '    .previousSelectedIndex = .ProjIDComboBox.Items.Count - 1
            'Else
            '    .previousSelectedIndex = 0
            'End If
 
        End With
 
    End Sub
 
    Private Sub saveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveButton.Click
 
        With Me
 
            .addingBoolean = False
            .editingBoolean = False
 
            'lock controls
            .lockControls(True)
            .StartDateDateTimePicker.Enabled = False
            .EndDateDateTimePicker.Enabled = False
 
            'enable navigation and other buttons
            .lockNavigationButtons(True)
            .saveButton.Enabled = False
            .editButton.Enabled = True
 
            'combobox is set for data display
            .setProjIDComboboxEditing()
 
            Try
 
                .Validate()
 
                'end edits
                .aBindingSource.EndEdit()
 
                'update datasource
                .aProjLogDataTier.UpdateDataSource(.aDataSet)
 
                'send changes to dataset
                .aDataSet.AcceptChanges()
 
                'change status text to record(s) saved
                .ToolStripStatusLabel1.Text = "Record(s) Saved"
 
            Catch ex As Exception
 
                Throw ex
 
            End Try
 
        End With
 
    End Sub
 
    Private Sub editButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editButton.Click
 
        With Me
 
            'unlock textboxes and controls
            .lockControls(False)
            .StartDateDateTimePicker.Enabled = True
            .EndDateDateTimePicker.Enabled = True
 
            .editingBoolean = True
 
            'set combobox for editing
            .setProjIDComboboxEditing()
 
            '.previousSelectedIndex = .ProjIDComboBox.SelectedIndex
 
 
 
        End With
 
    End Sub
 
    Private Sub deleteButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles deleteButton.Click
 
        Dim deleteDialogResult As DialogResult
 
        Try
 
            deleteDialogResult = MessageBox.Show("Delete Record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
 
            With Me
 
                If deleteDialogResult = Windows.Forms.DialogResult.Yes Then
 
                    With .aBindingSource
 
                        'end edit
                        .EndEdit()
 
                        'remove record
                        .RemoveCurrent()
 
                    End With
 
                    'update toolstripstatus
                    .ToolStripStatusLabel1.Text = "Record deleted"
 
                Else
 
                    .ToolStripStatusLabel1.Text = String.Empty
 
                End If
 
                'enable navigation
                .lockNavigationButtons(True)
 
                .saveButton.Enabled = True
 
            End With
 
        Catch ex As Exception
 
            Dim msgString As String
            msgString = "Unable to complete Delete" & ex.Message
            MessageBox.Show(msgString, "Delete", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
 
        End Try
 
    End Sub
 
    Private Sub lockControls(ByVal lockControlsBoolean As Boolean)
 
        With Me
            .ProjNameTextBox.ReadOnly = lockControlsBoolean
            .ProjDescTextBox.ReadOnly = lockControlsBoolean
            .RequestorTextBox.ReadOnly = lockControlsBoolean
            .Dept_DivTextBox.ReadOnly = lockControlsBoolean
            .HoursTextBox.ReadOnly = lockControlsBoolean
            .GISPTextBox.ReadOnly = lockControlsBoolean
        End With
 
    End Sub
 
    Private Sub lockNavigationButtons(ByVal lockNav As Boolean)
 
        With Me
            .moveFirstButton.Enabled = lockNav
            .moveLastButton.Enabled = lockNav
            .moveNextButton.Enabled = lockNav
            .movePreviousButton.Enabled = lockNav
        End With
    End Sub
 
    Private Sub setProjIDComboboxEditing()
 
        With Me.ProjIDComboBox
 
            If addingBoolean Or editingBoolean Then
 
                'change combobox style to accept text input
                .DropDownStyle = ComboBoxStyle.Simple
 
                'change datasourceupdate mode
                .DataBindings!text.DataSourceUpdateMode = DataSourceUpdateMode.OnValidation
 
            Else
 
                .DropDownStyle = ComboBoxStyle.DropDownList
                .DataBindings!text.DataSourceUpdateMode = DataSourceUpdateMode.Never
 
            End If
 
        End With
 
    End Sub
 
End Class

Open in new window

tableadapterprops.jpg

Nasir Razzaq

As can be seen in the properties window in screenshot, the DeleteCommand is set to none. The Update command is also set to none. You need to either manually add these commands or regenerate these commands using the tableadapter.

debugged

ASKER

Hey cruiser, thanks for responding.
No matter how I try, the tableadapter does not regenerate these commands automatically for me. As I mentioned, I configured tableadapter in every possible way, all options are checked. Only the refresh option unchecks itself automatically for some reason.
If I have to do it manually, I dont know much SQL, so could you help me with the DELETE and UPDATE commands?
Thanks

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

debugged

ASKER

Ok, please see the screenshot for what I have for SELECT
ProjID is my primary key and I have it set in the designer

tableadapterSELECT.jpg

Nasir Razzaq

The commands should be generated. Try using the * instead of the fieldlist. Its not a stored procedure right?

debugged

ASKER

No its not a stored procedure and the * didnt work either. Why does my 'refresh ' advanced option automatically get unchecked every time?
Do you think theres something to do with the way I created the data table?

Nasir Razzaq

Its not normal behaviour and there could be any reason. The only option left now is to start with a fresh project and try creating the adapter from scratch to see if this bahaviour is limited to the current project.

debugged

ASKER

Hey Cruiser,
All this time I was setting the primary key for the table in the designer. Your solution only worked if I set the primary key both in the designer as well as the table definition in the server explorer. But thanks for your help

Nasir Razzaq

When i said you need primary key i meant the database table not the datatable. When there is primary key in database the datatable automatically creates it.

Getting error &quot;Update requires a valid DeleteCommand when passed DataRow collection with deleted rows&quot;

Getting error "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows"