Updating a datatable in c#

Question

I have a datatable storing info about a student classroom. My table looks like this:

Student ID     Grade     Absence Count
00001           85             0
00002           95             7
00002           70             5
00003           35             1

Dont ask me why there are two id's that are the same... its just the way it is. Now i want to update the absence count for the 00002 id that has absence count of 7. At the same time, i want to delete the 00002 entry that doesnt have the absence count of 7 (in this case the one with count 5). Now i know how to query the table with a select statement and update the 00002 id student with count 7. How can i, at the same time, delete the other entry for the 00002 student? This is my code:

foreach(oldCount in absenceCount)
{
    DataRow[] dr = dt.Select("Student ID='" + ID + "' AND Absence Count='" + oldCount);
    dr[0]["Absence Count"] = newCount;
}

So here how can i tell the program that if there is another student id whose absence count isnt in the absenceCount list, delete it from the table?

Thanks

Probably a duplicate: http://stackoverflow.com/questions/1591771/datatable-how-to-conditionally-delete-rows — skink, Jun 23 '11 at 18:53
You'll need to better define the rules surrounding why you want to delete the 2nd one and update the 1st. Is it positional? Because 7 > 5? because 95 > 70? user input? — Conrad Frix, Jun 23 '11 at 18:57

score 1 · Answer 1 · answered Jun 23 '11 at 18:43

1

You can write

dr[1].Delete();

answered Jun 23 '11 at 18:43

SLaks

868,454
176
1,908
1,964

No i cannot as the select only gets the rows that have the absence count i want to delete. In other words, the 00002 id row with count 5 will not be in the dr datarow – Greg Jun 23 '11 at 18:46
@Greg: You can run another `Select()` with a different condition. – SLaks Jun 23 '11 at 18:47
like what? how can i run a select to select a row that doesnt meet the contents of the oldCOunt list? – Greg Jun 23 '11 at 18:49
One would wonder.. what if there's 2 students with the same id (sigh) and the same number of absences... – Blindy Jun 23 '11 at 18:50
lol this is a test for something bigger, so dont worry about exception cases – Greg Jun 23 '11 at 18:51
@Greg: `Absence Count <> something` – SLaks Jun 23 '11 at 18:51
Im sorry but i dont understand that syntax? – Greg Jun 23 '11 at 18:51
@Greg: See the documentation http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx – SLaks Jun 23 '11 at 18:52
This might be a dumber question, but why not just mandate a absense count of 7? How do you "know" you want to delete the one where its != 7? and if you did know, why wouldn't you isolate accordingly? – Perplexed Jun 23 '11 at 18:52

ata · Answer 2 · 2011-06-23T18:58:43.937

0

I suppose you can select all the rows with same ID. Update the one you want to, and delete the rest of the rows. Something like

DataRow[] dr = dt.Select("Student ID='" + ID + "'");

and then updating the one that matches the Absence count, deleting the rest of them.

Regarding the comment: This is taken from Kenny's reply, but this is what i meant

foreach(DataRow row in dr)
{
  if (dr["Absence Count"] == absencCount)
      dr["Absence Count"] = newCount;
  else
      dr.Delete()
}

edited Jun 23 '11 at 18:58

answered Jun 23 '11 at 18:52

ata

8,853
8
42
68

But how do you get "the rest of them"? – Greg Jun 23 '11 at 18:53

kakridge · Answer 3 · 2011-06-23T19:07:43.990

0

foreach(oldCount in absenceCount)
{
    DataRow[] dr = dt.Select("Student ID='" + ID);
    bool updated = false;
    foreach(DataRow row in dr)
    {
      if (!updated && dr["Absence Count"] == absenceCount)
      {
          dr["Absence Count"] = newCount;
          updated = true;
      }
      else
      {
          dr.Delete()
      }
    }
}

edited Jun 23 '11 at 19:07

answered Jun 23 '11 at 18:55

kakridge

2,153
1
17
27

score 0 · Accepted Answer · answered Jun 23 '11 at 18:55

0

You could do the same select but with the opposite condition on the oldcount, like so

foreach(oldCount in absenceCount)
{
  DataRow[] dr = dt.Select("Student ID='" + ID + "' AND Absence Count != '" + oldCount);
  dr[0].Delete();
}

This will get you that student id but where the absence count is not equal to the old count.

You obviously still have a problem where you have, as Blindy suggested, 2 rows with the same student id and the same absence count.

answered Jun 23 '11 at 18:55

hermiod

1,158
4
16
27

oops just tried that, apparently ! is not an accepted query syntax – Greg Jun 23 '11 at 19:00
Is the repeated row the same student (with the same id) appearing twice, or is it two different students with the same Id each appearing once. – hermiod Jun 23 '11 at 20:39

Updating a datatable in c#

4 Answers4