We help IT Professionals succeed at work.

general question about datatable and sql write

Amien90
Amien90 asked
on
i have a certain datatable in a gridview. Users can edit or delete a row. I'm using RowCommand for this.

the data is written to the database when the users confirms to save the current shown gridview to the database.

before, i deleted all the lines from the database (related to a certain productID) and then add all rows from the gridview.
i want to replace the procedure by using an UPDATE query instead of a DELETE and then INSERT query.

Updating the rows using UPDATE is no problem, if the users doesn't delete a line from the gridview. But if a user deleted a line from the gridview, then i have problem. i can update the ones that still exist, but i can't flush the reconds in the SQL database for the records that were deleted from the gridview.

my solution is to move the DELETE query to the rowcommand ..
other solution is to flag the rows that needs to be deleted and don't show the flagged lines in the gridview.

anyone has a different solution for this?
Comment
Watch Question

You may want to consider using a datatableadapter of the predefined datatable in a dataset of the app_code folder.   The datatableadapter can be configured with your select, update, delete, and insert commands (I prefer Stored Procedures).  You can use this datatable for your gridview.  When the user confirms, the following snippet (change names appropriately) to make changes to the SQL database.  The adapter will call the appropriate command to add, change, delete, or no action for each record to make the database reflect your table.

for more on tableadapters:
http://msdn.microsoft.com/en-us/library/bz9tthwx(v=VS.100).aspx
http://msdn.microsoft.com/en-us/library/ms171933(v=VS.100).aspx#Y298



''  Retrieve your datatable used by the gridview (
Dim dt As MyDataSet.ProductDataTable = DirectCast(Session("AnExample"), MyDataSet.ProductDataTable)


Using ta As New MyDataSetTableAdapters.ProductTableAdapter()
  ta.Update(dt)
End Using

Open in new window

Author

Commented:
the gridview is not used for editing, but only to show values.

The actual editing is done below the gridview, where there is a form with all columns. Users can there, using listboxes, dropdown etc, changed or add the values.

Using the gridview to edit would be not end-user friendly, because there are too much values to fill in
Let me clarify, I was referencing the datatable, not the gridview you use to display some of its values.  How the datatable is modified is not the part of the comment.  The key point is the datatable keeps a row state of each row; Unchanged, Added, Modified, Deleted,and Detached.  The detached is only while you are adding a new row thru a newrow process of adding a new record.  When the user confirms the changes, the datatable is sent to the tableadapter.  

For more on Rowstates:
http://msdn.microsoft.com/en-us/library/system.data.datarowstate.aspx