DataRow Delete Method

  • Thread starter Thread starter rn5a
  • Start date Start date
R

rn5a

The .NET 2.0 documentation states the following:

When using a DataSet or DataTable in conjunction with a DataAdapter & a
relational data source, use the Delete method of the DataRow to remove
the row. The Delete method marks the row as Deleted in the DataSet or
DataTable but does not remove it. Instead when the DataAdapter
encounters a row marked as Deleted, it executes its DeleteCommand
method to delete the row at the data source. The row can then be
permanently removed using the AcceptChanges method.

Now I have this code:

<script runat="server">
Sub Page_Load(.....)
Dim sqlConn As SqlConnection
Dim sqlDapter As SqlDataAdapter
Dim dSet As DataSet
Dim dTable As DataTable

sqlConn = New SqlConnection("..........")
sqlDapter = New SqlDataAdapter("SELECT * FROM Marks", sqlConn)

dSet = New DataSet()
sqlDapter.Fill(dSet, "Marks")

dTable = dSet.Tables("Marks")

'delete the 4th row from the DataTable
dTable.Rows(3).Delete()
dTable.Rows(3).AcceptChanges()

dgMarks.DataSource = dSet.Tables("Marks").DefaultView
dgMarks.DataBind()
End Sub
</script>

<form runat="server">
<asp:DataGrid ID="dgMarks" runat="server"/>
</form>

When I execute the above code, the 4th row gets deleted from the
DataTable & hence the DataGrid doesn't display that row. Now how do I
make the SqlDataAdapter encounter the 4th row which has been marked as
Deleted in the Page_Load sub? Do I have to add the OnDeleteCommand
event to the DataGrid like this?

<asp:DataGrid ID="dgMarks" OnDeleteCommand="DeleteItem"
runat="server"/>

& then add the event handler named "DeleteItem" which will have the
same code snippet that exists in the Page_Load sub (of course, except
for the 2 lines that immediately precede the 'End Sub' line)?
 
Your problem is that you are accepting changes, this has the effect of
removing the row, hence the DataAdapter does not see the row maked for
deletion and will not delete the row in the database itself.
'delete the 4th row from the DataTable
dTable.Rows(3).Delete()
dTable.Rows(3).AcceptChanges() '// REMOVE THIS LINE //
Click to expand...

HTH
 
You are correct, Ohm but just removing the AcceptChanges line won't
delete the record from the data source. To delete the row from the data
source, the DataAdapter's Update method has to be invoked without which
the deletion won't take place. The code would look something like this
(it comes immediately after the dTable.Rows(3).Delete line shown in
post #1; of course, remove or comment out the AcceptChanges line as
well):

'instantiate the SqlCommand object with the SQL
'query to delete the row from the data source

sqlCmd = New SqlCommand("DELETE FROM Marks WHERE ID = 4", sqlConn)
sqlDapter.DeleteCommand = sqlCmd
sqlDapter.Update(dTable)

'the above Update line is equivalent to
sqlDapter.Update(dSet.Tables("Marks"))

That's it! Now the 4th row will be deleted from the SQL Server 2005 DB
table data source permanently.
 
Yes well of course.

I assumed you were not getting the result you wanted and 'Were' invoking the
update method. Thats why I told you to remove the AcceptChanges line.

I find your response a little strange because it sounds like you are trying
to teach me ADO.NET basics, there is no need beleive me, I have been doing
this stuff for nearly four years now.

Good Luck.
 
Oh! no, Ohm, I am not trying to teach you ADO.NET. After all, how can
I, who has been working with ADO.NET since last 4 months, teach you
ADO.NET which you have been doing since last 4 years?

Actually I myself strugggled to get my code going & wasn't aware that
the DataAdapter's Update method needs to be invoked to reflect the
changes in the data source. Had I known that, you would have seen that
Update line in the code in post #1 (I don't know what made you assume
that I was invoking the Update method). So that's the reason why I
mentioned about the Update method in my follow-up post. & I swear I
wasn't aware of the fact the you have got 4 years of experience behind
you as far as ADO.NET is concerned.

There's another reason why I mentioned about the Update method in my
follow-up post. Often I find that a post has been answered in just
words like "You do this..& do that...delete that line" so on & so forth
(note that I am not referring to your first response to this post).
This often leaves the person who has put forth his question (especially
if he happens to be a newbie) perplexed as to what he should do & what
he shouldn't. A better way of answering would be a code snippet instead
of "you do this & then do that...". A small code snippet is equivalent
to those hundred words. Of course, all posts don't warrantee a code
snippet wherein a small explanation would suffice. For e.g. it was very
much kind of you to add that small code-snippet after your explanation
to my original post though your explanation was good enough to make me
realize where I was going wrong but it doesn't tell anything about the
DataAdapter's Update method. Had you added one line saying that the
DataAdapter's Update method needs to be invoked finally to change the
data source, then it would have saved me nearly an hour's time. So
that's the reason why I mentioned about the Update method & added the
small code snippet so that in future, if anyone struggles like I did &
if he happens to come across this post without wasting much time, then
he would get his problem resolved quickly.

My follow-up post mentioning about the Update method was meant more for
those who aren't aware about the Update method & who might encounter a
similar problem rather than those who already know about it.

Lastly, no offence intended.
 
No offence taken. Glad you sorted it out. Had I known, you had not completed
the code, I would have posted a snippet. I will be more careful next time.

Cheers
 
Back
Top