DataTable and it's Gridview - A Column Order Question

Well, Ive been developing with gridviews for quite some time now. Just recently I came across a situation that has me stumped. Heres 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 havent 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!

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

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

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

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.

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

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.

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

sansan,

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