DataTable and it's Gridview - A Column Order Question RSS

• • Reply
  

  redline122

  0 Points

  3 Posts

  DataTable and it's Gridview - A Column Order Question

  Oct 12, 2010 06:16 PM|LINK

  Well, I’ve been developing with gridviews for quite some time now.  Just recently I came across a situation that has me stumped.  Here’s how the gridview has been working for me.

   

  1.      A stored procedure from the database is called (three parameters are passed (reporttype, name, location)

  2.     The data is retrieved using the SqlDataAdapter to fill a Datatable

  3.     The gridview DOES NOT AUTOGENERATE COLUMNS

  4.     Columns are created programmatically as it reads the DataTable results (headertext, datafield, and dataformatstring are set at runtime)

  5.     The Gridview triggers the databind event – and voila it works.

   

  My dilemma is this:  the stored procedure (that I haven’t developed) returns a various set of columns and column order.  I have one report type that does the following:

   

  ReportType 1:  column orders returned in the following order when running directly in SQL

              col B, col C, col D, col E, col A

   

  when I run the report through my gridview, the column order is as follows:

              col A, col B, col C, col D, col E

   

  I see that the columns are being filled into the datatable (in codebehind) in the following order:  col A, col B, col C, col D, col E

   

  >>  My question is why would the datatable be filling the columns in that order, when the stored procedure returns the table back with:  col B, col C, col D, col E, col A

   

  Your thoughts would be appreciated!

  gridview DataTable sqldataadapter column order

  • Moved by MetalAsp.Net on Oct 12, 2010 06:50 PM
• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: DataTable and it's Gridview - A Column Order Question

  Oct 12, 2010 07:19 PM|LINK

  It depends on how you are adding the column names. DataTable Column order will be same as the column order in the query.

  SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);
  SqlDataAdapter adap = new SqlDataAdapter("Select [Col Z], [Col A] from TestTable", conn);
  DataSet ds = new DataSet();
  adap.Fill(ds);
  GridView GridView1 = new GridView();
  GridView1.AutoGenerateColumns = false;
  foreach (DataColumn col in ds.Tables[0].Columns)
  {
      BoundField bf = new BoundField();
      bf.DataField = col.ColumnName;
      bf.HeaderText = col.ColumnName;
      GridView1.Columns.Add(bf);
  }
  GridView1.DataSource = ds.Tables[0];
  GridView1.DataBind();
  Page.Form.Controls.Add(GridView1);

  Santhosh
  • Edited by sansan on Oct 12, 2010 07:20 PM
• • Reply
  

  redline122

  0 Points

  3 Posts

  Re: DataTable and it's Gridview - A Column Order Question

  Oct 12, 2010 08:12 PM|LINK

  Right after the datatable is filled, I checked it's column order and it's not the same as what is generated from the sproc.
  

  • Edited by redline122 on Oct 12, 2010 08:13 PM
• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: DataTable and it's Gridview - A Column Order Question

  Oct 12, 2010 08:29 PM|LINK

  Honestly, I have used that many times and never noticed any issues. Command or Stored Procedure, it returned same column order. I am not sure if there is any configuration that you have to check.

  Santhosh
• • Reply
  

  redline122

  0 Points

  3 Posts

  Re: DataTable and it's Gridview - A Column Order Question

  Oct 14, 2010 09:18 PM|LINK

  sansan,

  It was a configuration issue - I was pointing to a different instance of the database.  Talk about a rookie mistake!
  

  • Marked as answer by Wencui Qian - MSFT on Oct 20, 2010 06:55 AM