none
DataGridView - doing update/add/delete

    Question

  • I've been mostly doing asp.net the last couple of years and now need to do a new project in windows forms using the new DataGridView control. I have my 4 basic CRUD stored procedures written and have added a DataGridView control and bound it to the sprocs. My question is what work is involved in getting a basic grid to handle add/delete/update? It seemed like the new control could add a lot of the functionality automatically for simple grids but I can't seem to get it working or find any good tutorials on it. Thanks for any suggestions.
    Tuesday, November 08, 2005 3:21 PM

Answers

  • For the simple grids I was able to support add/edit/delete by using the RowChanged event and the TableAdapter to save changes. For the grids with more data and rules I just put an add and edit button on the form and used another form to do the adds and updates and just used the grid for display, sort, and delete. The DataBinding stuff with TableAdapters, etc still seems a little flaky to me. For example, one of my adapters had all my CRUD procedures defined in it but would not come up in intellisense. Not sure what was going on there. Hopefully more good books will be coming out soon now that 2005 is out.
    Thursday, December 15, 2005 1:03 PM

All replies

  • I built a simple master-detail example with 2 related/synchronized gridviews by:
    - using the Add New Data Source wizard (Data menu)
    - opening the Data Sources windows (Data menu)
    - using the pulldown in the Data Sources window to change the type for each table to DataGridView
    - dragging each table from the Data Sources window onto the form
    - setting the DataSource and DataMember for the detailBindingSource so that it synchronizes with the master (not required for a single gridview).
    - coding the bindingNavigator SaveItem click handler as follows.



    this.masterBindingSource.EndEdit();
    this.masterTableAdapter.Update(this.test1DataSet.Master);
    this.detailBindingSource.EndEdit();
    this.detailTableAdapter.Update(this.test1DataSet.Detail);

     


    Good luck...
    Tuesday, November 08, 2005 7:33 PM
  • Thanks. I guess my problem is at a more fundamental level. I assumed that with the new datagridview that when you check the add/update/delete settings and have stored procedures for each it could handle the edit automatically. I dragged the datasource onto the from as a DataGridView. I added the stored procedures in the table adapter. But actually wiring them up is what's confusing me. I figured that it being a windows form control that it would know when an update, delete, or add occurs and have events for them but I do not see any specific events for the DGV. I didn't want to add a save button since I don't want to do batch updates. I want changes to be on a row by row basis. I was hoping that if you update a row and then leave that row it would save the changes but not excatly sure how to accomplish that with least amount of code. There is no BindingNavigator control on the page. Is this required? Thanks for any help.
    Monday, November 21, 2005 2:45 PM
  • I'm pretty sure you don't need a Binding Navigator.  It just has the common database navigation controls all in one control.

    Regarding wiring up your stored procedures, I believe you do this in the DataSet designer.  Double-click the DataSet item (.xsd) in your project expolore.  This should bring up the DataSet table(s) in a visual editor.  Now highlight the section of the table graphic that says "<TblName>TableAdapter" and the Properties window (F4) will show the TableAdapter details.  Change the CommandType for each command to "Stored Procedure" and set the CommandText to be the stored procedure name.  This will cause the DataSet to be regenerated with SP calls instead of SQL statements.

    Good luck...

    Monday, November 21, 2005 3:40 PM
  • Hmmm. They are all set to Stored Procedure and have the correct proc name. But nothing actually gets added or updated to the database. Know of any samples out on the net of just a simple updateable DGV? Thanks.
    Monday, November 21, 2005 4:32 PM
  • Do you call TableAdapter.Update?
    Monday, November 21, 2005 6:30 PM
  • From where? That's my issue. Looks like there's a UserDeletedRow event that would work for Delete but how about the others? Does UserAddedRow fire after the user ebters information into the new row and leaves the row? Do you have to add a save button to each row or does the DGV know when your leaving the row to call an update. Sorry, just haven't been able to find any simple examples of an editable DGV, only read only ones. Thanks.
    Monday, November 21, 2005 9:09 PM
  • Hi,

    i'm also looking for the best way to save edited data from the datagridview to the database (i have 3 dgv in a master->child->child-situation, VS2005, C++ and actually SQL-Server Express 2005, later we will use SQL 2005). The loading process works fine - no problem, but i didn't find a way to save the changes.

    Nearly 2 1/2 weeks later, did you found a solution for your problem? Did you found a better example?

    Greetings from germany,

    Ralf Ellersiek

    Thursday, December 15, 2005 10:38 AM
  • For the simple grids I was able to support add/edit/delete by using the RowChanged event and the TableAdapter to save changes. For the grids with more data and rules I just put an add and edit button on the form and used another form to do the adds and updates and just used the grid for display, sort, and delete. The DataBinding stuff with TableAdapters, etc still seems a little flaky to me. For example, one of my adapters had all my CRUD procedures defined in it but would not come up in intellisense. Not sure what was going on there. Hopefully more good books will be coming out soon now that 2005 is out.
    Thursday, December 15, 2005 1:03 PM
  • Almost 2 years late now still want to know if any one could give an example code to do update, save (create) and delete with the datagridview directly,  with a button event.  I am not very experience therefore wants an example code.

    Many thanks

     

    Hope there be an example soon before I can learn it elsewhere

    • Proposed as answer by guillermofl Wednesday, October 26, 2011 3:42 PM
    • Unproposed as answer by guillermofl Wednesday, October 26, 2011 3:42 PM
    Friday, March 30, 2007 4:42 AM
  • Hi Kevin,

    Here's the steps to use the wizard:

    • Create a new Windows Forms application
    • Add a new SQL Database item to the project (cancel the Data Source Configuration Wizard)
    • Use Server Explorer to add a table to your database (be sure to add a Primary Key column)
    • Select Data, Show Data Sources
    • Click Add New Data Source
    • In the Data Source Configuration Wizard select:
      • Database (next)
      • Your database (next, next)
      • Your table (finish)
    • In the Data Sources windows, select your table, hit the dropdown arrow, and change the selection to DataGridView
    • Drag your table onto a form, and in the data grid view properties window select Dock Fill
    • If your primary key database column is Identity (auto-generated value), from the solution explorer open the DataSet (.xsd) that was generated, select the primary key column and change the AutoIncrementStep property to -1 (this will prevent the DataSet in memory from generating values that already exist - it's a little silly that they could not come up with a better solution, seeing that the database is going to generate a unique number when the update is sent over)
    • Give it a run!

    Unlike older technology such as VB6, databinding and the code generated by the wizard is fairly compatible with good architecture that you would program by hand. To understand what was generated and start down the road to programmatically taking control you're going to need to do some learning. You could Start with these help topics:
    ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_raddata/html/28edce21-220a-484c-b461-a75b0232d293.htm
    ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_cscon/html/82541d1d-b609-458b-a8eb-ef29357602c0.htm
    ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/dv_fxmclictl/html/0a45c661-89dc-4390-9cc6-c47eee501488.htm

     

    Good luck...

     

    Monday, April 02, 2007 11:24 AM
  • how could i permanentally delete any Row form DataGridView as well as Database(sqlserver 2000) using c#. For doing this, I want to use   BindingNavigator  Control.
    Thursday, April 19, 2007 6:15 AM
  • Hi Rastogi,

     

    If you follow the example in the previous post of this thread, it supports delete. The X button deletes a row from the grid/dataset and the save (diskette) button saves all dataset changes to the database.

     

    Good luck...

    Thursday, April 19, 2007 12:38 PM
  • hello Randal

    I have wirtten the given below Code on the Code behind Page

     

    private void Form2_Load(object sender, EventArgs e)

    {

    // TODO: This line of code loads data into the 'maheshDataSet1.Cities' table. You can move, or remove it, as needed.

    this.citiesTableAdapter.Fill(this.maheshDataSet1.Cities);

    // TODO: This line of code loads data into the 'maheshDataSet.Cities' table. You can move, or remove it, as needed.

    bindingNavigator1.BindingSource = citiesBindingSource;

    }

    private void saveToolStripButton_Click(object sender, EventArgs e)

    {

    citiesBindingSource.EndEdit();

    citiesTableAdapter.Update(maheshDataSet1.Cities);

    }

    whenever i press X button from the BindingNavigator Control, selected row delete from the DataGridView but it never delete form the Database.

    after that if i press  Save button of the BindingNavigatro Control it throw the given below Exception-----

     

    System.InvalidOperationException was unhandled
      Message="Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."
      Source="System.Data"
      StackTrace:
           at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
           at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
           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(DataTable dataTable)
           at BindingNavigator_Control.maheshDataSet1TableAdapters.CitiesTableAdapter.Update(CitiesDataTable dataTable) in C:\RND2005\AllControls\BindingNavigator_Control\BindingNavigator_Control\maheshDataSet1.Designer.cs:line 724
           at BindingNavigator_Control.Form2.saveToolStripButton_Click(Object sender, EventArgs e) in C:\RND2005\AllControls\BindingNavigator_Control\BindingNavigator_Control\Form2.cs:line 57
           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(Form mainForm)
           at BindingNavigator_Control.Program.Main() in C:\RND2005\AllControls\BindingNavigator_Control\BindingNavigator_Control\Program.cs:line 17
           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()

    Friday, April 20, 2007 6:55 AM
  • The TableAdapter for that table needs to have a delete command. Go to design view on the dataset (xsd), highlight the TableAdapter of the table in question, and check the Property called DeleteCommand. If should have been generated from your database by the wizard! Check your database design (for instance, make sure you have a primary key) and run the wizard again. If it still doesn't generate a delete command you could manually add the delete SQL to the TableAdapter.

     

    RG

    Friday, April 27, 2007 7:48 PM
  • Hii

    You can  use commanbuilder object

    like  OldbCommandbuilder commandbuilder=new OldbCommandbuilder (adapter)

    this sentence is genareting   your  update /delete/insert query   by automatically  if your  database   design is correct

     

    Wednesday, May 09, 2007 2:19 PM
  •  DigitalMan2112 wrote:
    I've been mostly doing asp.net the last couple of years and now need to do a new project in windows forms using the new DataGridView control. I have my 4 basic CRUD stored procedures written and have added a DataGridView control and bound it to the sprocs. My question is what work is involved in getting a basic grid to handle add/delete/update? It seemed like the new control could add a lot of the functionality automatically for simple grids but I can't seem to get it working or find any good tutorials on it. Thanks for any suggestions.
    Sunday, November 16, 2008 5:37 PM
  • This whole subject is so annoying.  Yes, I'm still using an old tool (VS2005), but I really want to know the same thing that other people are asking:
    How do you delete from the underlying database when you implement the MAGIC CODE of the BindingNavigator?  Is it simply impossible?  Oh, man, I'm so annoyed with this and there really are no samples out there.  When I figure this out I'm going to document it. 

    Everyone just says, "Oh, it works."  It does not work.  When you click the delete button, the row is deleted from your DataTable, but not the underlying database. 

    I actually got it working on another project and now I'm trying to figure out how I got it working, so I'm writing a sample app. using a local database file.  I wonder if that works.   Why would the binding navigator do so much and so little at the same time?
    It's like a terrible trick. Hey, wait, I'm going to go try it on VS2008 and see if it works there.  Maybe there's a bug or something.
    Wednesday, January 06, 2010 4:21 PM
  • I am also facing the same issue.

    If anyone or orzarbledev found the solution please post the solution in the same thread.

    Or mail me on

    ravi.khambhati@in.v2solutions.com
    ravick4u@yahoo.co.in

    Thanks
    Monday, February 08, 2010 10:47 AM
  • Read what Randal Greene1 wrote to Kevin. It works! Read and follow it carefully.

    The only thing I did differently was I didn't do:

    "... and in the data grid view properties window select Dock Fill"

    I'm using VS2008 - couldn't find Dock Fill

    One note...whenever you change your program and it rebuilds, you lose all data in the Sql Database.

    Wednesday, June 09, 2010 9:03 PM
  • I have a datagridview on a second form and just used the same codes for the add, save, and delete buttons as on the first data table form.  i just put three buttons on the datagridview form.
    ralph e. brooks
    Tuesday, February 15, 2011 11:44 PM
  • Sorry to read this to many years lates here is what help me:

        Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
            PartsBindingSource.AddNew()
            Me.PartsTableAdapter.Update(Me._JOB_ORDERS_3000DataSet.Parts)
        End Sub
    

    is a simple save using the datagrids but work just fine, change the dat set acording to your tables meeds
     

    Wednesday, October 26, 2011 3:49 PM
  • your code is not updated the DB, plz review and code it full way, from start to mention the code format
    ShoaibA Shaikh
    Thursday, December 15, 2011 9:44 PM