Deleting multiuple rows from a database...

  • Thread starter Thread starter Brad Pears
  • Start date Start date
B

Brad Pears

I am working on a new vb.net 2005 project using SQL server 2000 as the
backend db.

I have a listview where control I want a user to be able to select either
just one or multiple rows in the listview and perform an action on them. If
the user chooses to delete these rows I wonder what the best way to handle
the deletion of multiple rows is...

Right now I have a stored procedure for deleteing jsut opne row. i.e delete
from {tablename} where field1 = Parm1". However, if the user selects say 10
rows, should I loop through the lsitview items and call this SP each time,
OR can I somehow batch them so that I can run the stored procedure just
once - which would then delete all selected rows using a clause similiar to
the following.... "delete from {tablename} where field1 in (select field1
from{tempdeleteditemstable})"

Any ideas if this is the best way to do it - or is simply looping through
the listview and calling the stored proc to do a single row delete each time
ok? They would never be deleting that many items at once - but definately
there may be times when multiple deletes will be required...

Thanks, Brad
 
Brad,

The best way is to use a Data related control like the DataGridView or the
DataGrid (eventualy with no row and column headers) and for sure not the
listview which is more intended for things as showing things like files.

Cor
 
Brad said:
I am working on a new vb.net 2005 project using SQL server 2000 as the
backend db.

I have a listview where control I want a user to be able to select
either just one or multiple rows in the listview and perform an
action on them. If the user chooses to delete these rows I wonder
what the best way to handle the deletion of multiple rows is...

Right now I have a stored procedure for deleteing jsut opne row. i.e
delete from {tablename} where field1 = Parm1". However, if the user
selects say 10 rows, should I loop through the lsitview items and
call this SP each time, OR can I somehow batch them so that I can run
the stored procedure just once - which would then delete all selected
rows using a clause similiar to the following.... "delete from
{tablename} where field1 in (select field1
from{tempdeleteditemstable})"
Any ideas if this is the best way to do it - or is simply looping
through the listview and calling the stored proc to do a single row
delete each time ok? They would never be deleting that many items at
once - but definately there may be times when multiple deletes will
be required...
Click to expand...

Doing them with tempdeleteitemstable would be a little excessive, since you
would have to do 10 inserts to fill the temp table first....

You could do "delete from tablename where field1 in (123,127,129)", that is pass
the ids literally as a string, but then you get no stored procedure or use of
parameters. Maybe you can pass a parameter array?

Unless the number is large, I would just call the SP 10 times with the ids. It
will cache up and execute pretty fast. Maybe put a transaction around it too.
 
Great! Thanks for your help!!

Brad
Steve Gerrard said:
Doing them with tempdeleteitemstable would be a little excessive, since
you would have to do 10 inserts to fill the temp table first....

You could do "delete from tablename where field1 in (123,127,129)", that
is pass the ids literally as a string, but then you get no stored
procedure or use of parameters. Maybe you can pass a parameter array?

Unless the number is large, I would just call the SP 10 times with the
ids. It will cache up and execute pretty fast. Maybe put a transaction
around it too.
Click to expand...
 
Never been a fan of the datagrid at all- just doesn't give you the proper
control I have found. It's always seemed clumsy to me and hard to really do
what you want with it...

I have not tried the datagridview. Maybe I should take a look at it...

Thanks, Brad
 
Back
Top