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:BoundColum n DataField="Addr ess" HeaderText="ADD RESS"/>
<asp:BoundColum n DataField="City " HeaderText="CIT Y"/>
<asp:BoundColum n DataField="Stat e" HeaderText="STA TE"/>
<asp:BoundColum n DataField="Coun try" HeaderText="COU NTRY"/>
<asp:BoundColum n 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 TestHyperLinkCo lumn()
{
// First add a simple bound column
BoundColumn nameColumn = new BoundColumn();
nameColumn.Data Field = "ProductNam e";
nameColumn.Data FormatString = "{0}";
nameColumn.Head erText = "Product";

// Now add the HyperLink column
HyperLinkColumn linkColumn = new HyperLinkColumn ();
linkColumn.Data TextField = "ProductNam e";
linkColumn.Data TextFormatStrin g = "{0} Details";
linkColumn.Data NavigateUrlFiel d = "ProductID" ;
linkColumn.Data NavigateUrlForm atString =
"/MyApp/ProductDetails. aspx={0}";
linkColumn.Head erText = "Details";

// Add the link in a BoundColumn
// where the text can be the same for all rows
BoundColumn blinkColumn = new BoundColumn();
blinkColumn.Dat aField = "ProductID" ;
blinkColumn.Dat aFormatString =
"<a href='/MyApp/ProductDetails. aspx={0}'>Detai ls</a>";
blinkColumn.Hea derText = "Details";

DataGrid1.Colum ns.Add(nameColu mn);
DataGrid1.Colum ns.Add(linkColu mn);
DataGrid1.Colum ns.Add(blinkCol umn);
DataGrid1.AutoG enerateColumns = false;

DataTable dt = GetNorthwindPro ductTable();
DataGrid1.DataS ource = dt;
DataGrid1.DataB ind();

}


<rn**@rediffmai l.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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:BoundColum n DataField="Addr ess" HeaderText="ADD RESS"/>
<asp:BoundColum n DataField="City " HeaderText="CIT Y"/>
<asp:BoundColum n DataField="Stat e" HeaderText="STA TE"/>
<asp:BoundColum n DataField="Coun try" HeaderText="COU NTRY"/>
<asp:BoundColum n 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.DataSou rce = sqlReader
While (sqlReader.Read )
BillAdd.Columns .Add(CreateBoun dColumn(sqlRead er.GetName(i)))
i = i + 1
End While

BillAdd.DataBin d()
phlAddress.Cont rols.Add(BillAd d)
End Sub

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

bColumn.DataFie ld = ColumnName
bColumn.HeaderT ext = 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 TestHyperLinkCo lumn()
{
// First add a simple bound column
BoundColumn nameColumn = new BoundColumn();
nameColumn.Data Field = "ProductNam e";
nameColumn.Data FormatString = "{0}";
nameColumn.Head erText = "Product";

// Now add the HyperLink column
HyperLinkColumn linkColumn = new HyperLinkColumn ();
linkColumn.Data TextField = "ProductNam e";
linkColumn.Data TextFormatStrin g = "{0} Details";
linkColumn.Data NavigateUrlFiel d = "ProductID" ;
linkColumn.Data NavigateUrlForm atString =
"/MyApp/ProductDetails. aspx={0}";
linkColumn.Head erText = "Details";

// Add the link in a BoundColumn
// where the text can be the same for all rows
BoundColumn blinkColumn = new BoundColumn();
blinkColumn.Dat aField = "ProductID" ;
blinkColumn.Dat aFormatString =
"<a href='/MyApp/ProductDetails. aspx={0}'>Detai ls</a>";
blinkColumn.Hea derText = "Details";

DataGrid1.Colum ns.Add(nameColu mn);
DataGrid1.Colum ns.Add(linkColu mn);
DataGrid1.Colum ns.Add(blinkCol umn);
DataGrid1.AutoG enerateColumns = false;

DataTable dt = GetNorthwindPro ductTable();
DataGrid1.DataS ource = dt;
DataGrid1.DataB ind();

}


<rn**@rediffmai l.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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:BoundColum n DataField="Addr ess" HeaderText="ADD RESS"/>
<asp:BoundColum n DataField="City " HeaderText="CIT Y"/>
<asp:BoundColum n DataField="Stat e" HeaderText="STA TE"/>
<asp:BoundColum n DataField="Coun try" HeaderText="COU NTRY"/>
<asp:BoundColum n 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**@rediffmai l.comwrote in message
news:11******** *************@h 48g2000cwc.goog legroups.com...

Sloan, this is what I tried:

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

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

BillAdd.DataSou rce = sqlReader
While (sqlReader.Read )
BillAdd.Columns .Add(CreateBoun dColumn(sqlRead er.GetName(i)))
i = i + 1
End While

BillAdd.DataBin d()
phlAddress.Cont rols.Add(BillAd d)
End Sub

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

bColumn.DataFie ld = ColumnName
bColumn.HeaderT ext = 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 TestHyperLinkCo lumn()
{
// First add a simple bound column
BoundColumn nameColumn = new BoundColumn();
nameColumn.Data Field = "ProductNam e";
nameColumn.Data FormatString = "{0}";
nameColumn.Head erText = "Product";

// Now add the HyperLink column
HyperLinkColumn linkColumn = new HyperLinkColumn ();
linkColumn.Data TextField = "ProductNam e";
linkColumn.Data TextFormatStrin g = "{0} Details";
linkColumn.Data NavigateUrlFiel d = "ProductID" ;
linkColumn.Data NavigateUrlForm atString =
"/MyApp/ProductDetails. aspx={0}";
linkColumn.Head erText = "Details";

// Add the link in a BoundColumn
// where the text can be the same for all rows
BoundColumn blinkColumn = new BoundColumn();
blinkColumn.Dat aField = "ProductID" ;
blinkColumn.Dat aFormatString =
"<a href='/MyApp/ProductDetails. aspx={0}'>Detai ls</a>";
blinkColumn.Hea derText = "Details";

DataGrid1.Colum ns.Add(nameColu mn);
DataGrid1.Colum ns.Add(linkColu mn);
DataGrid1.Colum ns.Add(blinkCol umn);
DataGrid1.AutoG enerateColumns = false;

DataTable dt = GetNorthwindPro ductTable();
DataGrid1.DataS ource = dt;
DataGrid1.DataB ind();

}


<rn**@rediffmai l.comwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.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:BoundColum n DataField="Addr ess" HeaderText="ADD RESS"/>
<asp:BoundColum n DataField="City " HeaderText="CIT Y"/>
<asp:BoundColum n DataField="Stat e" HeaderText="STA TE"/>
<asp:BoundColum n DataField="Coun try" HeaderText="COU NTRY"/>
<asp:BoundColum n 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?
>