Delete Command for datagrid not working 1

[slwolf8]

My delete command for my datagrid isn't working. I am using the following code:

Dim strSubCatID As String = dgSubCat.DataKeys(e.Item.ItemIndex).ToString

Dim dr As DataRow
Dim i As Integer
For i = 0 To DsBudget.Tables("SubCat").Rows.Count - 1
dr = DsBudget.Tables("SubCat").Rows(i)
If dr(0) Is strSubCatID Then
dr.Delete()
Exit For
End If
Next

daSubCat.Update(DsBudget, "SubCat")
SetDS()

Any ideas? I've been working on this so long I can't think of anything else to try!! Thanks in advance.

 

[jzelhart]

Why don't you try a Delete SQL statement instead?

Hope everyone is having a great day!

Thanks - Jennifer

 

[slwolf8]

i'm just a little confused on what to do . The book I am following (Microsoft Certification book by Gunderloy) had me setup the code this way in the delete command event of the datagrid and it also had me configure the data adapter with a delete command:

DELETE * FROM Subcat HERE SubCatID=@SubCatID

Now what I don't understand is this. When I call the update method on the data adapter, is the delete command (not the event) automatically fired? Also, at what point does the item command fire? Thanks!

 

[OhioSteve]

Of course there are many ways to handle deletions. However, I suspect that the most common technique is to use the data adapter's delete command. If that is your plan, then the delete button should fire code like this~


SqlDataAdapter1.DeleteCommand.Parameters("@original_id").Value = [variable name]

SqlConnection1.Open()

SqlDataAdapter1.DeleteCommand.ExecuteNonQuery()

SqlConnection1.Close()

Note: In this sample, the first two lines are actually one line of code. The tek-tips window is wrapping my text.

Personally, I don't use this technique, because it really does kickoff an sql with the verb "delete". I add a boolean field called "deleted" to all of my sql server tables. Its default value is "false". All of my interfaces show records from select queries. Those queries have a parameter excluding records where deleted=true. My delete button actually sets deleted=true and refreshes the screen.

When my users tell me that they mistakenly deleted a record, I tell them that the situation is probably hopeless. Then the next morning I claim that I "found" the record. They don't know that they can't delete a record.

 

[slwolf8]

Oh my word that is SOOO funny. I like that idea!!! Thanks, I'll give that a try!!!!

 

[slwolf8]

One more question. If I were to call SqlDataAdapter1.update(ds, "table") in the deletecommand event of the datagrid would that be the same thing as doing the SqlDataAdapter.deletecommand.executenonquery()?

 

[slwolf8]

I am still having trouble with this!! The deletions (updates) are done in the database but the datagrid doesn't update to reflect the changes. Here is the code I am using now:

Private Sub dgSubCat_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgSubCat.DeleteCommand

Dim strSubCatID As String = dgSubCat.DataKeys(e.Item.ItemIndex).ToString

DsBudget.Tables("SelSubCat").Rows.Count - 1

daSubCat.DeleteCommand.Parameters("@SubCatID").Value = strSubCatID

SqlConn.Open()
daSubCat.DeleteCommand.ExecuteNonQuery()

daSubCat.Update(DsBudget, "SelSubCat")

SetDS()
SqlConn.Close()


End Sub

Private Sub SetDS()
dgSubCat.DataSource = DsBudget
dgSubCat.DataMember = "SelSubCat"
dgSubCat.DataKeyField = "SubCatID"
dgSubCat.DataBind()
End Sub

also, on pageload, I fill the data adapter.

 

[OhioSteve]

This is my code under the datagrid's "update" event. One problem with my system (in .Net) is that the "deleted" field does not resemble a "delete" button. I haven't fixed that yet. My story involved an MS Access database.

SqlDataAdapter1.UpdateCommand.Parameters("@auditorid").Value = CType(e.Item.Cells(1).Controls(0), TextBox).Text.ToString()

SqlDataAdapter1.UpdateCommand.Parameters("@auditorid").Value = CType(e.Item.Cells(1).Controls(0), TextBox).Text.ToString()

SqlDataAdapter1.UpdateCommand.Parameters("@vacationStart").Value = CType(e.Item.Cells(2).Controls(0), TextBox).Text.ToString()

SqlDataAdapter1.UpdateCommand.Parameters("@vacationEnd").Value = CType(e.Item.Cells(3).Controls(0), TextBox).Text.ToString()

SqlDataAdapter1.UpdateCommand.Parameters("@deleted").Value = CType(e.Item.Cells(4).Controls(0), TextBox).Text.ToString()

SqlDataAdapter1.UpdateCommand.Parameters("@original_id").Value = Convert.ToInt32(DataGrid1.DataKeys(e.Item.ItemIndex))

SqlConnection1.Open()
SqlDataAdapter1.UpdateCommand.ExecuteNonQuery()
SqlConnection1.Close()

DataGrid1.EditItemIndex = -1

DataSet11.Clear()
SqlDataAdapter1.Fill(DataSet11)
SqlDataAdapter2.Fill(DataSet11)
DataGrid1.DataBind()

 

[slwolf8]

Thank you again so much!!!!! Clearing the dataset first did it!!! That also solved another problem I had. Thank you soo much!!!