updating data - having problems

[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

[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