delete a record

  • Thread starter Thread starter mark kurten
  • Start date Start date
M

mark kurten

I've posted this in the aspnet group, but i think this is the more
appropriate group.

I have the code below which receives an error. The code gets invoked when a
button is clicked.

Dim row As DataRow
Try
row = ds.Tables("TestTable").Select("emp_id=" & txtEmpID.Value )(0)
row.Delete()
myAdapter.Update(ds)
ds.AcceptChanges()
Catch err As Exception
lblError.Text = err.ToString()
End Try

Error is "System.InvalidOperationException: Update requires a valid
DeleteCommand when
passed DataRow collection with deleted rows."


any help is appreciated..
 
You forgot to code the DeleteCommand contained in the DataAdapter. Unlike
ADO classic, ADO.NET does not construct the action queries needed to make
changes to the db--you have to build these yourself.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Mark,

Although Bill does not like it, because it is not the best solution, try
this first and test if very good, because Bill is always saying that it can
give big errors and when Bill says that I believe him.
Dim row As DataRow
Try
row = ds.Tables("TestTable").Select("emp_id=" & txtEmpID.Value )(0)
row.Delete()
Click to expand...

dim cmb as new XXXcommandbuilder(myAdapter)

(I can not see if XXX = Oledb.Oledb or SQLclient.SQL)
 
that's what i thought, but wasn't sure..so when i build the delete command
(i don't have to build a selectcommand also to retrieve the records first,
right?) i have to basically put a standard delete statement together and use
a parameter so i can pass it the "txtEmpID.Value" just like when i select it
from the dataset as i did below.

thanks.
 
thanks you both very much..


Cor said:
Hi Mark,

Although Bill does not like it, because it is not the best solution, try
this first and test if very good, because Bill is always saying that it can
give big errors and when Bill says that I believe him.


dim cmb as new XXXcommandbuilder(myAdapter)

(I can not see if XXX = Oledb.Oledb or SQLclient.SQL)
Click to expand...
 
Cor is right. I don't like the CommandBuilder. However, as a learning tool,
use the DataAdapter Configuration Wizard to create a DataAdapter. This uses
the CB behind the scenes. Look at the generated code. It will give you an
idea about what to do.
You can also read about how to do this in my article on the CommandBuilder.
See http://www.betav.com/msdn_magazine.htm

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
thanks for the help... One last question though..

So, if i do deletes, inserts and updates on my dataset rows (like i'm doing
in my example)

row = ds.Tables("TestTable").Select("emp_id=" & txtEmpID.Value )(0)
row.Delete()

I have to always build corresponding commands (insertcommand, updatecommand
or deletecommand) before the dataadapter will actually update the database.

Correct? If so, I understand.
 
Right. Think of the DataSet as a temporary copy of a rowset (hopefully not
an entire database table) in memory. You make changes to this object and use
the Update method to post the new rows, changes and deleted rows to the
database. When Update executes, it walks the DataTables and runs either the
UpdateCommand, InsertCommand or DeleteCommand as appropriate. It does so one
row at a time and expects a "rows affected" value of 1 (only 1). If this
returns 1, then it goes to the next row in the DataTable. If it fails, it
stops unless you program the DataAdapter to continue after a concurrency
failure.

As I said, I discuss this in depth in my book.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
one final question then..thank you very much for the help..

lets say i have a table with firstname and lastname as columns.
if i wanted to delete all the firstnames with "mark" would it be better not
to use datasets then and use the
executenonquery method? I'm assuming I should only use datasets when I need
to manipulate the data and then do an update.
Another example, if i want to insert one new record into a table. I should
probably use the executenonquery method directly and not mess with datasets.

also, what book are you referencing?

thanks.
 
Nope. If you can create a Delete statement that precludes the need to move
the rowset to the client, then do it. In this case a
DELETE myTable WHERE FirstName = 'mark'
with an ExecuteNonQuery would be best (assuming you hadn't already created
the DataSet)

The "book" is described on my website "ADO.NET and ADO Examples Best
Practices For VB.NET Programmers" (and for C# Programmers).


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
thank you for all your help...

William (Bill) Vaughn said:
Nope. If you can create a Delete statement that precludes the need to move
the rowset to the client, then do it. In this case a
DELETE myTable WHERE FirstName = 'mark'
with an ExecuteNonQuery would be best (assuming you hadn't already created
the DataSet)

The "book" is described on my website "ADO.NET and ADO Examples Best
Practices For VB.NET Programmers" (and for C# Programmers).


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Click to expand...
 
Back
Top