Re: :o( ADO.NET - Deleting is still a problem


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Re: :o( ADO.NET - Deleting is still a problem

  1. #1
    Pino Carafa Guest

    Re: :o( ADO.NET - Deleting is still a problem

    What I still haven't worked out is how to delete records properly.

    OK, I can delete records quite easily with an SQL statement:

    Private Function DABDeleteItem(ByVal strItem As String) As Boolean
    Dim objCommand As ADO.ADOCommand

    objCommand = New ADO.ADOCommand("Delete From LockTable Where LockItem = '" & strItem & "'", mobjConn)
    objCommand.ExecuteNonQuery()

    'Haven't implemented this bit yet
    Return True
    End Function

    But, what if you have a situation where you first retrieve a data set, show details to the user, they make a random selection from the set, and you have to go and delete those records. Say, just to be evil, that there is no unique key (yuck) ....

    Ideally I'd like to be able to do something like this:

    - Create a DataSet, based on a Select statement
    - Select a random selection of rows from this DataSet
    - Update!

    But I can't work out how....
    "Miha Markic" <miham@spin.si> wrote in message news:3a377b6f@news.devx.com...
    I don't have an example, but I suppose that this is done by DataSetCommand (execution) & DataSet (rows).

    Miha
    "Pino Carafa" <deathtospammers@eircom.net> wrote in message news:3a375183@news.devx.com...
    Can someone give me a simple example on how to add a new row to a table in a database using ADO.NET?
    And how to change an existing one?
    How to delete one?

    The documentation is making my head hurt.

    (


  2. #2
    Daniel Pratt Guest

    Re: :o( ADO.NET - Deleting is still a problem

    Hi Pino,

    In one of your posts you have a line that looks like this:

    objDS.Tables("LockTable").Rows.Add(objRow)

    Working from that, you could delete a row like this:

    objDS.Tables("LockTable").Rows(3).Delete 'mark 3rd row for delete

    Any rows that you mark for delete will get deleted when you execute

    objDSC.Update(objDS, "LockTable")

    It is important that you call the DataRow.Delete method rather than remove the row from the DataTable with Rows.Remove(). The deleted row actually has to be in the DataTable for the ADODataSetCommand to "see" it.

    The ADODataSetCommand has four child ADOCommand objects that it uses to populate a DataSet and update the changes back to the DBMS. These are SelectCommand, UpdateCommand, InsertCommand and DeleteCommand. Since you've only specified the SelectCommand, the other three commands get auto-generated when you call the Update method. Depending on the row's status (updated, new or deleted), one of the other three commands get's executed for every changed row in the DataSet. The ADODataSetCommand fills in that command's parameters based on the data in the changed row.

    There are certain circumstances under which the other three commands can't be auto-generated and there are also times when you would want to specify your own update commands (e.g. if you wanted to use stored procedures). You can do this by setting up the command objects of the ADODataSetCommand yourself. You might take a look at the other three commands that get auto-generated to get an idea of how the ADODataSetCommand "handles" updating the DBMS.

    Hope that helps.

    Regards,
    Dan
    "Pino Carafa" <deathtospammers@eircom.net> wrote in message news:3a378a7a@news.devx.com...
    What I still haven't worked out is how to delete records properly.

    OK, I can delete records quite easily with an SQL statement:

    Private Function DABDeleteItem(ByVal strItem As String) As Boolean
    Dim objCommand As ADO.ADOCommand

    objCommand = New ADO.ADOCommand("Delete From LockTable Where LockItem = '" & strItem & "'", mobjConn)
    objCommand.ExecuteNonQuery()

    'Haven't implemented this bit yet
    Return True
    End Function

    But, what if you have a situation where you first retrieve a data set, show details to the user, they make a random selection from the set, and you have to go and delete those records. Say, just to be evil, that there is no unique key (yuck) ....

    Ideally I'd like to be able to do something like this:

    - Create a DataSet, based on a Select statement
    - Select a random selection of rows from this DataSet
    - Update!

    But I can't work out how....
    "Miha Markic" <miham@spin.si> wrote in message news:3a377b6f@news.devx.com...
    I don't have an example, but I suppose that this is done by DataSetCommand (execution) & DataSet (rows).

    Miha
    "Pino Carafa" <deathtospammers@eircom.net> wrote in message news:3a375183@news.devx.com...
    Can someone give me a simple example on how to add a new row to a table in a database using ADO.NET?
    And how to change an existing one?
    How to delete one?

    The documentation is making my head hurt.

    (


  3. #3
    Pino Carafa Guest

    Re: :o( ADO.NET - Deleting is still a problem

    It sure does help!

    That was *exactly* what I was doing: I was using Remove, not Delete.

    Thanks
    "Daniel Pratt" <dprREMOVETHISatt71@hotmail.com> wrote in message news:3a3791f3@news.devx.com...
    Hi Pino,

    In one of your posts you have a line that looks like this:

    objDS.Tables("LockTable").Rows.Add(objRow)

    Working from that, you could delete a row like this:

    objDS.Tables("LockTable").Rows(3).Delete 'mark 3rd row for delete

    Any rows that you mark for delete will get deleted when you execute

    objDSC.Update(objDS, "LockTable")

    It is important that you call the DataRow.Delete method rather than remove the row from the DataTable with Rows.Remove(). The deleted row actually has to be in the DataTable for the ADODataSetCommand to "see" it.

    The ADODataSetCommand has four child ADOCommand objects that it uses to populate a DataSet and update the changes back to the DBMS. These are SelectCommand, UpdateCommand, InsertCommand and DeleteCommand. Since you've only specified the SelectCommand, the other three commands get auto-generated when you call the Update method. Depending on the row's status (updated, new or deleted), one of the other three commands get's executed for every changed row in the DataSet. The ADODataSetCommand fills in that command's parameters based on the data in the changed row.

    There are certain circumstances under which the other three commands can't be auto-generated and there are also times when you would want to specify your own update commands (e.g. if you wanted to use stored procedures). You can do this by setting up the command objects of the ADODataSetCommand yourself. You might take a look at the other three commands that get auto-generated to get an idea of how the ADODataSetCommand "handles" updating the DBMS.

    Hope that helps.

    Regards,
    Dan
    "Pino Carafa" <deathtospammers@eircom.net> wrote in message news:3a378a7a@news.devx.com...
    What I still haven't worked out is how to delete records properly.

    OK, I can delete records quite easily with an SQL statement:

    Private Function DABDeleteItem(ByVal strItem As String) As Boolean
    Dim objCommand As ADO.ADOCommand

    objCommand = New ADO.ADOCommand("Delete From LockTable Where LockItem = '" & strItem & "'", mobjConn)
    objCommand.ExecuteNonQuery()

    'Haven't implemented this bit yet
    Return True
    End Function

    But, what if you have a situation where you first retrieve a data set, show details to the user, they make a random selection from the set, and you have to go and delete those records. Say, just to be evil, that there is no unique key (yuck) ....

    Ideally I'd like to be able to do something like this:

    - Create a DataSet, based on a Select statement
    - Select a random selection of rows from this DataSet
    - Update!

    But I can't work out how....
    "Miha Markic" <miham@spin.si> wrote in message news:3a377b6f@news.devx.com...
    I don't have an example, but I suppose that this is done by DataSetCommand (execution) & DataSet (rows).

    Miha
    "Pino Carafa" <deathtospammers@eircom.net> wrote in message news:3a375183@news.devx.com...
    Can someone give me a simple example on how to add a new row to a table in a database using ADO.NET?
    And how to change an existing one?
    How to delete one?

    The documentation is making my head hurt.

    (


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center