3 Replies - 2149 Views - Last Post: 08 January 2012 - 10:43 AM Rate Topic: -----

#1 John-Ellis   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 305
  • Joined: 23-March 10

delete command for sql database app

Posted 08 January 2012 - 08:48 AM

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."
  Source="System.Data"
  StackTrace:
       at System.Data.Common.DbDataAdapter.UpdatingRowStatusErrors(RowUpdatingEventArgs rowUpdatedEvent, DataRow dataRow)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at SlimmingWorld.Breakfast.SaveToolStripButton_Click(Object sender, EventArgs e) in ***.vb:line 25
       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(Int32 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 System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at SlimmingWorld.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 




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

Is This A Good Question/Topic? 0
  • +

Replies To: delete command for sql database app

#2 John-Ellis   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 305
  • Joined: 23-March 10

Re: delete command for sql database app

Posted 08 January 2012 - 10:09 AM

I have an idea, can I add a second line of code like below to allow me to delete data from my dataset.

 breakfast.Update(dsbreakfast, "breakfast")



Can I add this line underneath

breakfast.Delete(dsbreakfast, "breakfast")



I will give it a blast, hopefully this will work.

John
Was This Post Helpful? 0
  • +
  • -

#3 John-Ellis   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 305
  • Joined: 23-March 10

Re: delete command for sql database app

Posted 08 January 2012 - 10:18 AM

Ok, I changed my code, please see below

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



I get an error on the DeleteCommand line saying "Property access must assign to the property or use it's value.

I will have to keep googling.

Cheers

John
Was This Post Helpful? 0
  • +
  • -

#4 John-Ellis   User is offline

  • D.I.C Regular

Reputation: 8
  • View blog
  • Posts: 305
  • Joined: 23-March 10

Re: delete command for sql database app

Posted 08 January 2012 - 10:43 AM

ok, reading some stuff on google, it looks like I need to add a primary key and that sort it out.

John
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1