dataset/dataview + delete

[thesource]

Hello,

 

I have a probleme deleting rows from a dataset, now i looked on the internet to solve this problem but i can't find a solution.

 

the problem is

i have a form where i load a dataset, then i put this dataset in a dataview(for easy filtering). and then i show it to the users. Now i want to delete a item from the dataview/dataset(and with a update from the server). Now i use a inner join to fill the dataset(table) and the dataview,because i need it in the dataview, so i can't delete from here.

this is why i load a second table in it, which contains the data i want to delete(just a straight on select * from table)

 

now my idea was to select the primary key. and then look for the primary key in the dataset. and when i found the primary key i delete the row containing it.

 

but i tried a lot of coding doing this and nothing seems to work,

 

does anyone have a solutions for this

 

Many thanks in advance,

MZ

[Nerseus]

First, let me check on a few things.

 

Your select to fill the DataTable/DataView looks something like this:

SELECT t1.Col1, t1.Col2, t2.Col1, t2.Col2 FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Col1 = t2.Col1

 

or something like that - essentially two (or more) tables, joined together.

 

Now you want to delete from Table1 only?

 

You need to set the DeleteCommand of the DataAdapter to a statment that deletes from Table1. Something like:

DELETE FROM Table1 WHERE Col1 =...

 

Or whatever you need. If you're using the CommandBuilder, don't. Use it once to see the commands it builds then manually cut-n-paste the commands into the DataAdapter yourself. The CommandBuilder is good for simple updates only - anything even slightly complex and you're better off coding the commands yourself.

 

-Nerseus

[Mehyar]

Well MZ, you seem to got the correct idea , i don't know why it didnt work out.

 

Maybe this code will help....

'suppose your table name is dt1

'and the column in which the primary key is in is 0

Dim s as string = The primaryKey u want to delete

 

Dim i as integer

 

For i = 0 to dt1.rows.count - 1

If dt1.rows(i)(0) = s then

dt1.rows(i).delete

exit for

end if

Next

 

or simply select the row with the primary key you have

dt1.select("NameofColumn = ' & s)(0).delete()

 

hope this helps

Mehyar

[jfackler]

You can perform a Find on the dataview or a Select on a datatable and avoid the loop above, resetting the binding context and performing a delete followed by an update to persist the changes back to the database.

 

http://www.xtremedotnettalk.com/showthread.php?s=&threadid=72923&perpage=5&pagenumber=1

 

Jon