problems importing excel workbook and displaying in dynamic datagrid

[optimizeit]

What I am attempting to do is import an Excel Workbook and display the
worksheets in a datagrid dynamically.

I am very close to getting this to work. I have to this point
successfully imported a single worksheet into a dataset. I successfully
built a dynamic datagrid. And I did successfully bind it to the
dataset. Then I added the datagrid control to a PlaceHolder. The first
worksheet displays beautifully.

The next step is to allow the user to move from one worksheet to the
next. I list the worksheets in a drop down list box. The user chooses
the second worksheet from the list.

I go through all of the same gyrations again. I load the worksheet into
the dataset, build the datagrid dynamically based upon the structure of
the second worksheet, and add the datagrid control to my PlaceHolder.

This is where the problem seems to arise.

It appears within QuickWatch that when I add the datagrid control to
the PlaceHolder the second time, although the underlying datasource
column info looks good, the datagrid column properties appear as they
were with the first worksheet.

This culminates in the following error message when I attempt to
DataBind() my datagrid:

"A field or property with the name <datagrid column name goes here> was
not found on the selected datasource."

I don't understand why it is doing this, since I am creating the
datagrid dynamically from scratch, DataGrid myDataGrid = new
DataGrid(), each time the aspx page is loaded.

I did theorize that the persistent nature of the datagrid properties
may have been caused by an enabled "View State". So I ensured that the
"View State" property of my PlaceHolder was set to false. However, this
did not correct the problem.

I also thought that I may need to explicitly remove the datagrid
control from PlaceHolder before adding it the second time. But it
appears in QuickWatch (as I would expect) that the PlaceHolder does not
have any controls attached to it until I invoke
PlaceHolder.Controls.Add(dgWorksheet).

Can anyone please help me? This is very puzzling and has moved beyond
tedious to possible showstopper. Thanks for your prompt reply(s).

My code is below.




Sincerely, -Van

_________________

dgWorksheet.Width=100;
dgWorksheet.UseInternalCSS=false;
dgWorksheet.DataSource=this.dvWorksheet;
dgWorksheet.AllowSorting=true;
dgWorksheet.AllowPaging=true;
dgWorksheet.BorderColor=Color.White;
dgWorksheet.BorderWidth=1;
dgWorksheet.BackColor=Color.White;
dgWorksheet.ItemStyle.CssClass="dgRowDark";
dgWorksheet.CellPadding=3;
dgWorksheet.CellSpacing=0;
dgWorksheet.BorderStyle=BorderStyle.Outset;
dgWorksheet.PageSize=13;
dgWorksheet.CssClass="dgTable";
dgWorksheet.EnableViewState=false;
dgWorksheet.DataKeyField=this.dsWorksheet.Tables[0].Columns[0].ColumnName;

dgWorksheet.PagerStyle.HorizontalAlign=HorizontalAlign.Right;
dgWorksheet.PagerStyle.CssClass="dgFooter";
dgWorksheet.PagerStyle.Mode=PagerMode.NumericPages;
dgWorksheet.AlternatingItemStyle.CssClass="dgRowLight";
dgWorksheet.FooterStyle.CssClass="dgFooter";
dgWorksheet.AutoGenerateColumns = false;

BoundColumn[] bca=new BoundColumn[100];

// Add New Columns to DataGrid
for(int j=0; j < this.dsWorksheet.Tables[0].Columns.Count; j++)
{
bca[j]=new BoundColumn();
bca[j].DataField=this.dsWorksheet.Tables[0].Columns[j].ColumnName;
bca[j].HeaderText=bca[j].DataField.Replace("_"," ");
bca[j].SortExpression=bca[j].DataField;
dgWorksheet.Columns.Add(bca[j]);
}
dgWorksheet.Visible=true;
lblRowCount.Font.Size=5;
lblRowCount.Font.Name="Verdana";
lblRowCount.Visible=true;

//Here is where the problem seems to arise.
//When I do a QuickWatch on dgWorksheet here, it appears with only
//datasource column info. All datagrid column info is not yet set.
//Those properties appear to be getting set upon databind the first
//time around, and everything is smooth.
//But when I go to the second worksheet, then the problem seems to
//Occur in this next statement.

// Add New Controls to WorksheetDataGrid

this.WorksheetDataGrid.Controls.Add(dgWorksheet);

//After executing the above statement the second time around,
//then the prior datagrid column properties seem to reappear,
//and I can't figure out why.
//Any help would be greatly appreciated.

this.WorksheetDataGrid.Controls.Add(lblRowCount);
this.WorksheetDataGrid.Visible=true;

this.DataBind();