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

Discussion in 'ASP .Net Web Controls' started by Chumley the Walrus, Jul 27, 2004.

  1. 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
     
    Chumley the Walrus, Jul 27, 2004
    #1
    1. Advertising

  2. Chumley the Walrus

    John Spiegel Guest

    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
    "Chumley the Walrus" <> wrote in message
    news:...
    > 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, Jul 27, 2004
    #2
    1. Advertising

  3. Chumley the Walrus

    Tom Gosselin Guest

    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

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

    "John Spiegel" <> wrote in message
    news:eP%23PD1$...
    > 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
    > "Chumley the Walrus" <> wrote in message
    > news:...
    > > 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

    >
    >
     
    Tom Gosselin, Aug 10, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. loreille
    Replies:
    1
    Views:
    4,509
    Ben Strackany
    Oct 28, 2004
  2. loreille
    Replies:
    0
    Views:
    464
    loreille
    Oct 28, 2004
  3. Replies:
    2
    Views:
    495
  4. race4space
    Replies:
    11
    Views:
    962
    Chris Marsh
    Jan 21, 2008
  5. techie
    Replies:
    3
    Views:
    170
    Curt_C [MVP]
    Sep 17, 2003
Loading...

Share This Page