Re: :o) ADO.NET - Deleting is no longer a problem

[Pino Carafa]

I see what you mean.... Had to change my table so LockItem was a primary key before I could get this to work:

Private Function DABDeleteItem(ByVal strItem As String) As Boolean

Dim objDSC As ADO.ADODataSetCommand
Dim objDS As Data.DataSet
Dim objRow As Data.DataRow

objDSC = New ADO.ADODataSetCommand()

objDSC.SelectCommand = New ADO.ADOCommand("Select * From LockTable Where LockItem = '" & strItem & "'", mobjConn)

objDS = New Data.DataSet()

objDS.Tables.Add("LockTable")
objDSC.FillDataSet(objDS, "LockTable")
For Each objRow In objDS.Tables.Item("LockTable").Rows
objRow.Delete()
Next
objDSC.Update(objDS, "LockTable")

Return True
End Function

But now it does! Cheers!
"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.

:o(