Dynamically Populate DataGrid Using SqlDataReader

  • Thread starter Thread starter rn5a
  • Start date Start date
R

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?
 
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.
Click to expand...
//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();

}
 
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.GetName(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?
 
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.

...
 
Back
Top