Unable to delete Row

[Rajya]

Hi

I am unable to delete Row(s) from a DataTable - nothing actually happens - there are no errors & the lines get executed but when I check the actual Table, I find all the Rows intact! Following is the code I am using for delete -

cEssentials.cDataTable.Rows(intIndexToDelete).Delete()
cEssentials.cDataSet.AcceptChanges()
cEssentials.cSqlDataAdapter.Update(cEssentials.cDataSet, Security")

I am Filling the DataSet using the same DataAdapter in the Form Load event; later, the above delete code is getting fired from a proc.

I have also tried out .Remove and .RemoveAt but both behave the same.The DataTable, DataSet and SqlDataAdapter objects are not touched after the Form Load.

Where am I going wrong? Please help :-(

 

[jmcilhinney]

You are calling AcceptChanges before you are calling Update. AcceptChanges sets the RowState property of every DataRow to Unchanged, so when you try to update there are no modified rows so nothing happens. When you call Update on a DataAdapter the call to AcceptChanges is implicit, so you never need to call it yourself, before or after updating.

 

[Rajya]

Thanks for you reply.

I now get the following error when I comment out .AcceptChanges and go to .Update directly -

An unhandled exception of type 'System.InvalidOperationException' ...
Additional information: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

Even though I am using a SqlCommand (StoredProc) for Filling the DataSet, & setting the SqlDataAdapter's SelectCommand property, I am just using .Delete straightoff here and then .Update. Another serious mistake somewhere, I suppose?

Also, when I use .Delete, the .RowState changes to 'Deleted', but when I am using .Remove / .RemoveAt, the .RowState stays at 'Unchanged' (all this before calling .Update). I have kept .AcceptChanges commented out here too. The Rows stay intact. Why?

I think I am asking too many questions, but I really have no answers myself :-(

 

[jmcilhinney]

If you call Remove or RemoveAt, the DataRow is unchanged but it is removed from the DataTable. This has no effect whatsoever on the database itself, but only on the DataTable object within your app. When you call Delete, the RowState is changed to Deleted so that the database engine knows that this row must be deleted from the database itself when you call Update.

Having said that, you still need an SQL statement to actually delete the row. It can be a stored procedure or you can write the SQL in your app, but an SqlDataAdapter has a DeleteCommand property that must be assigned an SqlCommand object that contains a valid SQL DELETE statement. The same goes for inserting and updating. There are corresponding InsertCommand and UpdateCommand properties of the SqlDataAdapter class.

 

[Rajya]

Thanks for your valuable time

But if I HAVE TO write a Stored Procedure or Sql statement to delete a Row, then why should I go to the trouble of using a SqlDataAdapter, for the delete?! Is there an advantage there?

I could have used the SqlCommand just as well and passed on the Row details as Parameters to the SP, and run the SqlCommand.ExecuteNonQuery method.

Am I still not getting something obvious???

Further, if the .Remove / .Remove only removes the Rows from the DataTable, how do I go about removing the same physically from the database?

 

[jmcilhinney]

Let's say that you execute a select query that returns 1000 records. The user then deletes 150 of those records in no particular pattern. Using an SqlDataAdapter with a parameterised DeleteCommand will execute that command on the database with a single line of code. You cannot use ExecuteNonQuery in this way because you would need to specify in your SQL statement the ID of each record to delete.

When you call Remove or RemoveAt, the specified DataRow is removed from the DataTable as though it was never part of the result set in the first place. When you call Delete, the specified DataRow is marked as Deleted. When you then call Update on your SqlDataAdapter aginst your DataTable, the process iterates over all the rows marked as Deleted, uses the parameters in your SQL statement to substitute the actual field values (usually just the primary key for deleting) from the DataRow into the SQL and executes that statement for each row. The same process is then executed for every row marked as Added using the InsertCommand, and then for every row marked as Modified using the UpdateCommand.

 

[Rajya]

Thanks for the pointed advice.
But I am still not able to get it working & remove the selected rows physically.

I researched the issue - it seems you need to have a SqlCommandBuilder object to automatically configure a SqlDataAdapter, in case a SelectCommand is already working. Therefore, I attached a Builder object with the DataAdapter.

It also seems that the Table in question should have at least one unique column - mine didn't have any. So, I defined one with the uniqueidentifier datatype and filled it with unique values supplied by the T-SQL newid() function.

Note that I am using a Stored Proc with the SelectCommand which all fetches all the rows without any condition.

Now how do I fix this situation? I have put all the relevant code into a Class and am attaching the same here. The proc containing the SelectCommand is being called by the relevant Form Load event, while the Delete proc is being called by a button click event on the same form.

Please help (with sample code if you can).

 

[jmcilhinney]

Here is a simple example that you can adapt to your own needs. If there are parts you don't understand then I suggest you read up on them. I could write more code for you, but if you don't understand it then you'll just have to ask for help again the next time. If you can understand this example then you will be able to adapt the principle to almost any situation. Also note that you can use an SqlCommandBuilder, or several other methods, to generate your SQL statements. Note that the command builder requires that your query return fields from a single table and return the primary key (perhaps any unique field is enough, but I don't use command builders so I can't tell you for sure).

		[color=Blue]Dim[/color] myConnection [color=Blue]As New[/color] SqlConnection [color=Green]'You need to configure the connection yourself.[/color]

		[color=Green]'Create a new data adapter. The SQL query is automatically assigned to a new SelectCommand.[/color]
		[color=Blue]Dim[/color] myAdapter [color=Blue]As New[/color] SqlDataAdapter("SELECT * FROM Table1", myConnection)

		[color=Blue]Dim[/color] myTable [color=Blue]As New[/color] DataTable("Table1")

		[color=Green]'Retrieve the data.[/color]
		myAdapter.Fill(myTable)

		[color=Green]'Edit the data here. You can delete, insert and update as many rows as you like.[/color]

		[color=Green]'Create a new command and assign it to the DeleteCommand property.[/color]
		myAdapter.DeleteCommand = [color=Blue]New[/color] SqlCommand("DELETE FROM Table1 WHERE ID = @ID", myConnection)

		[color=Green]'Add a parameter that will substitute the actual ID for the place-holder for each deleted row.[/color]
		myAdapter.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 0, "ID")

		[color=Green]'You would need to do the equivalent for the InsertCommand and UpdateCommand
 		'if you want to insert and update rows, but I'll leave that up to you.[/color]

		[color=Green]'Commit any changes made to the data table back to the database.[/color]
		myAdapter.Update(myTable)

Edit:
It occurred to me that you may not be able to use a command builder if your query uses a stored procedure. I don't know if this is the case but it is possible.

 

[Rajya]

Thanks Jmcilhinney
With your great help, I was able to crack the 'uncrackable' (for me)!
Basically, what it seemed to me that you cannot use a SqlCommand object explicitly with the SqlDataAdapter in this case.

Once I observed that & re-wrote my code, things flowed smoothly.Here's what is working now -

.cSqlDataAdapter = New SqlClient.SqlDataAdapter("Entire Security Table", gConn)
.cSqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
.cSqlCommandBuilder = New SqlClient.SqlCommandBuilder(.cSqlDataAdapter)

And then I am calling the .Delete and .Update elsewhere.
Please also note that Stored Procedure is also working with the CommandBuilder object.

Thanks again!