Member Avatar for discovery-power

Hi All,

I always have trouble with this every time I create a desktop app which needs to update data from a database.

So, I have created and coded my form to load data into a data grid view control, I can add data to the data grid view and it updates fine but when I come to delete a line, I get the error message below

System.InvalidOperationException was unhandled
  Message="Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information."

Please see below my code for the form.

Imports System.Data.SqlClient
Public Class Breakfast

    Dim conn As SqlConnection
    Dim breakfast As SqlDataAdapter
    Dim dsbreakfast As DataSet

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

        Try
            conn = New SqlConnection("Data Source=JOHN-ELLIS-PC;Initial Catalog=SlimmingWorld;Integrated Security=True")
            dsbreakfast = New DataSet
            breakfast = New SqlDataAdapter("SELECT RedorGreen,Name,Ingredients,Sins FROM Breakfast", conn)
            Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(breakfast)
            breakfast.Fill(dsbreakfast, "breakfast")
            DataGridView1.DataSource = dsbreakfast.Tables("Breakfast")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


    End Sub

    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
        breakfast.Update(dsbreakfast, "breakfast")
    End Sub
End Class

I believe it's the update button code that's causing the problem.

Can anyone help.

Thanks

John

  • 1 Contributor
  • 1 Reply
  • 142 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by discovery-power

Dani AI

Generated

marked this as "sorted". The exception shown in the thread happens when the runtime can't get reliable key/row identity information to build the SQL needed to remove or update rows. Below are practical, low‑risk ways to avoid that and make updates more predictable.

Set schema/key info on the client before letting a command generator run. Two common approaches:

  • Tell the adapter to bring keys with the schema before filling:

    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
adapter.FillSchema(ds, SchemaType.Source)
adapter.Fill(ds, "Breakfast")

  • Or create/assign a primary key column in the DataTable if you need a client-side key (AutoIncrement helps):

    Dim tbl As DataTable = ds.Tables("Breakfast")
Dim keyCol As New DataColumn("RowId", GetType(Integer))
keyCol.AutoIncrement = True
tbl.Columns.Add(keyCol)
tbl.PrimaryKey = New DataColumn() { keyCol }

If you prefer full control, build the adapter commands yourself instead of relying on dynamic generation. Always map parameters and use the original row values for WHERE clauses so deletes/updates target the correct row:

Dim del As New SqlCommand("DELETE FROM Breakfast WHERE Id=@Id", conn)
del.Parameters.Add("@Id", SqlDbType.Int, 0, "Id").SourceVersion = DataRowVersion.Original
adapter.DeleteCommand = del

Quick troubleshooting checklist:

  • Call the binding/source EndEdit methods to commit in‑grid changes before updating.
  • Do not call AcceptChanges on the DataSet before calling Update (that clears change tracking).
  • Consider a rowversion/timestamp column for safer concurrency checks.

For more detail on automatic command generation and schema handling, see the Microsoft docs for SqlCommandBuilder and DataAdapter.FillSchema:

DataAdapter.FillSchema method

Member Avatar for discovery-power

Hi All,

I always have trouble with this every time I create a desktop app which needs to update data from a database.

So, I have created and coded my form to load data into a data grid view control, I can add data to the data grid view and it updates fine but when I come to delete a line, I get the error message below

System.InvalidOperationException was unhandled
  Message="Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information."

Please see below my code for the form.

Imports System.Data.SqlClient
Public Class Breakfast

    Dim conn As SqlConnection
    Dim breakfast As SqlDataAdapter
    Dim dsbreakfast As DataSet

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

        Try
            conn = New SqlConnection("Data Source=JOHN-ELLIS-PC;Initial Catalog=SlimmingWorld;Integrated Security=True")
            dsbreakfast = New DataSet
            breakfast = New SqlDataAdapter("SELECT RedorGreen,Name,Ingredients,Sins FROM Breakfast", conn)
            Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(breakfast)
            breakfast.Fill(dsbreakfast, "breakfast")
            DataGridView1.DataSource = dsbreakfast.Tables("Breakfast")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


    End Sub

    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
        breakfast.Update(dsbreakfast, "breakfast")
    End Sub
End Class

I believe it's the update button code that's causing the problem.

Can anyone help.

Thanks

John

I forgott to add a primary key.

Sorted

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.