Adding rows to the Datatable in c#

Question

I am working on a project and i want to add rows to the Datatable and what is happening is if i am fetching two rows from the database only one of the two is being added to the datatable but i want both to be added to the datatable i.e the datatable is not able to hold the data of first row,the data is being overwrite by data of second row. the code of the method is. this is my calling statement.

    DataTable dttable = new DataTable();
    dttable = gettable(dtgreater, dtcurrentdate);
    public DataTable gettable(List<DateTime> objct1, DateTime objct2)
    {

    DataTable data=null;
    for (int j = 0; j < dtgreater.Count; j++)
    {
        sql = "select library_issue.STUDENTCODE,library_issue.studentname,library_book.bookname,library_issue.issuedate,library_issue.returndate from library_issue             join library_book on library_book.book_id = library_issue.book_id where library_issue.returndate ='" + objct1[j].ToString("dd/MM/yyyy") + "'";
        ds = obj.openDataset(sql, Session["SCHOOLCODE"].ToString());
        Label1.Text = (ds.Tables[0].Rows.Count).ToString();
        data = new DataTable();
        data.Columns.Add("STUDENTCODE", typeof(int));
        data.Columns.Add("Studentname", typeof(string));
        data.Columns.Add("Bookname", typeof(string));
        data.Columns.Add("Issuedate", typeof(string));
        data.Columns.Add("Returndate", typeof(string));
        data.Columns.Add("NO of Days Exceeded", typeof(string));
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {

            TimeSpan ts = objct1[j] - objct2;
            Label1.Text = ts.ToString("dd");
            data.Rows.Add(ds.Tables[0].Rows[i]["STUDENTCODE"], ds.Tables[0].Rows[i]["studentname"], ds.Tables[0].Rows[i]["bookname"], ds.Tables[0].Rows[i]["issuedate"], ds.Tables[0].Rows[i]["returndate"], ts.ToString("dd"));

        }

    }

     return data;
}

UPDATE:

   public DataTable gettable(List<DateTime> objct1, DateTime objct2)
    {

        DataTable data = new DataTable();

        data.Columns.Add("STUDENTCODE", typeof(int));
        data.Columns.Add("Studentname", typeof(string));
        data.Columns.Add("Bookname", typeof(string));
        data.Columns.Add("Issuedate", typeof(string));
        data.Columns.Add("Returndate", typeof(string));
        data.Columns.Add("NO of Days Exceeded", typeof(string));
    for (int j = 0; j < dtgreater.Count; j++)
    {
        sql = "select library_issue.STUDENTCODE,library_issue.studentname,library_book.bookname,library_issue.issuedate,library_issue.returndate from library_issue             join library_book on library_book.book_id = library_issue.book_id where library_issue.returndate ='" + objct1[j].ToString("dd/MM/yyyy") + "'";
        ds = obj.openDataset(sql, Session["SCHOOLCODE"].ToString());
        Label1.Text = (ds.Tables[0].Rows.Count).ToString();

        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {

            TimeSpan ts = objct1[j] - objct2;
            Label1.Text = ts.ToString("dd");
            data.Rows.Add(ds.Tables[0].Rows[i]["STUDENTCODE"], ds.Tables[0].Rows[i]["studentname"], ds.Tables[0].Rows[i]["bookname"], ds.Tables[0].Rows[i]["issuedate"], ds.Tables[0].Rows[i]["returndate"], ts.ToString("dd"));

        }

    }

     return data;
}

Discription: As I was initializing the datatable inside the for loop which was causing the creation of new instance of datatable each time . As a result of which i was getting only one rows each time.

Guffa · Accepted Answer · 2013-04-17 12:12:16Z

4

You are creating the data table inside the loop, so in the second iteration it will discard the first data table with the first item and create a new empty one for the second item.

Create the data table and add the columns to it before the loop:

DataTable dttable = new DataTable();
dttable = gettable(dtgreater, dtcurrentdate);

public DataTable gettable(List<DateTime> objct1, DateTime objct2)
{

  DataTable data = new DataTable();
  data.Columns.Add("STUDENTCODE", typeof(int));
  data.Columns.Add("Studentname", typeof(string));
  data.Columns.Add("Bookname", typeof(string));
  data.Columns.Add("Issuedate", typeof(string));
  data.Columns.Add("Returndate", typeof(string));
  data.Columns.Add("NO of Days Exceeded", typeof(string));
  for (int j = 0; j < dtgreater.Count; j++) {
    sql = "select library_issue.STUDENTCODE,library_issue.studentname,library_book.bookname,library_issue.issuedate,library_issue.returndate from library_issue             join library_book on library_book.book_id = library_issue.book_id where library_issue.returndate ='" + objct1[j].ToString("dd/MM/yyyy") + "'";
    ds = obj.openDataset(sql, Session["SCHOOLCODE"].ToString());
    Label1.Text = (ds.Tables[0].Rows.Count).ToString();
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++) {

        TimeSpan ts = objct1[j] - objct2;
        Label1.Text = ts.ToString("dd");
        data.Rows.Add(ds.Tables[0].Rows[i]["STUDENTCODE"], ds.Tables[0].Rows[i]["studentname"], ds.Tables[0].Rows[i]["bookname"], ds.Tables[0].Rows[i]["issuedate"], ds.Tables[0].Rows[i]["returndate"], ts.ToString("dd"));

    }

  }

  return data;
}

answered Apr 17, 2013 at 12:12

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashish Over a year ago

i updated my code according to ur suggestion but i got an error as A column named 'STUDENTCODE' already belongs to this DataTable.

Guffa Over a year ago

@Ashish: Where do you get that error? There is only one place where the column is added, and that is immediately after a new, empty data table is created.

Nick N. · Accepted Answer · 2016-11-28 14:22:59Z

1

Place the table creation outside of the loop.

DataTable data = new DataTable();

It is probably better to use a foreach like this:

For example:

foreach (var dr in ds.Tables.First().Rows)
{
   string studentCode = dr["STUDENTCODE"].ToString();
}

edited Nov 28, 2016 at 14:22

answered Apr 17, 2013 at 12:15

Nick N.

13.7k9 gold badges62 silver badges77 bronze badges

Comments

Pandian · Accepted Answer · 2013-04-17 12:16:31Z

0

Remove the line : data = new DataTable();

Create the Datatable outside the For loop like below...

    DataTable data = new DataTable();
    data.Columns.Add("STUDENTCODE", typeof(int));
    data.Columns.Add("Studentname", typeof(string));
    data.Columns.Add("Bookname", typeof(string));
    data.Columns.Add("Issuedate", typeof(string));
    data.Columns.Add("Returndate", typeof(string));
    data.Columns.Add("NO of Days Exceeded", typeof(string))
    for (int j = 0; j < dtgreater.Count; j++)
     {

it will solve your problem...

answered Apr 17, 2013 at 12:16

Pandian

9,1562 gold badges26 silver badges34 bronze badges

2 Comments

Ashish Over a year ago

i updated my code according to ur suggestion but i got an error as A column named 'STUDENTCODE' already belongs to this DataTable.

Pandian Over a year ago

@Ashish: In the run time at which line you got this error...? Can you point that line

Rohit · Accepted Answer · 2013-04-17 12:21:42Z

What you can do here is

    DataTable dttable = new DataTable();
    dttable = gettable(dtgreater, dtcurrentdate);
    public DataTable gettable(List<DateTime> objct1, DateTime objct2)
    {

    DataTable data=null;
    for (int j = 0; j < dtgreater.Count; j++)
    {
        sql = "select library_issue.STUDENTCODE,library_issue.studentname,library_book.bookname,library_issue.issuedate,library_issue.returndate from library_issue             join library_book on library_book.book_id = library_issue.book_id where library_issue.returndate ='" + objct1[j].ToString("dd/MM/yyyy") + "'";
        ds = obj.openDataset(sql, Session["SCHOOLCODE"].ToString());
        Label1.Text = (ds.Tables[0].Rows.Count).ToString();
       if(data.Columns.count==0)
        {
        data = new DataTable();
        data.Columns.Add("STUDENTCODE", typeof(int));
        data.Columns.Add("Studentname", typeof(string));
        data.Columns.Add("Bookname", typeof(string));
        data.Columns.Add("Issuedate", typeof(string));
        data.Columns.Add("Returndate", typeof(string));
        data.Columns.Add("NO of Days Exceeded", typeof(string));
        }

        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {

            TimeSpan ts = objct1[j] - objct2;
            Label1.Text = ts.ToString("dd");
            data.Rows.Add(ds.Tables[0].Rows[i]["STUDENTCODE"], ds.Tables[0].Rows[i]["studentname"], ds.Tables[0].Rows[i]["bookname"], ds.Tables[0].Rows[i]["issuedate"], ds.Tables[0].Rows[i]["returndate"], ts.ToString("dd"));

        }

    }

     return data;
}

I guess this will work for you

oen · Accepted Answer · 2015-03-26 15:02:07Z

0

DataTable dttable = new DataTable();
dttable = gettable(dtgreater, dtcurrentdate);
public DataTable gettable(List<DateTime> objct1, DateTime objct2)
{
    DataTable data=null;
    sql = "select library_issue.STUDENTCODE,library_issue.studentname,library_book.bookname,library_issue.issuedate,library_issue.returndate from library_issue             join library_book on library_book.book_id = library_issue.book_id where library_issue.returndate ='" + objct1[j].ToString("dd/MM/yyyy") + "'";
    ds = obj.openDataset(sql, Session["SCHOOLCODE"].ToString());
    Label1.Text = (ds.Tables[0].Rows.Count).ToString();


for (int j = 0; j < dtgreater.Count; j++)
{
   if(data.Columns.count==0)
    {
    data = new DataTable();
    data.Columns.Add("STUDENTCODE", typeof(int));
    data.Columns.Add("Studentname", typeof(string));
    data.Columns.Add("Bookname", typeof(string));
    data.Columns.Add("Issuedate", typeof(string));
    data.Columns.Add("Returndate", typeof(string));
    data.Columns.Add("NO of Days Exceeded", typeof(string));
    }

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {

        TimeSpan ts = objct1[j] - objct2;
        Label1.Text = ts.ToString("dd");
        data.Rows.Add(ds.Tables[0].Rows[i]["STUDENTCODE"], ds.Tables[0].Rows[i]["studentname"], ds.Tables[0].Rows[i]["bookname"], ds.Tables[0].Rows[i]["issuedate"], ds.Tables[0].Rows[i]["returndate"], ts.ToString("dd"));

}
 return data;
}

edited Mar 26, 2015 at 15:02

answered Mar 26, 2015 at 14:53

oen

11 bronze badge

1 Comment

Emissary Over a year ago

A short description of what you've done and why will go a long way - nobody likes playing "spot the difference".

Unknown · Accepted Answer · 2017-06-20 19:04:14Z

0

Var.GRV.Columns.Add("NAME COLUMN");
            Var.GRV.Columns.Add("NAME COLUMN");
            Var.GRV.Columns.Add("*");
            Var.GRV.Columns.Add("*");
            Var.GRV.Columns.Add("*");

answered Jun 20, 2017 at 19:04

Unknown

1

Collectives™ on Stack Overflow

Adding rows to the Datatable in c#

6 Answers 6

2 Comments

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related