none
How to delete Record in database application vb 2015 RRS feed

  • Question

  • While deleting Record I encounter following error:

    An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

    Additional information: Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.

    MY COMPLETE CODE IS:

    Public Class Form1
        Private MyDatAdp As New SqlDataAdapter
        Private MyCmdBld As New SqlCommandBuilder
        Private MyDataTbl As New DataTable
        Private MyCn As New SqlConnection
        Private MyRowPosition As Integer = 0
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            MyCn.ConnectionString = "Data Source=DESKTOP-CVG069I;Initial Catalog=test;Integrated Security=True"
            MyCn.Open()
            MyDatAdp = New SqlDataAdapter("Select* from Contacts", MyCn)
            MyCmdBld = New SqlCommandBuilder(MyDatAdp)
            MyDatAdp.Fill(MyDataTbl)
            Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
            Dim strName As String
            Dim strState As String
            strName = MyDataRow("ContactName")
            strState = MyDataRow("State")
            TxtName.Text = strName.ToString
            TxtState.Text = strState.ToString
            Me.showRecords()
        End Sub

        Private Sub showRecords()
            If MyDataTbl.Rows.Count = 0 Then
                TxtName.Text = ""
                TxtState.Text = ""
                Exit Sub
            End If
            TxtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString
            TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString
        End Sub

        Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
            If MyRowPosition < MyDataTbl.Rows.Count - 1 Then
                MyRowPosition = MyRowPosition + 1
                Me.showRecords()
            End If
        End Sub

        Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
            If MyRowPosition > 0 Then
                MyRowPosition = MyRowPosition - 1
                Me.showRecords()
            End If

        End Sub

        Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
            MyRowPosition = 0
            Me.showRecords()

        End Sub

        Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
            If MyDataTbl.Rows.Count > 0 Then
                MyRowPosition = MyDataTbl.Rows.Count - 1
                Me.showRecords()
            End If
        End Sub
        Private Sub BtnAdd_Click(sender As Object, e As EventArgs) Handles BtnAdd.Click
            Dim MyNewRow As DataRow = MyDataTbl.NewRow()
            MyDataTbl.Rows.Add(MyNewRow)
            MyRowPosition = MyDataTbl.Rows.Count - 1

            Me.showRecords()
        End Sub
        Private Sub BtnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSave.Click

            If MyDataTbl.Rows.Count <> 0 Then
                MyDataTbl.Rows(MyRowPosition)("ContactName") = TxtName.Text
                MyDataTbl.Rows(MyRowPosition)("state") = TxtState.Text
                MyDatAdp.Update(MyDataTbl)
            End If
            MessageBox.Show("Records", "Record Saved")
        End Sub
        Private Sub BtnDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnDelete.Click
            If MyDataTbl.Rows.Count <> 0 Then
                MyDataTbl.Rows(MyRowPosition).Delete()
                MyRowPosition = 0
                MyDatAdp.Update(MyDataTbl)..................................................SHOW ERROR HERE.
                Me.showRecords()
            End If
        End Sub

        Private Sub BtnExit_Click(sender As Object, e As EventArgs) Handles BtnExit.Click
            End
        End Sub
    End Class

    Monday, January 29, 2018 4:37 PM

Answers

  • If your SELECT statement does not contain a primary key then the DELETE command will throw that exception. Best way to solve this is to have a primary key and to return the primary key as part of the SELECT statement for the Adapter.

    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

    • Proposed as answer by Cor Ligthert Monday, January 29, 2018 6:30 PM
    • Marked as answer by sheelnath Wednesday, January 31, 2018 3:44 PM
    Monday, January 29, 2018 5:20 PM
    Moderator
  • Hi sheelnath,

    SqlCommandBuilder used in conjunction with the SqlDataAdapter, you can easily go to the database to be updated. As long as the specified Select statement can automatically generate Insert, update, delete statement, but be careful. The columns returned in the Select statement include the primary key column, otherwise the Update, and Delete statements will not be generated. The corresponding operation will not be executed

    Based on your provided codes, you need to add the "Adapter.UpdateCommand = DataCommandBuilder.GetUpdateCommand();" before you update the Datatable. In this case, the dataadapter can know use what sql expressions to update the datatable.

    Private MyDatAdp As SqlDataAdapter
        Private MyCmdBld As SqlCommandBuilder
        Private MyDataTbl As New DataTable
        Private MyCn As New SqlConnection
        Private MyRowPosition As Integer = 0
        Private Sub FrmDataTable_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            MyCn.ConnectionString = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testdb;Integrated Security=True"
            MyCn.Open()
            MyDatAdp = New SqlDataAdapter("Select* from Test2", MyCn)
            MyCmdBld = New SqlCommandBuilder(MyDatAdp)
            MyDatAdp.Fill(MyDataTbl)
            Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
            Dim strName As String
            Dim strState As String
            strName = MyDataRow("Column1")
            strState = MyDataRow("Column2")
            TxtName.Text = strName.ToString
            TxtState.Text = strState.ToString
            Me.showRecords()
        End Sub
        Private Sub showRecords()
            If MyDataTbl.Rows.Count = 0 Then
                TxtName.Text = ""
                TxtState.Text = ""
                Exit Sub
            End If
            TxtName.Text = MyDataTbl.Rows(MyRowPosition)("Column1").ToString
            TxtState.Text = MyDataTbl.Rows(MyRowPosition)("Column2").ToString
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If MyRowPosition < MyDataTbl.Rows.Count - 1 Then
                MyRowPosition = MyRowPosition + 1
                Me.showRecords()
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            If MyRowPosition > 0 Then
                MyRowPosition = MyRowPosition - 1
                Me.showRecords()
            End If
        End Sub
    
        Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
            Dim MyNewRow As DataRow = MyDataTbl.NewRow()
            MyDataTbl.Rows.Add(MyNewRow)
            MyRowPosition = MyDataTbl.Rows.Count - 1
    
            Me.showRecords()
    
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            MyRowPosition = 0
            Me.showRecords()
        End Sub
    
        Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            If MyDataTbl.Rows.Count > 0 Then
                MyRowPosition = MyDataTbl.Rows.Count - 1
                Me.showRecords()
            End If
        End Sub
    
        Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
            If MyDataTbl.Rows.Count <> 0 Then
                MyDataTbl.Rows(MyRowPosition)("Column1") = TxtName.Text
                MyDataTbl.Rows(MyRowPosition)("Column2") = TxtState.Text
                MyDatAdp.Update(MyDataTbl)
            End If
            MessageBox.Show("Records", "Record Saved")
        End Sub
    
        Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
            If MyDataTbl.Rows.Count <> 0 Then
                MyCmdBld = New SqlCommandBuilder(MyDatAdp)
                MyDataTbl.Rows(MyRowPosition).Delete()
                MyRowPosition = 0
                MyDatAdp.Update(MyDataTbl)
                Me.showRecords()
            End If
        End Sub

    Here is Test table, The Id is the primary key:

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, January 30, 2018 2:53 AM
    Moderator

All replies

  • If your SELECT statement does not contain a primary key then the DELETE command will throw that exception. Best way to solve this is to have a primary key and to return the primary key as part of the SELECT statement for the Adapter.

    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

    • Proposed as answer by Cor Ligthert Monday, January 29, 2018 6:30 PM
    • Marked as answer by sheelnath Wednesday, January 31, 2018 3:44 PM
    Monday, January 29, 2018 5:20 PM
    Moderator
  • Hi sheelnath,

    SqlCommandBuilder used in conjunction with the SqlDataAdapter, you can easily go to the database to be updated. As long as the specified Select statement can automatically generate Insert, update, delete statement, but be careful. The columns returned in the Select statement include the primary key column, otherwise the Update, and Delete statements will not be generated. The corresponding operation will not be executed

    Based on your provided codes, you need to add the "Adapter.UpdateCommand = DataCommandBuilder.GetUpdateCommand();" before you update the Datatable. In this case, the dataadapter can know use what sql expressions to update the datatable.

    Private MyDatAdp As SqlDataAdapter
        Private MyCmdBld As SqlCommandBuilder
        Private MyDataTbl As New DataTable
        Private MyCn As New SqlConnection
        Private MyRowPosition As Integer = 0
        Private Sub FrmDataTable_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            MyCn.ConnectionString = "Data Source=(LOCALDB)\MSSQLLOCALDB;Initial Catalog=Testdb;Integrated Security=True"
            MyCn.Open()
            MyDatAdp = New SqlDataAdapter("Select* from Test2", MyCn)
            MyCmdBld = New SqlCommandBuilder(MyDatAdp)
            MyDatAdp.Fill(MyDataTbl)
            Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
            Dim strName As String
            Dim strState As String
            strName = MyDataRow("Column1")
            strState = MyDataRow("Column2")
            TxtName.Text = strName.ToString
            TxtState.Text = strState.ToString
            Me.showRecords()
        End Sub
        Private Sub showRecords()
            If MyDataTbl.Rows.Count = 0 Then
                TxtName.Text = ""
                TxtState.Text = ""
                Exit Sub
            End If
            TxtName.Text = MyDataTbl.Rows(MyRowPosition)("Column1").ToString
            TxtState.Text = MyDataTbl.Rows(MyRowPosition)("Column2").ToString
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If MyRowPosition < MyDataTbl.Rows.Count - 1 Then
                MyRowPosition = MyRowPosition + 1
                Me.showRecords()
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            If MyRowPosition > 0 Then
                MyRowPosition = MyRowPosition - 1
                Me.showRecords()
            End If
        End Sub
    
        Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
            Dim MyNewRow As DataRow = MyDataTbl.NewRow()
            MyDataTbl.Rows.Add(MyNewRow)
            MyRowPosition = MyDataTbl.Rows.Count - 1
    
            Me.showRecords()
    
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            MyRowPosition = 0
            Me.showRecords()
        End Sub
    
        Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            If MyDataTbl.Rows.Count > 0 Then
                MyRowPosition = MyDataTbl.Rows.Count - 1
                Me.showRecords()
            End If
        End Sub
    
        Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
            If MyDataTbl.Rows.Count <> 0 Then
                MyDataTbl.Rows(MyRowPosition)("Column1") = TxtName.Text
                MyDataTbl.Rows(MyRowPosition)("Column2") = TxtState.Text
                MyDatAdp.Update(MyDataTbl)
            End If
            MessageBox.Show("Records", "Record Saved")
        End Sub
    
        Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
            If MyDataTbl.Rows.Count <> 0 Then
                MyCmdBld = New SqlCommandBuilder(MyDatAdp)
                MyDataTbl.Rows(MyRowPosition).Delete()
                MyRowPosition = 0
                MyDatAdp.Update(MyDataTbl)
                Me.showRecords()
            End If
        End Sub

    Here is Test table, The Id is the primary key:

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, January 30, 2018 2:53 AM
    Moderator