none
Deleting and Updating records within a Database in Vb.net RRS feed

  • Question

  • Hello, again:

    Now that I have known how to add and update data as I have discussed in the previous question, I would now want to know how to delete and update data.

    Here is the source code snippet below:

    Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
    
    
    
            Dim DELAdoRecordAdapter As New OleDb.OleDbDataAdapter("DELETE * FROM ListOfNames WHERE NameID=@NameID", mcnAdoNetConnect)
            mcbAdoDelCmdBuild = New OleDb.OleDbCommandBuilder(mdaListOfNames)
            ListOfNamesBindingSource.EndEdit()
    
    
        End Sub
    

    When I ran the program, it temporarily deletes it, but it will fail to update thus given this error below:

    System.InvalidOperationException
      HResult=0x80131509
      Message=Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.
      Source=NamesDBProject01
      StackTrace:
       at NamesDBProject01.NamesDBDataSetTableAdapters.TableAdapterManager.UpdateAll(NamesDBDataSet dataSet) in C:\Users\johnd\source\repos\NamesDBProject01\NamesDBProject01\NamesDBDataSet.Designer.vb:line 1220
       at NamesDBProject01.Form1.ListOfNamesBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Users\johnd\source\repos\NamesDBProject01\NamesDBProject01\Form1.vb:line 15
       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ToolStrip.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at NamesDBProject01.My.MyApplication.Main(String[] Args) in :line 81

    Can anyone help me out on how to solve this problem?

    Regards,

    JohnDBCTX


    jp

    Friday, April 20, 2018 12:41 AM

Answers

  •   Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
            'Would a select query be before these lines of code below?
            Dim objCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
            objCommand.Connection = mcnADONetConnect
            objCommand.CommandText = "DELETE FROM ListOfNames WHERE NameID=@NameID AND LastName=@LastName AND FirstName=@FirstName"
            objCommand.CommandType = CommandType.Text
    
            With objCommand.Parameters
                .Add(New OleDb.OleDbParameter())
                .Item(0).Direction = ParameterDirection.Input
                .Item(0).DbType = DbType.Int32
                .Item(0).Size = 11
                .Item(0).Value = NameIDTextBox.Text
    
                .Add(New OleDb.OleDbParameter())
                .Item(1).Direction = ParameterDirection.Input
                .Item(1).DbType = DbType.String
                .Item(1).Size = 11
                .Item(1).Value = LastNameTextBox.Text
    
    
                .Add(New OleDb.OleDbParameter())
                .Item(2).Direction = ParameterDirection.Input
                .Item(2).DbType = DbType.String
                .Item(2).Size = 11
                .Item(2).Value = FirstNameTextBox.Text
            End With
    
    
            objCommand.ExecuteNonQuery()
            mcnADONetConnect.Close()
            objCommand = Nothing
    
        End Sub
    

    Finally, after all those hours passed, I have found a solution to my own problem...and it worked.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Friday, April 20, 2018 8:08 AM
    Friday, April 20, 2018 8:08 AM

All replies

  • See the following with code example. Inserts and Updates follow the same pattern.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 20, 2018 1:21 AM
    Moderator
  • Just to review from that link:

        Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
            'Would a select query be before these lines of code below?

            CNAdoNetAdapter = New OleDb.OleDbDataAdapter("DELETE FROM ListOfNames WHERE NameID=@NameID", mcnADONetConnect)
            mcbAdoCmdBuild = New OleDb.OleDbCommandBuilder(CNAdoNetAdapter)

        End Sub

    Regards,

    JohnDBCTX


    jp


    In other words perform a select query first and then perform the delete query?
    • Edited by JohnDBCTX Friday, April 20, 2018 7:02 AM clarification
    Friday, April 20, 2018 7:01 AM
  •   Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
            'Would a select query be before these lines of code below?
            Dim objCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
            objCommand.Connection = mcnADONetConnect
            objCommand.CommandText = "DELETE FROM ListOfNames WHERE NameID=@NameID AND LastName=@LastName AND FirstName=@FirstName"
            objCommand.CommandType = CommandType.Text
    
            With objCommand.Parameters
                .Add(New OleDb.OleDbParameter())
                .Item(0).Direction = ParameterDirection.Input
                .Item(0).DbType = DbType.Int32
                .Item(0).Size = 11
                .Item(0).Value = NameIDTextBox.Text
    
                .Add(New OleDb.OleDbParameter())
                .Item(1).Direction = ParameterDirection.Input
                .Item(1).DbType = DbType.String
                .Item(1).Size = 11
                .Item(1).Value = LastNameTextBox.Text
    
    
                .Add(New OleDb.OleDbParameter())
                .Item(2).Direction = ParameterDirection.Input
                .Item(2).DbType = DbType.String
                .Item(2).Size = 11
                .Item(2).Value = FirstNameTextBox.Text
            End With
    
    
            objCommand.ExecuteNonQuery()
            mcnADONetConnect.Close()
            objCommand = Nothing
    
        End Sub
    

    Finally, after all those hours passed, I have found a solution to my own problem...and it worked.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Friday, April 20, 2018 8:08 AM
    Friday, April 20, 2018 8:08 AM
  • Interesting, I thought you were going with an Adapter path. If you are going in this direction then consider the following where the idea is to place all CRUD operations in a class rather than in a form (placing data operations in a form is okay but it's better to separate data ops into classes) e.g. and take note how I wrote the delete method.

    Public Class Sample5
        Private Builder As New OleDbConnectionStringBuilder With
            {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Public Function Delete(pNameIdentifier As Integer, pFirstName As String, pLastName As String) As Boolean
            Dim success As Boolean = True
            Dim affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM ListOfNames " &
                                          "WHERE NameID=@NameID AND LastName=@LastName AND FirstName=@FirstName"
    
                        cmd.Parameters.AddWithValue("@NameID", pNameIdentifier)
                        cmd.Parameters.AddWithValue("@LastName", pFirstName)
                        cmd.Parameters.AddWithValue("@FirstName", pLastName)
    
                        cn.Open()
    
                        affected = cmd.ExecuteNonQuery()
                        If affected = 1 Then
                            success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                success = False
            End Try
    
            Return success
    
        End Function
        Public Function Read() As DataTable
    
        End Function
    
    
        Public Function Add(pFirstName As String, pLastName As String) As Boolean
            ' TODO
        End Function
        Public Function Update(pNameIdentifier As Integer, pFirstName As String, pLastName As String) As Boolean
            ' TODO
        End Function
    
    End Class
    
    If interested in seeing more check out my MSDN code sample.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 20, 2018 8:41 AM
    Moderator
  • I know there is no remedy for it given the current forum rules. 

    However, I see more and more persons who ask for a solution for a problem and than give themselves code and mark that as answer. 

    It this thread was asked "I would now want to know how to delete and update data."

    And then the OP comes with code that he made himself without even looking at the replies and gives code which is in this case only about deleting rows in a kind of 2002 style. 

    For the moderators, it does not give points, but that is less important than the misleading of Microsoft customers who are searching for a likewise question. In my perception it should not be allowed.


    Success
    Cor

    Friday, April 20, 2018 8:43 AM
  • I have browsed over the uploaded customized apps, and I am interested in creating samples before uploading to the developer network site.  

    How should I perform that?

    I would like to know more about how that works.

    Can anyone provide me some links?

    Regards,

    JohnDBCTX


    jp

    I have found the link in regards to uploading samples within the developer network page.

    https://code.msdn.microsoft.com/site/search?f%5B0%5D.Type=ProgrammingLanguage&f%5B0%5D.Value=VB&f%5B0%5D.Text=VB.NET&f%5B1%5D.Type=Platform&f%5B1%5D.Value=Data&f%5B1%5D.Text=Data

    • Edited by JohnDBCTX Friday, April 20, 2018 9:15 AM Link to samples upload page
    Friday, April 20, 2018 9:10 AM
  • I have browsed over the uploaded customized apps, and I am interested in creating samples before uploading to the developer network site.  

    How should I perform that?

    I would like to know more about how that works.

    Can anyone provide me some links?

    Regards,

    JohnDBCTX


    jp

    I have found the link in regards to uploading samples within the developer network page.

    https://code.msdn.microsoft.com/site/search?f%5B0%5D.Type=ProgrammingLanguage&f%5B0%5D.Value=VB&f%5B0%5D.Text=VB.NET&f%5B1%5D.Type=Platform&f%5B1%5D.Value=Data&f%5B1%5D.Text=Data

    If you are interested in writing code samples.

    • Research the current samples, don't write one that has already been written well either in MSDN or TechNet code sites.
    • Study well written code samples to best write your own
    • Become an expert on the subject matter, be able to answer any questions after posting. Make sure to check the box to allow questions when writing for MSDN code sample site.
    • When dealing with data, never write data operations in forms, instead all data operations should be written in classes.
    • When applicable talk about doing things the wrong and right way.
    • Write unit test for you code samples.

    I have 145 code samples on MSDN were each and everyone have been completely tested before uploading to ensure the best possible code sample for those in need of a specific topic.

    Sometimes a topic is short while other times very long. For instance, the following (I wrote this on TechNet) is long and not complete thus there will be follow ups e.g. Part 2, Part 3.

    Another example, this one on MSDN for working with SQL-Server operations.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, April 20, 2018 10:01 AM
    Moderator