none
Using 'Update' to transfer data from a dataset to a database. RRS feed

  • Question

  • Using visual studio 2010 express with SQL server 3.5.

    So I've seen multiple examples oh how to update a database with a dataset. I think I understand the process:

    Fill dataset with database values
    Make changes to dataset
    Update the datebase with new dataset values.

    The problem is, I can never get the Update/UpdateAll function to work right. It always says, "Update requires a valid Update Command when passed DataRow collection with modified rows."

    I've even tried using the code automatically generated by the default save button given on the form. Wouldn't work. I'm stuck. Help?

    Code I'm using:
    private void testDBTableBindingNavigatorSaveItem_Click(object sender, EventArgs e)
            {
                this.Validate();
                this.testDBTableBindingSource.EndEdit();
                this.tableAdapterManager.UpdateAll(testDBDataSet);
            }

    Thanks,
    Racrumb

    Error details:
    System.InvalidOperationException was unhandled
      Message=Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
      Source=Test
      StackTrace:
           at Test.TestDBDataSetTableAdapters.TableAdapterManager.UpdateAll(TestDBDataSet dataSet) in C:\Users\XXX\documents\visual studio 2010\Projects\Test\Test\TestDBDataSet.Designer.cs:line 1209
           at Test.Menu.testDBTableBindingNavigatorSaveItem_Click(Object sender, EventArgs e) in C:\Users\XXX\documents\visual studio 2010\Projects\Test\Test\Menu.cs:line 27
           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(IntPtr 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(Form mainForm)
           at Test.Program.Main() in C:\Users\XXX\documents\visual studio 2010\Projects\Test\Test\Program.cs:line 18
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly 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, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException:



    • Edited by Racrumb Monday, July 23, 2012 7:25 PM
    • Moved by Vicky SongMicrosoft employee Tuesday, July 24, 2012 2:15 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Monday, July 23, 2012 7:23 PM

Answers

  • Hi Racrumb,

    I have tested the project you posted, it is not caused by your code, the root cuase is there's no primary key in your database table. Update must have a primary key to identify which record to update. So, please add a id column in your database table, and set it as primary key, then, the problem will go away.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Racrumb Tuesday, July 31, 2012 7:45 PM
    Tuesday, July 31, 2012 1:46 AM
    Moderator

All replies

  • Hi Racrumb,

    I am moving your case to the ADO.NET Dataset forum so that you can get better support there.

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, July 24, 2012 2:15 AM
  • are you updating the dataset fro each update OR at one go, say storign the changes in some temporary placeholder and then updating.

    http://support.microsoft.com/kb/307587 is link, you can check for your requirement.

    regards

    joon

    Tuesday, July 24, 2012 12:51 PM
  • Thanks Vicky, and sorry.

    Joon,

    The article looks helpful thank you! I can't get to my code at the moment but I think the problem is that I am missing namespaces. I'll post results when I can get to it.

    -Racrumb

    Tuesday, July 24, 2012 3:21 PM
  • Still can't get it to work.... In VS 2010 the connection between DB and dataset seems to be automated so I don't need to set up a connection in the code, as I can fill the dataset with database items. I can update the dataset, but I can't actually save the items back to the database. What am I missing? What information do you all need that will help?

    Thanks,

    -racrumb

    Wednesday, July 25, 2012 3:05 AM
  • Hi Racrumb,

    Please try the steps below.

    1. Right click your adapter on the designer, select 'Configure...'.

    2. Click 'Advanced Options...'.

    3. Make sure all the three options are selected and click 'OK'.

    Please let me know if this works.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 25, 2012 3:16 AM
    Moderator
  • On my form[Design] I have a "datasetTableName"TableAdapter, but it has no Configure... selection. Perhaps I'm not looking a the right thing.


    • Edited by Racrumb Wednesday, July 25, 2012 3:49 AM
    Wednesday, July 25, 2012 3:48 AM
  • Hi Racrumb,

    Please look at the picture below.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 25, 2012 4:51 AM
    Moderator
  • Only "GenerateInsert..." is checked. The other two options ("Use optimistic..", "Refresh the data table") are grayed out. Is this because I'm on the Express edition?

    Thanks,

    -Racrumb

    Wednesday, July 25, 2012 2:25 PM
  • Hi Racrumb,

    You can also configure your own command to avoid the error message. Right click the tableadapter and select Configure, you will see the select command in the textbox, please make sure all the columns are in select section and then click 'Next' until the wizard end.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, July 26, 2012 9:56 AM
    Moderator
  • Sorry for the delayed response. Went through the steps and got all 3 checked. What's the next step?
    Saturday, July 28, 2012 7:18 PM
  • Hi Racrumb,

    Have you check the select section which I suggest in above post? If the select query is correct and the auto-generated commands still lead the exception, I suggest you to upload the project to SkyDrive and post the link here. Then, I will help you to test it and find the root cause.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, July 30, 2012 8:47 AM
    Moderator
  • Here's a link to the download: http://www.sendspace.com/file/2ytfot

    Test.zip

    I've tried multiple variations of the code, not all of which are commented out. Hope you can figure it out! Thank you so much for all your help already.

    -racrumb

    Monday, July 30, 2012 10:47 PM
  • Hi Racrumb,

    I have tested the project you posted, it is not caused by your code, the root cuase is there's no primary key in your database table. Update must have a primary key to identify which record to update. So, please add a id column in your database table, and set it as primary key, then, the problem will go away.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Racrumb Tuesday, July 31, 2012 7:45 PM
    Tuesday, July 31, 2012 1:46 AM
    Moderator
  • Well I feel silly...

    Thanks a lot Allen!

    -racrumb

    Tuesday, July 31, 2012 7:51 PM