Data Table Selecting

[Sueffel]

Okay, I have the following scenerio:

Protected Sub DelRows()
Dim rw As DataRow
Dim rrw As DataRow
Dim DT as DataTable
Dim rws() as DataRow

For each rw in ds.Tables("Table1").Rows
rws = ds.Tables("Table2").Select("KeyRow = '" &
rw.Item(0).ToString & "'")
For Each rrw in rws
rrw.Delete()
Next
Next

End Sub

As you can see, I have 2 DataTables in my DataSet. I have to delete data
from Table2, and the key for that data is Item(0) in Table1. Now, I'm
thinking that this will delete the tables I selected out of Table2 from
Table2, but that's not what's working...
Help......

Sueffel

 

[Cor]

Hi Sueffel,

rws = ds.Tables("Table2").Select("KeyRow = '" &

Is "KeyRow" a real column name in table2?
If so test it once with a hard value in table2 on that KeyRow, that you
know, and debug it.

Cor

 

[Sueffel]

Hi Sueffel,


Is "KeyRow" a real column name in table2?
If so test it once with a hard value in table2 on that KeyRow, that you
know, and debug it.

Cor

KeyRow is just a name I threw out, the actual name of the column is
PaymentID, which is the PrimaryKey column. If I do this:
Dim T as String = rrw.Item(0).ToString, I get the value back. rrws return
sthe length correctly. I am now stumped on why it is not deleting. As a
"visual" thing, i bound a DataGrid to it and set the CaptionText to be the
count of rows for the table, and those don'tchange, but it does hit the
delete method. Hmmm again..

Sueffel

 

[Sueffel]

Hi Sueffel,


Is "KeyRow" a real column name in table2?
If so test it once with a hard value in table2 on that KeyRow, that you
know, and debug it.

Cor

As an added WTF, I stepped through the code and watched the rowstate, and it
did switch to Deleted, but I'm concerned that if isn't actually referencing
back to the base table as deleted.....

Sueffel

 

[Cor]

Hi Sueffel,

just a tempory mycounter as integer = ds.tables(x).rows.count
before it and after it, debug and you know.
(and the second one with another name)

As an added WTF, I stepped through the code and watched the rowstate, and it
did switch to Deleted, but I'm concerned that if isn't actually referencing
back to the base table as deleted.....

Cor

 

[Sueffel]

Hi Sueffel,

just a tempory mycounter as integer = ds.tables(x).rows.count
before it and after it, debug and you know.
(and the second one with another name) and
Cor

Will give that a shot and let ya know.

Sueffel

 

[Sueffel]

Hi Sueffel,

just a tempory mycounter as integer = ds.tables(x).rows.count
before it and after it, debug and you know.
(and the second one with another name) and
Cor

Now somethings shedding light! I just did that, and what would ya know, the
RowCount did not change! Urrggghhhhhh

Sueffel

 

[Sueffel]

Now somethings shedding light! I just did that, and what would ya know, the
RowCount did not change! Urrggghhhhhh

Sueffel

Well shister! forgot ds.AcceptChanges()! LOL

All is well now LMAO
Sueffel

 

[Jay B. Harlow [MVP - Outlook]]

Sueffel,

Well shister! forgot ds.AcceptChanges()! LOL

If you are not updating the backing database itself (DataAdapter.Update) you
can call DataRow.Remove instead of DataRow.Delete, DataRow.Remove will
remove the row from the DataTable immediately!

Note DataAdapter.Update should be calling AcceptChanges for you...

Hope this helps
Jay

 

[Cor]

Hi Sueffel,

Are you sure that did fix it?

I think it is something else this should not fix the problem in my opinion
(with the part of program I saw from you).

Well shister! forgot ds.AcceptChanges()! LOL

Cor

 

[Sueffel]

Hi Sueffel,

Are you sure that did fix it?

I think it is something else this should not fix the problem in my opinion
(with the part of program I saw from you).


Cor

I haven't called the update yet. I know I can just display the deleted
rows to the user, but I'm opting to delete the rows, accept the changes to
the underlying dataset, then bind a datagrid to the dataset, and let the
user decide if they did it correctly, then commit it to the database. I
probably will take the rows marked deleted and dump them into another table
and have the user check that, withuot caling AcceptChanges, so then the user
can just cancel either some or all of the deleted rows and not have to
re-read the dataadapter from the database.
I want to give the user every chance to make sure they are deleting or
changing the right thing before making it perminant. Some of my users take
the notion of "Human Error" to a level that I haven't seen from 4th graders,
so I really need to be on my toes for stuff. So, that's why I have to use
"wierd methods" to get things done.

Thanks again,
Sueffel

 

[Cor]

Hi Sueffel,

Maybe I am wrong but I think you are interpretting accept.changes in the
wrong way.
(Look at the note from Jay B also for that).

You can use acceptchanges to fix the changes in the dataset (better fix the
rowstate) as update done.
The dataadapter does that also for you if you are writing something as
xxxdataadapter.update(mydataset)

But suppose you would not want to do that and you take another method to
update the dataset. (There can be reasons for that). Than you can use
dataset.acceptchanges to tell that you have done all the updates to the
database.

If you do not use updates to the database at all, you can also use as Jay B.
stated
dataset.xxxx.remove

I hope this makes it more clear

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Sueffel,
As Cor pointed out, I too have concerns about you calling AcceptChanges,
then thinking you can update the DataBase. Calling DataSet.GetChanges to
show the to the user may be a better choice. GetChanges is the "easy" way to
"take the rows marked deleted and dump them into another table".

If you do not have it, I would strongly recommend you purchase and read,
thoroughly! David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS
Press. It is both a good tutorial on ADO.NET as well as a good desk
reference once you know ADO.NET.

Sceppa's book provides lots of details on DataSets, DataTables, deleting,
presenting changes to user, along with updating the database.

Hope this helps
Jay

 

[Sueffel]

I can see where my confusion is coming in. I'm looking at my book
again, and rereading the stuff on "Managing DataSet Changes". According to
this example they provide:
dsCustomers = dsCustomers.GetChanges(DataRowState.Deleted)
I concluded that I had to do this before using AcceptChanges. So, i
need to rethink the process to do the following:

1) Let users delete rows from DataSet.Table using DataRow.Delete
2) Provide a temprorary dataset, and return the GetChanges method there,
showing it through DataGrid
3) If user is liking it, then AcceptChanges, else RejectChanges
4) Tell the DataAdapter for this perticular Table in the DataSet to
Update(), committing these changes back tot he database.
5) Stop thinking in classic ADO methodology!!!!!!!!!
I see a concern in the code above, is this going to remove the rest of the
dataset and leave only rows that are deleted? I think that's what is
confusing me and tripping me up....

More food for my thought!

Thanks again,
Sueffel

 

[Cor]

Hi Sueffel

No

You are staying with the accept changes. Forget it a while.

Here row by row comments on your sentences.

1) Let users delete rows from DataSet.Table using DataRow.Delete

No problem

2) Provide a temprorary dataset, and return the GetChanges method there,
showing it through DataGrid

No problem

3) If user is liking it, then AcceptChanges, else RejectChanges

A big problem, has to be "If the user is not liking it Reject Changes"

4) Tell the DataAdapter for this perticular Table in the DataSet to
Update(), committing these changes back tot he database.

If you do not that acceptChanges, the dataAdapter does the accept changes
for you if you have used full dataset in the dataadapter(and only then).

If you have done acceptchanges before there is no need for an update,
because there is nothing to update anymore.

5) Stop thinking in classic ADO methodology!!!!!!!!!

Yes

Cor