Record not being deleted in dbase, even tho the display on datagrid is deleted..

[Chumley the Walrus]

IN my code behind .vb page for a delete records script (this also does
a deletion confirmation with a javascript popup, this gets called on
my front .aspx page with the datagrid), I'm not sure if he
row.delete() in my Delete_Row sub is correct because when deleting a
record from the dbase, it doesnot get deleted from the actual
database, even tho on the datagrid it does show that the record is
deleted:


'''''''''''''
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.UI.WebControls


Public Class ConfirmDelDG
Inherits System.Web.UI.Page

Protected WithEvents dtgProducts As
System.Web.UI.WebControls.DataGrid
Private strConnection As String =
"SERVER=xxxx.xxx.xxx.xx;UID=xxxx;PWD=xxxx;DATABASE=players;"
Private strSql As String = "SELECT * FROM pictures Order by pid
desc"
Private objConn As SqlConnection

Private Sub Page_Load(ByVal Sender As System.Object, ByVal E As
System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
BindTheGrid()
End If
End Sub

Private Sub BindTheGrid()
Connect()
Dim adapter As New SqlDataAdapter(strSql, objConn)
Dim ds As New DataSet()
adapter.Fill(ds, "pictures")
Disconnect()

dtgProducts.DataSource = ds.Tables("pictures")
dtgProducts.DataBind()
End Sub

Private Sub Connect()
If objConn Is Nothing Then
objConn = New SqlConnection(strConnection)
End If

If objConn.State = ConnectionState.Closed Then
objConn.Open()
End If
End Sub

Private Sub Disconnect()
objConn.Dispose()
End Sub

Private Sub dtgProducts_ItemDataBound (ByVal sender As
System.Object, _
ByVal e As DataGridItemEventArgs) Handles
dtgProducts.ItemDataBound

Dim btn As Button
If e.Item.ItemType = ListItemType.Item or e.Item.ItemType =
ListItemType.AlternatingItem Then
btn = CType(e.Item.Cells(0).FindControl("btnDelete"), Button)
btn.Attributes.Add("onclick", "return confirm_delete();")
End If

End Sub

Public Sub Delete_Row(ByVal Sender As Object, ByVal E As
DataGridCommandEventArgs)

' Retrieve the ID of the product to be deleted
Dim pid As system.Int32 =
System.Convert.ToInt32(E.Item.Cells(0).Text)

dtgProducts.EditItemIndex = -1

' Create and load a DataSet
Connect()
Dim adapter As New SqlDataAdapter(strSql, objConn)
Dim ds As New DataSet()
adapter.Fill(ds, "pictures")
Disconnect()

' Mark the product as Deleted in the DataSet
Dim tbl As DataTable = ds.Tables("pictures")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("pid") _
}
Dim row As DataRow = tbl.Rows.Find(pid)
row.Delete()
' Display remaining rows in the DataGrid
dtgProducts.DataSource = ds.Tables("pictures")
dtgProducts.DataBind()

End Sub

End Class
''''''''''''

??????
chumley

 

[John Spiegel]

Hey Chumley,

It looks like you're not sending any word of the deletion back to the
database. To the contrary, you're actually re-pulling data from the
database then affecting the delete before letting it refresh the datagrid.

One way to do it would be to pull the data from the database in the
Delete_Row function as you currently do.
- Define a command object that would perform a delete to the effect of
"DELETE * FROM MyTable WHERE PID = @PID"
- Set its parameters
- Set the command object as your data adapter's DeleteCommand.
- Call your data adapter's Update() function.

Just as Fill uses the SelectCommand to pull data through the data adapter to
your data set. The Update will apply the queries referenced in its
InsertCommand, DeleteCommand and UpdateCommand back to the database.

HTH,

John

 

[Tom Gosselin]

Related comment/question -- In a situation of mine, the data adapter's
update() method didn't do a thing even though everybody I talked to and
everything I read said it would work just fine. Finally I gave up and I had
to resort to using an executenonquery to finally delete the row.

Any thoughts as to why this was the case?

Thanks,
TomG

----------------------------------