Dynamically Populate DataGrid Using SqlDataReader

[rn5a]

A SqlDataReader is populated with the records from a SQL Server 2005 DB
table. The records retrieved depends upon 2 conditions (the conditions
depend on what a user selects in an ASPX page). If condition1 is true,
then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.

Moreover the 2 tables do not have the same no. of columns - even the
column names are different. This means that the DataGrid has to be
filled dynamically. Had this not been the case, then using the
DataField property of the DataGrid, I could have populated the DataGrid
using

<asp:DataGrid ID="dgAddress" runat="server">
<Columns>
<asp:BoundColumn DataField="Address" HeaderText="ADDRESS"/>
<asp:BoundColumn DataField="City" HeaderText="CITY"/>
<asp:BoundColumn DataField="State" HeaderText="STATE"/>
<asp:BoundColumn DataField="Country" HeaderText="COUNTRY"/>
<asp:BoundColumn DataField="Zip" HeaderText="ZIP"/>
</Columns>
</asp:DataGrid>

But since I need to populate the DataGrid dynamically, the above
approach won't be feasible. So I tried to do it in the Page_Load sub
but I don't find any property named 'DataField' when I browsed through
the different properties of the DataGrid.

So how do I populate the DataGrid dynamically?

[sloan]

If you want to use dynamic columns for your datasource, then you should
dynamically create your DataGrid.

Do a google search for

datagrid "new BoundColumn"
Or redesign/rethink your approach.

//Quote
If condition1 is true,

then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.

//End Quote
That sounds very hacky........
Here is some code I pulled from a hit (using that google search)

public void TestHyperLinkColumn()
{
// First add a simple bound column
BoundColumn nameColumn = new BoundColumn();
nameColumn.DataField = "ProductName";
nameColumn.DataFormatString = "{0}";
nameColumn.HeaderText = "Product";

// Now add the HyperLink column
HyperLinkColumn linkColumn = new HyperLinkColumn();
linkColumn.DataTextField = "ProductName";
linkColumn.DataTextFormatString = "{0} Details";
linkColumn.DataNavigateUrlField = "ProductID";
linkColumn.DataNavigateUrlFormatString =
"/MyApp/ProductDetails.aspx={0}";
linkColumn.HeaderText = "Details";

// Add the link in a BoundColumn
// where the text can be the same for all rows
BoundColumn blinkColumn = new BoundColumn();
blinkColumn.DataField = "ProductID";
blinkColumn.DataFormatString =
"<a href='/MyApp/ProductDetails.aspx={0}'>Details</a>";
blinkColumn.HeaderText = "Details";

DataGrid1.Columns.Add(nameColumn);
DataGrid1.Columns.Add(linkColumn);
DataGrid1.Columns.Add(blinkColumn);
DataGrid1.AutoGenerateColumns = false;

DataTable dt = GetNorthwindProductTable();
DataGrid1.DataSource = dt;
DataGrid1.DataBind();

}


<rn**@rediffmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

A SqlDataReader is populated with the records from a SQL Server 2005 DB
table. The records retrieved depends upon 2 conditions (the conditions
depend on what a user selects in an ASPX page). If condition1 is true,
then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.

Moreover the 2 tables do not have the same no. of columns - even the
column names are different. This means that the DataGrid has to be
filled dynamically. Had this not been the case, then using the
DataField property of the DataGrid, I could have populated the DataGrid
using

<asp:DataGrid ID="dgAddress" runat="server">
<Columns>
<asp:BoundColumn DataField="Address" HeaderText="ADDRESS"/>
<asp:BoundColumn DataField="City" HeaderText="CITY"/>
<asp:BoundColumn DataField="State" HeaderText="STATE"/>
<asp:BoundColumn DataField="Country" HeaderText="COUNTRY"/>
<asp:BoundColumn DataField="Zip" HeaderText="ZIP"/>
</Columns>
</asp:DataGrid>

But since I need to populate the DataGrid dynamically, the above
approach won't be feasible. So I tried to do it in the Page_Load sub
but I don't find any property named 'DataField' when I browsed through
the different properties of the DataGrid.

So how do I populate the DataGrid dynamically?

[rn5a]

Sloan, this is what I tried:

Sub Page_Load(....)
Dim BillAdd As DataGrid

BillAdd = New DataGrid
BillAdd.ID = "dgBillAdd"
i = 2

BillAdd.DataSource = sqlReader
While (sqlReader.Read)
BillAdd.Columns.Add(CreateBoundColumn(sqlReader.Ge tName(i)))
i = i + 1
End While

BillAdd.DataBind()
phlAddress.Controls.Add(BillAdd)
End Sub

Function CreateBoundColumn(ByVal ColumnName As String) As BoundColumn
Dim bColumn As BoundColumn
bColumn = New BoundColumn

bColumn.DataField = ColumnName
bColumn.HeaderText = ColumnName

Return bColumn
End Function

But the above code just displays the HeaderText of the column whose
index is 2 in the DataGrid & the HeaderText of the rest of the columns
don't get displayed in the DataGrid. Moreover, none of the records get
displayed in the DataGrid.

What am I missing here?
sloan wrote:

If you want to use dynamic columns for your datasource, then you should
dynamically create your DataGrid.

Do a google search for

datagrid "new BoundColumn"
Or redesign/rethink your approach.

//Quote
If condition1 is true,

then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.

//End Quote
That sounds very hacky........
Here is some code I pulled from a hit (using that google search)

public void TestHyperLinkColumn()
{
// First add a simple bound column
BoundColumn nameColumn = new BoundColumn();
nameColumn.DataField = "ProductName";
nameColumn.DataFormatString = "{0}";
nameColumn.HeaderText = "Product";

// Now add the HyperLink column
HyperLinkColumn linkColumn = new HyperLinkColumn();
linkColumn.DataTextField = "ProductName";
linkColumn.DataTextFormatString = "{0} Details";
linkColumn.DataNavigateUrlField = "ProductID";
linkColumn.DataNavigateUrlFormatString =
"/MyApp/ProductDetails.aspx={0}";
linkColumn.HeaderText = "Details";

// Add the link in a BoundColumn
// where the text can be the same for all rows
BoundColumn blinkColumn = new BoundColumn();
blinkColumn.DataField = "ProductID";
blinkColumn.DataFormatString =
"<a href='/MyApp/ProductDetails.aspx={0}'>Details</a>";
blinkColumn.HeaderText = "Details";

DataGrid1.Columns.Add(nameColumn);
DataGrid1.Columns.Add(linkColumn);
DataGrid1.Columns.Add(blinkColumn);
DataGrid1.AutoGenerateColumns = false;

DataTable dt = GetNorthwindProductTable();
DataGrid1.DataSource = dt;
DataGrid1.DataBind();

}


<rn**@rediffmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

A SqlDataReader is populated with the records from a SQL Server 2005 DB
table. The records retrieved depends upon 2 conditions (the conditions
depend on what a user selects in an ASPX page). If condition1 is true,
then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.

Moreover the 2 tables do not have the same no. of columns - even the
column names are different. This means that the DataGrid has to be
filled dynamically. Had this not been the case, then using the
DataField property of the DataGrid, I could have populated the DataGrid
using

<asp:DataGrid ID="dgAddress" runat="server">
<Columns>
<asp:BoundColumn DataField="Address" HeaderText="ADDRESS"/>
<asp:BoundColumn DataField="City" HeaderText="CITY"/>
<asp:BoundColumn DataField="State" HeaderText="STATE"/>
<asp:BoundColumn DataField="Country" HeaderText="COUNTRY"/>
<asp:BoundColumn DataField="Zip" HeaderText="ZIP"/>
</Columns>
</asp:DataGrid>

But since I need to populate the DataGrid dynamically, the above
approach won't be feasible. So I tried to do it in the Page_Load sub
but I don't find any property named 'DataField' when I browsed through
the different properties of the DataGrid.

So how do I populate the DataGrid dynamically?

[sloan]

You're trying to reuse the sqlReader. You only get ONE loop through on the
datareader object. You can't reuse it.

You're looping on it once to create the columns.
You're ~trying to reloop on it, when it gets bound to the grid. But you
can't reloop on it, thus you don't see any data-records/rows.

...

<rn**@rediffmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...

Sloan, this is what I tried:

Sub Page_Load(....)
Dim BillAdd As DataGrid

BillAdd = New DataGrid
BillAdd.ID = "dgBillAdd"
i = 2

BillAdd.DataSource = sqlReader
While (sqlReader.Read)
BillAdd.Columns.Add(CreateBoundColumn(sqlReader.Ge tName(i)))
i = i + 1
End While

BillAdd.DataBind()
phlAddress.Controls.Add(BillAdd)
End Sub

Function CreateBoundColumn(ByVal ColumnName As String) As BoundColumn
Dim bColumn As BoundColumn
bColumn = New BoundColumn

bColumn.DataField = ColumnName
bColumn.HeaderText = ColumnName

Return bColumn
End Function

But the above code just displays the HeaderText of the column whose
index is 2 in the DataGrid & the HeaderText of the rest of the columns
don't get displayed in the DataGrid. Moreover, none of the records get
displayed in the DataGrid.

What am I missing here?
sloan wrote:

If you want to use dynamic columns for your datasource, then you should
dynamically create your DataGrid.

Do a google search for

datagrid "new BoundColumn"
Or redesign/rethink your approach.

//Quote
If condition1 is true,

then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.

//End Quote
That sounds very hacky........
Here is some code I pulled from a hit (using that google search)

public void TestHyperLinkColumn()
{
// First add a simple bound column
BoundColumn nameColumn = new BoundColumn();
nameColumn.DataField = "ProductName";
nameColumn.DataFormatString = "{0}";
nameColumn.HeaderText = "Product";

// Now add the HyperLink column
HyperLinkColumn linkColumn = new HyperLinkColumn();
linkColumn.DataTextField = "ProductName";
linkColumn.DataTextFormatString = "{0} Details";
linkColumn.DataNavigateUrlField = "ProductID";
linkColumn.DataNavigateUrlFormatString =
"/MyApp/ProductDetails.aspx={0}";
linkColumn.HeaderText = "Details";

// Add the link in a BoundColumn
// where the text can be the same for all rows
BoundColumn blinkColumn = new BoundColumn();
blinkColumn.DataField = "ProductID";
blinkColumn.DataFormatString =
"<a href='/MyApp/ProductDetails.aspx={0}'>Details</a>";
blinkColumn.HeaderText = "Details";

DataGrid1.Columns.Add(nameColumn);
DataGrid1.Columns.Add(linkColumn);
DataGrid1.Columns.Add(blinkColumn);
DataGrid1.AutoGenerateColumns = false;

DataTable dt = GetNorthwindProductTable();
DataGrid1.DataSource = dt;
DataGrid1.DataBind();

}


<rn**@rediffmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

A SqlDataReader is populated with the records from a SQL Server 2005

DB

table. The records retrieved depends upon 2 conditions (the conditions
depend on what a user selects in an ASPX page). If condition1 is true,
then the SqlDataReader will be populated with the records existing in
table1 & will return 0 to the calling function but if condition2 is
true, then the SqlDataReader will be populated with the records from
another table named table2 & will return 1 to the calling function.
>
Moreover the 2 tables do not have the same no. of columns - even the
column names are different. This means that the DataGrid has to be
filled dynamically. Had this not been the case, then using the
DataField property of the DataGrid, I could have populated the

DataGrid

using
>
<asp:DataGrid ID="dgAddress" runat="server">
<Columns>
<asp:BoundColumn DataField="Address" HeaderText="ADDRESS"/>
<asp:BoundColumn DataField="City" HeaderText="CITY"/>
<asp:BoundColumn DataField="State" HeaderText="STATE"/>
<asp:BoundColumn DataField="Country" HeaderText="COUNTRY"/>
<asp:BoundColumn DataField="Zip" HeaderText="ZIP"/>
</Columns>
</asp:DataGrid>
>
But since I need to populate the DataGrid dynamically, the above
approach won't be feasible. So I tried to do it in the Page_Load sub
but I don't find any property named 'DataField' when I browsed through
the different properties of the DataGrid.
>
So how do I populate the DataGrid dynamically?
>