CODEWITHSUNDEEP
c#asp.netmysqldatatabledatarow
Ashish
Ashish

Reputation: 107

Adding rows to the Datatable in c#

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.

Upvotes: 3

Views: 25455

Answers (6)

Unknown
Unknown

Reputation: 1

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

Upvotes: 0

Nick N.
Nick N.

Reputation: 13559

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();
}

Upvotes: 1

oen
oen

Reputation: 1

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;
}

Upvotes: 0

Rohit
Rohit

Reputation: 10226

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

Upvotes: 0

Pandian
Pandian

Reputation: 9126

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...

Upvotes: 0

Guffa
Guffa

Reputation: 700152

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;
}

Upvotes: 4

Related Questions