Dynamic SQL generation for the DeleteCommand is not supported ...

[ietprofessional]

I'm using the SqlCommandBuilder class and a datagrid and when I try to delete something from my datagrid and make updates I'm getting this error:

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

Do you know what this could be?

Here is my code:

   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Not Page.IsPostBack Then
            Dim daServices As SqlDataAdapter
            Dim dsServices As DataSet
            ' Creating DataTable
            Dim dt As DataTable
            dsServices = New DataSet

            ' services table (Step Two)
            dt = New DataTable("services")
            dt.Columns.Add("partnumber")
            dt.Columns.Add("application")
            dsServices.Tables.Add(dt)

            ' create the data adapter 
            Dim accountnameid As String = Session("accountnameid")

            daServices = New SqlDataAdapter("SELECT PartNumber, Application FROM  dbo.ServicesInfo WHERE (AccountNameID = " + accountnameid + ")", ConfigurationSettings.AppSettings("SQLServer"))

            ' create an SqlCommandBuilder - this will automatically generate the
            ' commands, and set the appropriate properties in the dataAdapter
            Dim commandBuilder As New SqlCommandBuilder(daServices)
            ' create the DataSet 

            ' fill the DataSet using our DataAdapter into a table called users 
            daServices.Fill(dsServices, "services")
            ' set the DataGrid source to the one table in our dataset 
            dgServices.DataSource = dsServices.Tables(0)
            Session("ServicesDS") = dsServices
            Session("ServicesDA") = daServices
        End If
    End Sub

    Private Sub btnAddService_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddService.Click
        Dim dr As DataRow
        dr = Session("ServicesDS").Tables("services").NewRow
        dr.Item("partnumber") = txtPartNumber.Text
        dr.Item("application") = txtApplication.Text

        Session("ServicesDS").Tables("services").Rows.Add(dr)
        dgServices.DataSource = Session("ServicesDS").Tables("services")
        dgServices.DataBind()
        ToggleDGsVisibility(dgServices)
        Session("ServicesDA").Update(Session("ServicesDS").Tables("services"))
    End Sub

    Public Sub ToggleDGsVisibility(ByVal dg As DataGrid)
        If dg.Items.Count < 1 Then
            dg.Visible = False
        Else
            dg.Visible = True
        End If
    End Sub

    Private Sub lnkStepThree_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkStepThree.Click

        Response.Redirect("NewAccountStep3.aspx")
    End Sub

    Private Sub dgServices_DeleteCommand(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgServices.DeleteCommand

        CType(Session("ServicesDS"), DataSet).Tables("services").Rows(e.Item.ItemIndex).Delete()

        dgServices.DataSource = CType(Session("ServicesDS"), DataSet).Tables("services")
        dgServices.DataBind()
        ToggleDGsVisibility(dgServices)
        Session("ServicesDA").Update(Session("ServicesDS").Tables("services"))
    End Sub
End Class

 

[ietprofessional]

I added a datakeyfield to my datagrid, which is the primary key to the table I'm deleting from and I'm getting a new error. What is the problem now?

Concurrency violation: the DeleteCommand affected 0 records.


Line 111: dgServices.DataBind()
Line 112: ToggleDGsVisibility(dgServices)
Line 113: Session("ServicesDA").Update(Session("ServicesDS").Tables("services"))