Databinding by ClientID

[oafyuf]

Hi,
I'm trying to learn ASP.NET by doing a pilot project:

I have a DataGrid which contains a nested DataList. I want to iterate
through the SQLDataReader for the DataGrid and populate each DataList
by binding to a fresh SQLDataReader. At the moment I can only bind to
the first instance of the DataList. If I could bind the data by the
ClientID (or UniqueID), I'm sure it would work. Is there a way of
doing this? Or maybe there is a better approach to getting nested data
from separate SQLDataReaders.

Here's the relevant part of the code with extraneous bits removed (I
hope the line breaks don't make it unreadable!):

// ######### ASPX ###########

<asp:DataGrid Border="1" Runat="server" Id="dgApplicant s">
<Columns>
<asp:BoundColum n DataField="Appl icantRef" Visible="False" />

<%-- Some more BoundColumns and TemplateColumns --%>

<asp:TemplateCo lumn>
<ItemTemplate >
<tr>
<td width="30px">&n bsp;</td>
<td colspan="8">
<asp:DataList Border="0" Runat="server" Id="dgApplicati ons"
AutoGenerateCol umns="False" Height="0px" Width="100%"
AllowSorting="F alse">
<ItemTemplate >
<b>App&nbsp;< %# DataBinder.Eval (Container.Data Item,
"VendorNo")%>&n bsp;<%# DataBinder.Eval (Container.Data Item,
"ApplicationRef ")%>&nbsp;v <%# DataBinder.Eval (Container.Data Item,
"Version")%>&nb sp;<%# DataBinder.Eval (Container.Data Item,
"Status")%> </b>
</ItemTemplate>
</asp:DataList>
</td>
</tr>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid>

// ######### ASPX ENDS ###########

// ######### CODE-BEHIND ###########
//adoApplicantRea der is the outer loop
//adoApplicationR eader is the inner loop

if(objDuplicate PartiesApplican t.ListParties(i nputSurname, inputTown,
inputMainCPH, inputBusinessNa me, inputPostcode, inputOrderBy, ref
adoApplicantRea der) == MyAssembly.AESI S.ReturnCodes.S uccess)
{
// OK, the SQL data layer object returned "success", but did we get
some results?
if(adoApplicant Reader.HasRows)
{
// successful search: hide prompt and display Update button
lblPrompt.Visib le = false;
btnUpdate.Visib le = true;

// databinding method
this.dgApplican ts.DataSource = adoApplicantRea der;
this.dgApplican ts.DataBind();
adoApplicantRea der.Close();

// with the firehose cursor (SQLDataReader) , we have to do the query
again after it is bound to pass it's members...
if(objDuplicate PartiesApplican t.ListParties(i nputSurname, inputTown,
inputMainCPH, inputBusinessNa me, inputPostcode, inputOrderBy, ref
adoApplicantRea der) == MyAssembly.AESI S.ReturnCodes.S uccess)
{
// SQL data layer object returned "success"
if(adoApplicant Reader.HasRows)
{
while (adoApplicantRe ader.Read())
{
int intOrd = adoApplicantRea der.GetOrdinal( "ApplicantRef") ;
int intApplicantRef = (int)adoApplica ntReader.GetVal ue(intOrd);

SqlDataReader adoApplicationR eader = null;

if(objDuplicate PartiesApplicat ion.ListPartyAp plications(intA pplicantRef,
ref adoApplicationR eader) == MyAssembly.AESI S.ReturnCodes.S uccess)
{
// did we get some results?
if(adoApplicati onReader.HasRow s)
{
// find a reference to the nested DataList
foreach(DataGri dItem dgItem in dgApplicants.It ems)
{

DataList dgSelected =
(DataList)dgIte m.FindControl(" dgApplications" );

// here I need to find the reference name of each nested
datagrid
// and assign data to that...
Response.Write( "<tr><td><f ont color=#cc0000>C lientID:
"+dgSelected.Cl ientID+"</font></td></tr>"); // debug
dgSelected.Data Source = adoApplicationR eader;
dgSelected.Data Bind();

}
}
adoApplicationR eader.Close();
}
}
}
}
else
{
lblPrompt.Text = "No records returned for this search. Please try
again.";
}
// tidy up before the garbage collector gets a chance to do it...
adoApplicantRea der.Close();
}
else
{
lblPrompt.Text = "DATABASE ERROR:<br/><font color=#ff0000>\ "" +
objDuplicatePar tiesApplicant.N ativeError + "\"</font><br/>Please
report this error to AESIS support.";
}
}
// ######### CODE-BEHIND ENDS ###########

The whole thing looks ugly to me because I have to keep on getting
fresh data, is there a more flexible cursor that I can use?

My next step is to investigate using DataSets, combining the XML DOMs
and doing all the manipulation of the presentation layer via
client-side JavaScript. Is this the recommended method?

Thanks,
oafyuf

[Mike Moore [MSFT]]

Hi Oafyuf,

You need to use the datagrid's ItemDataBound event to populate the inner
datalist. Here is a sample based on the Pubs sample database.

I placed a datagrid on a form and added a template column (the rest are
autogenerate). I added a datalist to the template column. Then I added the
following code.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
If Not IsPostBack Then
Bind()
End If
End Sub

Private Sub Bind()
Dim Qry1 As System.Data.Sql Client.SqlDataR eader
Dim connectionStrin g As String = "server='localh ost';
trusted_connect ion=true; Database='pubs' "
Dim sqlConnection As System.Data.Sql Client.SqlConne ction = New
System.Data.Sql Client.SqlConne ction(connectio nString)
Dim queryString As String = "SELECT au_id, au_lname, au_fname FROM
authors"
Dim sqlCommand As System.Data.Sql Client.SqlComma nd = New
System.Data.Sql Client.SqlComma nd(queryString, sqlConnection)
sqlConnection.O pen()
Qry1 =
sqlCommand.Exec uteReader(Syste m.Data.CommandB ehavior.CloseCo nnection)
DataGrid1.DataS ource = Qry1
DataGrid1.DataB ind()
Qry1.Close()
sqlCommand.Disp ose()
sqlConnection.C lose()
sqlConnection.D ispose()
End Sub

Private Sub DataGrid1_ItemD ataBound(ByVal sender As Object, ByVal e As
System.Web.UI.W ebControls.Data GridItemEventAr gs) Handles
DataGrid1.ItemD ataBound
If e.Item.ItemType = ListItemType.It em Or e.Item.ItemType =
ListItemType.Al ternatingItem Then
Dim list As ListBox
list = e.Item.Cells(0) .Controls(1)
Dim Qry1 As System.Data.Sql Client.SqlDataR eader
Dim connectionStrin g As String = "server='localh ost';
trusted_connect ion=true; Database='pubs' "
Dim sqlConnection As System.Data.Sql Client.SqlConne ction = New
System.Data.Sql Client.SqlConne ction(connectio nString)
Dim queryString As String = "SELECT title FROM titles t inner
join titleauthor ta on t.title_id = ta.title_id inner join authors a on
ta.au_id = a.au_id where a.au_id = '" & e.Item.Cells(1) .Text & "'"
Dim sqlCommand As System.Data.Sql Client.SqlComma nd = New
System.Data.Sql Client.SqlComma nd(queryString, sqlConnection)
sqlConnection.O pen()
Qry1 =
sqlCommand.Exec uteReader(Syste m.Data.CommandB ehavior.CloseCo nnection)
list.DataSource = Qry1
list.DataTextFi eld = "title"
list.DataBind()
If list.Items.Coun t = 0 Then list.Visible = False
list.Height = list.Height.Poi nt(30 * list.Items.Coun t)
Qry1.Close()
sqlCommand.Disp ose()
sqlConnection.C lose()
sqlConnection.D ispose()
End If
End Sub

---
I hope this helps.

Thank you, Mike
Microsoft, ASP.NET Support Professional

Microsoft highly recommends to all of our customers that they visit the
http://www.microsoft.com/protect site and perform the three straightforward
steps listed to improve your computer’s security.

This posting is provided "AS IS", with no warranties, and confers no rights.
--------------------

From: oa****@hotmail. com (oafyuf)
Newsgroups: microsoft.publi c.dotnet.framew ork.aspnet
Subject: Databinding by ClientID
Date: 24 Oct 2003 04:24:41 -0700
Organization: http://groups.google.com
Lines: 138
Message-ID: <ea************ **************@ posting.google. com>
NNTP-Posting-Host: 212.137.57.41
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google. com 1066994681 30561 127.0.0.1 (24 Oct 2003 11:24:41 GMT) X-Complaints-To: gr**********@go ogle.com
NNTP-Posting-Date: Fri, 24 Oct 2003 11:24:41 +0000 (UTC)
Path: cpmsftngxa06.ph x.gbl!TK2MSFTNG P08.phx.gbl!new sfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.s yr.edu!news.max well.syr.edu!po stnews1.google. com!no
t-for-mail Xref: cpmsftngxa06.ph x.gbl microsoft.publi c.dotnet.framew ork.aspnet:1862 90
X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet

Hi,
I'm trying to learn ASP.NET by doing a pilot project:

I have a DataGrid which contains a nested DataList. I want to iterate
through the SQLDataReader for the DataGrid and populate each DataList
by binding to a fresh SQLDataReader. At the moment I can only bind to
the first instance of the DataList. If I could bind the data by the
ClientID (or UniqueID), I'm sure it would work. Is there a way of
doing this? Or maybe there is a better approach to getting nested data
from separate SQLDataReaders.

Here's the relevant part of the code with extraneous bits removed (I
hope the line breaks don't make it unreadable!):

// ######### ASPX ###########

<asp:DataGrid Border="1" Runat="server" Id="dgApplicant s">
<Columns>
<asp:BoundColum n DataField="Appl icantRef" Visible="False" />

<%-- Some more BoundColumns and TemplateColumns --%>

<asp:TemplateCo lumn>
<ItemTemplate >
<tr>
<td width="30px">&n bsp;</td>
<td colspan="8">
<asp:DataList Border="0" Runat="server" Id="dgApplicati ons"
AutoGenerateCol umns="False" Height="0px" Width="100%"
AllowSorting="F alse">
<ItemTemplate >
<b>App&nbsp;< %# DataBinder.Eval (Container.Data Item,
"VendorNo")%>&n bsp;<%# DataBinder.Eval (Container.Data Item,
"ApplicationRef ")%>&nbsp;v <%# DataBinder.Eval (Container.Data Item,
"Version")%>&nb sp;<%# DataBinder.Eval (Container.Data Item,
"Status")%> </b>
</ItemTemplate>
</asp:DataList>
</td>
</tr>
</ItemTemplate>
</asp:TemplateCol umn>
</Columns>
</asp:DataGrid>

// ######### ASPX ENDS ###########

// ######### CODE-BEHIND ###########
//adoApplicantRea der is the outer loop
//adoApplicationR eader is the inner loop

if(objDuplicate PartiesApplican t.ListParties(i nputSurname, inputTown,
inputMainCPH, inputBusinessNa me, inputPostcode, inputOrderBy, ref
adoApplicantRea der) == MyAssembly.AESI S.ReturnCodes.S uccess)
{
// OK, the SQL data layer object returned "success", but did we get
some results?
if(adoApplicant Reader.HasRows)
{
// successful search: hide prompt and display Update button
lblPrompt.Visib le = false;
btnUpdate.Visib le = true;

// databinding method
this.dgApplican ts.DataSource = adoApplicantRea der;
this.dgApplican ts.DataBind();
adoApplicantRea der.Close();

// with the firehose cursor (SQLDataReader) , we have to do the query
again after it is bound to pass it's members...
if(objDuplicate PartiesApplican t.ListParties(i nputSurname, inputTown,
inputMainCPH, inputBusinessNa me, inputPostcode, inputOrderBy, ref
adoApplicantRea der) == MyAssembly.AESI S.ReturnCodes.S uccess)
{
// SQL data layer object returned "success"
if(adoApplicant Reader.HasRows)
{
while (adoApplicantRe ader.Read())
{
int intOrd = adoApplicantRea der.GetOrdinal( "ApplicantRef") ;
int intApplicantRef = (int)adoApplica ntReader.GetVal ue(intOrd);

SqlDataReader adoApplicationR eader = null;

if(objDuplicate PartiesApplicat ion.ListPartyAp plications(intA pplicantRef, ref adoApplicationR eader) == MyAssembly.AESI S.ReturnCodes.S uccess)
{
// did we get some results?
if(adoApplicati onReader.HasRow s)
{
// find a reference to the nested DataList
foreach(DataGri dItem dgItem in dgApplicants.It ems)
{

DataList dgSelected =
(DataList)dgIte m.FindControl(" dgApplications" );

// here I need to find the reference name of each nested
datagrid
// and assign data to that...
Response.Write( "<tr><td><f ont color=#cc0000>C lientID:
"+dgSelected.Cl ientID+"</font></td></tr>"); // debug
dgSelected.Data Source = adoApplicationR eader;
dgSelected.Data Bind();

}
}
adoApplicationR eader.Close();
}
}
}
}
else
{
lblPrompt.Text = "No records returned for this search. Please try
again.";
}
// tidy up before the garbage collector gets a chance to do it...
adoApplicantRea der.Close();
}
else
{
lblPrompt.Text = "DATABASE ERROR:<br/><font color=#ff0000>\ "" +
objDuplicatePar tiesApplicant.N ativeError + "\"</font><br/>Please
report this error to AESIS support.";
}
}
// ######### CODE-BEHIND ENDS ###########

The whole thing looks ugly to me because I have to keep on getting
fresh data, is there a more flexible cursor that I can use?

My next step is to investigate using DataSets, combining the XML DOMs
and doing all the manipulation of the presentation layer via
client-side JavaScript. Is this the recommended method?

Thanks,
oafyuf