Displaying data in a data grid and using logic to display certain columns...

I'm pulling some tickets from SQL Server.  The ticket has two parts, a memberinfo and work steps.  The memberinfo information is in my JT_JOINT_TRENCH table and the steps information is in my JTJS_JOINT_TRENCH_JOB_STEPS table.  A foreign key in the JTJS_JOINT_TRENCH_JOB_STEPS(jtjs_jt_id) table joins it with the JT_JOINT_TRENCH table where the primary key is jt_id.

I want to display each ticket(whole ticket including steps) on arow of the data grid.  Some tickets have more steps than others.    I want to know how I can create my datagrid to do this.   Where it will populate all the JT_JOINT_TRENCH information, then find all the steps that have that are connected to that record byt the jt_id(jtjs_jt_id = jt_id) and populate those columns, then move to the next ticket.

Basically, I want it to look like this(columns below, seperated by | ):

JT_JOINT_TRENCH info                                   JTJS_...      

jt_id | jt_crt_date | jt_updt_date | etc....   then |jtjs_no | jtjs_job_id | etc...  

IF ANOTHER jtjs_id exists where jtjs_jt_id = jt_id THEN, the columns would repeat outside(to the right) of the 1st jtjs record.

Below is my code that pulls the data and binds to the data grid.... PLEASE help!!!
_______________________________________________________________________-

Sub Pull_Report(s as object, e as EventArgs)

Dim connString as String = "server=localhost;uid=sa;pwd=iamagt!;database=NJUNS_2001"
     Dim cnn as New SqlConnection(connString)
     Dim JTHeader as New SqlDataAdapter("SELECT JT_JOINT_TRENCH.jt_id, JT_JOINT_TRENCH.jt_crt_date, JT_JOINT_TRENCH.jt_updt_date, JT_JOINT_TRENCH.jt_required_date, JT_JOINT_TRENCH.jt_priority, JT_JOINT_TRENCH.jt_dot, JT_JOINT_TRENCH.jt_status, JT_JOINT_TRENCH.jt_place, JT_JOINT_TRENCH.jt_location, JT_JOINT_TRENCH.jt_contact_name, JT_JOINT_TRENCH.jt_phone, JT_JOINT_TRENCH.jt_phone_ext, JT_JOINT_TRENCH.jt_construction_type, MBR_MEMBER.mbr_code AS jt_coordinator_id, CNTY_COUNTY.cnty_name AS jt_cnty_id, ST_STATE.st_abbreviation FROM JT_JOINT_TRENCH INNER JOIN MBR_MEMBER ON JT_JOINT_TRENCH.jt_coordinator_id = MBR_MEMBER.mbr_id INNER JOIN CNTY_COUNTY ON JT_JOINT_TRENCH.jt_cnty_id = CNTY_COUNTY.cnty_id INNER JOIN ST_STATE ON CNTY_COUNTY.cnty_st_id = ST_STATE.st_id AND CNTY_COUNTY.cnty_st_id = ST_STATE.st_id", cnn)
     Dim JTSteps as New SqlDataAdapter("SELECT JTJS_JOINT_TRENCH_JOB_STEP.jtjs_no, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_job_id, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_lots, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_job_date, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_updt_date, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_y_or_n, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_remarks, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_feet, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_job_step, MBR_MEMBER.mbr_code AS jtjs_mem_id FROM JTJS_JOINT_TRENCH_JOB_STEP INNER JOIN MBR_MEMBER ON JTJS_JOINT_TRENCH_JOB_STEP.jtjs_mem_id = MBR_MEMBER.mbr_id", cnn)
     Dim dsJTHeader as New DataSet()
     Dim dsJTSteps as New DataSet()
     JTHeader.Fill(dsJTHeader, "MyTable")
     JTSteps.Fill(dsJTSteps, "MyTable")
     
     dsJTHeader.Merge(dsJTSteps)
     
     gridTickets.DataSource=dsJTHeader
     gridTickets.DataBind()
     gridTickets.Visible = True
     
     End Sub

almix77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

boulder_bumCommented:
Perhaps do this:

1. Get a table full of all tickets.
2. Get a table full of steps for all tickets.
3. Add both tables to a DataSet and add a DataRelation between the two tables such that the ticket steps are associated with their parent tickets.
4. Assign the DataSource of your grid to be the table full of tickets.
5. In the ItemTemplate for the grid, have a DataList or something.
6. In the ItemDataBound event of the grid, get all the child records (ticket steps) for the current Item and bind those child records to the Item's DataList.
0
almix77Author Commented:
I appreciate the steps you described but can anyone give some sample code for this?  I'm new to this and am unfamiliar with the DataRelation.
0
boulder_bumCommented:
This should give a quick overview:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatarelationclasstopic.asp

private void CreateRelation()
{
    // Get the DataColumn objects from two DataTable objects in a DataSet.
    DataColumn parentCol;
    DataColumn childCol;

    // Code to get the DataSet not shown here.
    parentCol = DataSet1.Tables["Customers"].Columns["CustID"];
    childCol = DataSet1.Tables["Orders"].Columns["CustID"];

    // Create DataRelation.
    DataRelation relCustOrder;
    relCustOrder = new DataRelation("CustomersOrders", parentCol, childCol);

    // Add the relation to the DataSet.
    DataSet1.Relations.Add(relCustOrder);
 }


Then you can retrieve an array of DataRows like this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatarowclassgetchildrowstopic.asp

DataRow[] childRows = parentRow.GetChildRows( myRelation );

You should then be able to bind the child rows.



0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

almix77Author Commented:
Thanks... one quick question... above in my code, I've created 2 datasets from my 2 queries and merged them.  Can I now just created 2 tables within the dataset(having to defined the columns) and then do the datarelation?  Or is there an easier way to do that?
0
boulder_bumCommented:
The easiest way is to populate the two tables (letting them control their own schema construction) then link those tables by referencing columns you expect to be there. For example, if you have:

SELECT some_field FROM...

then you'll have a "some_field" column you can then establish a relation for.
0
almix77Author Commented:
OK... I just don't understand how to fill the datatable with the values of the fields in the database... Forgive me, I'm very new at this but kind of got stuck with it.
0
boulder_bumCommented:
I want to make sure I'm understanding your question, too. :-)

When you use the SqlDataAdapter, it actually DOES fill the DataTable up with fields from the DB, as well as making the appropriate columns. If you have a SELECT statement: SELECT A, B, C, then your DataTable will automatically have the columns A, B, and C, there's no additional work involved.

To reference the columns (which are automatically created), you'd simply reference myDataTable.Columns["A"], where "A" can be any field name returned from the query.

 
0
almix77Author Commented:
Cool... I think I got that part figured out...  Now, previously, you stated that I should nest a datalist within my datagrid?
0
boulder_bumCommented:
Or something like that. Basically the idea is to use a databound control that is able to display a varying number of child records, and I think a DataList accomplishes the task nicely (it can display N-number of columns, and even neatly organize a group of children into rows of 3 or 4 columns per if you'd like to save page space).

Here's an example of databound control nesting:

http://www.codeguru.com/Csharp/.NET/net_data/datagrid/article.php/c5619/

For your purposes, you'd just get the child rows for the current parent row and bind the DataList (or whatever) to that child collection.
0
boulder_bumCommented:
I lied about the link; it doesn't give an example of nesting apparently (I posted it without reading). Let me see what else I can dig up really quick.
0
almix77Author Commented:
Thanks... I think I may be getting there, but the last part is stumping me.  What am I supposed to do on the "ItemDataBound" event?  I want the columns to repeat the jobsteps while jtjs_jt_id = jt_id
The following is the code I have:


      Sub Pull_Report(s as object, e as EventArgs)

Dim connString as String = "server=localhost;uid=sa;pwd=iamagt!;database=NJUNS_2001"
      Dim cnn as New SqlConnection(connString)
      Dim JTHeader as New SqlDataAdapter("SELECT JT_JOINT_TRENCH.jt_id, JT_JOINT_TRENCH.jt_crt_date,

JT_JOINT_TRENCH.jt_updt_date, JT_JOINT_TRENCH.jt_required_date, JT_JOINT_TRENCH.jt_priority, JT_JOINT_TRENCH.jt_dot,

JT_JOINT_TRENCH.jt_status, JT_JOINT_TRENCH.jt_place, JT_JOINT_TRENCH.jt_location, JT_JOINT_TRENCH.jt_contact_name,

JT_JOINT_TRENCH.jt_phone, JT_JOINT_TRENCH.jt_phone_ext, JT_JOINT_TRENCH.jt_construction_type, MBR_MEMBER.mbr_code AS

jt_coordinator_id, CNTY_COUNTY.cnty_name AS jt_cnty_id, ST_STATE.st_abbreviation FROM JT_JOINT_TRENCH INNER JOIN MBR_MEMBER

ON JT_JOINT_TRENCH.jt_coordinator_id = MBR_MEMBER.mbr_id INNER JOIN CNTY_COUNTY ON JT_JOINT_TRENCH.jt_cnty_id =

CNTY_COUNTY.cnty_id INNER JOIN ST_STATE ON CNTY_COUNTY.cnty_st_id = ST_STATE.st_id AND CNTY_COUNTY.cnty_st_id =

ST_STATE.st_id", cnn)
      Dim JTSteps as New SqlDataAdapter("SELECT JTJS_JOINT_TRENCH_JOB_STEP.jtjs_no, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_job_id,

JTJS_JOINT_TRENCH_JOB_STEP.jtjs_lots, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_job_date, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_updt_date,

JTJS_JOINT_TRENCH_JOB_STEP.jtjs_y_or_n, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_remarks, JTJS_JOINT_TRENCH_JOB_STEP.jtjs_feet,

JTJS_JOINT_TRENCH_JOB_STEP.jtjs_job_step, MBR_MEMBER.mbr_code AS jtjs_mem_id FROM JTJS_JOINT_TRENCH_JOB_STEP INNER JOIN

MBR_MEMBER ON JTJS_JOINT_TRENCH_JOB_STEP.jtjs_mem_id = MBR_MEMBER.mbr_id", cnn)
      
      Dim dsJT as New DataSet()
      Dim dsJTSteps as New DataSet()
      JTHeader.Fill(dsJT, "TicketHeader")
      JTSteps.Fill(dsJTSteps, "JobSteps")
      
      dsJT.Merge(dsJTSteps)

      Dim parentCol as DataColumn
      Dim childCol as DataColumn
      
      parentCol = dsJT.Tables("TicketHeader").Columns("jt_id")
      childCol = dsJT.Tables("JobSteps").Columns("jtjs_jt_id")

      Dim relID as DataRelation
      relID = New DataRelation("Tickets",parentCol,childCol)
      dsJT.Relations.Add(relID)
      
      
      
      gridTickets.DataSource=dsJT
      gridTickets.DataBind()
      gridTickets.Visible = True
      
      End Sub



</Script>


<html>
<head><title>Report_Test</title></head>
<body>

<form method="post" Runat="Server">
<center>

<asp:DataGrid id="gridTickets" runat="server" AutoGenerateColumns="False" CellPadding="2" HeaderStyle-BackColor="gray"

HeaderStyle-ForeColor="White" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True">

<Columns>
<asp:BoundColumn HeaderText="Ticket Number" DataField="jt_id" />
<asp:BoundColumn HeaderText="Date Created" DataField="jt_crt_date" />
<asp:BoundColumn HeaderText="Date Updated" DataField="jt_updt_date" />
<asp:BoundColumn HeaderText="Required Date" DataField="jt_required_date" />
<asp:BoundColumn HeaderText="Priority" DataField="jt_priority" />
<asp:BoundColumn HeaderText="DOT" DataField="jt_dot" />
<asp:BoundColumn HeaderText="Status" DataField="jt_status" />
<asp:BoundColumn HeaderText="Construction Type" DataField="jt_construction_type" />
<asp:BoundColumn HeaderText="Coordinator" DataField="jt_coordinator_id" />
<asp:BoundColumn HeaderText="Originator Company" DataField="mbr_full_name" />
<asp:BoundColumn HeaderText="Originator Location" DataField="mbr_city" />
<asp:BoundColumn HeaderText="Engineer" DataField="jt_contact_name" />
<asp:BoundColumn HeaderText="Engineer Phone" DataField="jt_phone" />
<asp:BoundColumn HeaderText="Engineer Ext" DataField="jt_phone_ext" />
<asp:BoundColumn HeaderText="State" DataField="st_abbreviation" />
<asp:BoundColumn HeaderText="County" DataField="cnty_name" />
<asp:BoundColumn HeaderText="Place" DataField="jt_place" />
<asp:BoundColumn HeaderText="Location" DataField="jt_location" />
<asp:TemplateColumn HeaderText="Job Steps" />
  <ItemTemplate>
      <asp:DataList id="myDataList" runat="server">
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_no") %>
      </ItemTemplate>      
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_job_id") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_lots") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_job_date") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_updt_date") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_feet") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_job_step") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_mem_id") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_y_or_n") %>
      </ItemTemplate>
      <ItemTemplate>
      <b><%# Container.DataItem("jtjs_remarks") %>
      </ItemTemplate>
      </asp:DataList>
  </ItemTemplate>
</asp:TemplateColumn>
</asp:DataGrid>
0
almix77Author Commented:
If you are wondering why I want a whole ticket (header and job steps) on one row, its because I then want to export the grid to a delimited file....
0
boulder_bumCommented:
Okay, since you have a grid which is essentially just a list of memberinfo records (or whatever), you basically want a way to have a nested data structure that contains all the child records. The item template will let you insert this data structure, but it won't let you assign a DataSource for the structure (because the datasource varies by each row). This is where the ItemDataBound event comes in.

In the ItemDataBound event, you basically have a place where you can perform special initialization for each row. In your case, you want to set the DataSource for the row, so you'd do something like:

//get the row from the parent DataTable based on the DataKeys (you'll have to set up some PrimaryKey stuff in the DataTable)
DataRow drCurrentParent = parentDataTable.Rows.Find( myDataGrid.DataKeys( e.Item.ItemIndex ) );

//get a collection of child records for the current row
DataRow[] drChildren = drCurrentParent.GetChildRows( myRelation );

//bind those rows to the data structure
myDataList.DataSource = drChildren;
myDataList.DataBind();



This approach will work well and should be pretty elegant, though I'm not sure it will accomplish what you want for the delimited file (did your plan for that involve a single table instead of nested sub-tables?).

0
almix77Author Commented:
Somehow, I have failed to define my datatables within my dataset, because it is not recognizing parentDataTable.Rows.Find... I'm confused as to what the datatble name would be... maybe I pulled it wrong in the above code.?
0
boulder_bumCommented:
Did you manually define the PrimaryKey? You have to do that.

Also, pardon me if you already realize this (I wasn't sure from the last post), but parentDataTable is just a name I gave: it could be anything like "bobTheTable.Rows.Find()"; parentDataTable isn't a property or anything. You'll actually have to get a reference to your parent table via myDataSet.Tables[/*index*/] or by passing or using the tables from the original code block.
0
boulder_bumCommented:
PS- I should probably mention that you need to set the PrimaryKey of the tables before binding them to controls in order to be able to associated teh DataKeys stuff properly.
0
boulder_bumCommented:
Actually I lied (my deepest appologies, it's been a while since I've done this particular task) the DataKeyField is what associates the grid rows with the keys, though you'll need to assign the PrimaryKey properly for processing.
0
almix77Author Commented:
I still can't get the code to recognize my table.  When I populate the dataset, does it not create the 2 tables for me?  Its not recognizing.  It notes the following code:

drCurrentParent = JT_JOINT_TRENCH.Rows.Find(gridTickets.DataKeys(e.Item.ItemIndex))

It says JT_JOINT_TRENCH is not declared.
0
almix77Author Commented:
Please look at this and let me know what I'm missing.... very confusing... you've been EXTREMELY helpful and cooperative.

Dim dsJT as New DataSet()
      Dim dsJTSteps as New DataSet()
      JTHeader.Fill(dsJT, "JT_JOINT_TRENCH")
      JTSteps.Fill(dsJTSteps, "JTJS_JOINT_TRENCH_JOB_STEP")
      
      dsJT.Merge(dsJTSteps)

      Dim parentCol as DataColumn
      Dim childCol as DataColumn
      
      parentCol = dsJT.Tables("JT_JOINT_TRENCH").Columns("jt_id")
      childCol = dsJT.Tables("JTJS_JOINT_TRENCH_JOB_STEPS").Columns("jtjs_jt_id")

      Dim relID as DataRelation
      relID = New DataRelation("Tickets",parentCol,childCol)
      dsJT.Relations.Add(relID)
      
      
      
      gridTickets.DataSource=dsJT
      gridTickets.DataBind()
      gridTickets.Visible = True
      
      End Sub
      
      Sub gridTickets_ItemDataBound(ByVal s As Object, ByVal e As DataGridItemEventArgs)

       If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
      
      dim drCurrentParent as DataRow
      dim drChildren as DataRow
      drCurrentParent = dsJT.Tables(JT_JOINT_TRENCH).Rows.Find(gridTickets.DataKeys(e.Item.ItemIndex))
      drChildren = drCurrentParent.GetChildRows(relID)
      
      jsDataList.DataSource = drChildren
      jsDataList.DataBind()
       End If


      End Sub
0
boulder_bumCommented:
In this case, there's no need to merge the data because you already have all the data you want in independant tables (and you want to keep it that way so you can look up child records). You probably want something like this:

     'this will hold your two tables
     Dim dsHolder AS New DataSet()

     'make the two tables seperately
     Dim dtJT as New DataTable()
     Dim dtJTSteps as New DataTable()

     'fill the data
     JTHeader.Fill(dtJT, "JT_JOINT_TRENCH")
     JTSteps.Fill(dtJTSteps, "JTJS_JOINT_TRENCH_JOB_STEP")

    'add the newly filled tables to the DataSet
     dsHolder.Tables.Add( dtJT )
     dsHolder.Tables.Add( dtJTSteps )

    'get columns to establish relation
     Dim parentCol AS DataColumn = dtJT.Columns("jt_id")
     Dim childCol AS DataColumn = dtJTSteps.Columns("jtjs_jt_id")
   
    'set primary key for the parent table
    Dim primaryKeyColumn() AS DataColumn = { parentCol }
    dtJT.PrimaryKey = primaryKeyColumn

    'establish relation
     Dim relID as DataRelation = New DataRelation("Tickets",parentCol,childCol)
     dsHolder.Relations.Add( relID )

'*********This block will actually go in the ItemDataBound event, but you want to make sure you set up the above first*********
    'this actually assumes you have a reference to dtJT, which you can make a member or something instead of a local variable
    'in the section where you fill the data

    'look at the current row being bound  
    Dim drv As DataRowView = CType(e.Item.DataItem, DataRowView)

    'get a parent row
    Dim drCurrentParent AS DataRow = dtJT.Rows.Find(drv("PrimaryKeyColumnName"))

    'get children
    Dim drChildrenArray() AS DataRow = drCurrentParent.GetChildRows(relID)

    'etc.
   

     
0
almix77Author Commented:
How do I make dtJT available in the ItemDataBound event?  That is the issue I'm having...
0
almix77Author Commented:
The code you posted gives the following error on line:

JTHeader.Fill(dtJT, "JT_JOINT_TRENCH")

Error:  Compiler Error Message: BC30311: Value of type 'System.Data.DataTable' cannot be converted to 'System.Data.DataSet'
0
boulder_bumCommented:
Oopsie. Just don't include the table name.

I didn't actually test the code, so let me know if there are other problems...

To make dtJT available throughout the code, you can do something like:

'in member declarations
Private dtJT AS DataTable


'in method that binds
dtJT = new DataTable()
JTHeader.Fill( dtJT )
'bind data


'in ItemDataBound
'dtJT is available for use
0
almix77Author Commented:
New Error on the following line:

Line 136:      Dim drChildrenArray() as DataRow = drCurrentParent.GetChildRows(relID)
Line 137:      
Line 138:      jsDataList.DataSource = drChildrenArray
Line 139:      jsDataList.DataBind()
Line 140:       End If

Error:  Compiler Error Message: BC30456: 'DataSource' is not a member of 'System.Array'.

It also happens if I have it as drChildrenArray()
0
boulder_bumCommented:
Is jsDataList an Array? Replace jsDataList with whatever the name of the DataList (and make sure you're referencing the DataList of the current row).
0
almix77Author Commented:
Ah, I had an error in it ... I fixed that.. Now, I get the following error, the page pulls up, but when I hit "PULL Reports", it shows the following:

'column' argument cannot be null. Parameter name: column
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentNullException: 'column' argument cannot be null. Parameter name: column

Source Error:


Line 118:      dtJT.PrimaryKey = primaryKeyColumn
Line 119:
Line 120:      Dim relID as DataRelation = New DataRelation("Tickets",parentCol,childCol)
Line 121:      dsHolder.Relations.Add(relID)
Line 122:      
 
0
boulder_bumCommented:
The trouble is probably here:

     'get columns to establish relation
     Dim parentCol AS DataColumn = dtJT.Columns("jt_id")
     Dim childCol AS DataColumn = dtJTSteps.Columns("jtjs_jt_id")

Make sure the column names are correct and that they are present when you make the declaration. Hopefully you're using Visual Studio. If you are, put a breakpoint on those lines and examine the .Columns collection of both of the tables to see what's in them.
0
almix77Author Commented:
I'm not using Visual Studio... I wish...  So, i'm not sure what to do.
0
boulder_bumCommented:
At first glance, it appears that you aren't currently including jtjs_jt_id in the records returned for the child table.
0
almix77Author Commented:
Hey, I'm getting something to work, which is good... I appreciate all your help.  The displaying of  my information is off though.

Take a look at it here

http://www.njuns.com/njuns/njuns_home/report_test8.aspx

Just pick any state, any company and pull report... those factors are connected to the pull report yet.

Its not populating the columns of the children to the right of the parent like I'd like it to.
0
almix77Author Commented:
My datalist isn't showing up on my page... I'm not sure if it isn't finding anything in the ItemBound event or if it is my layout code...
0
almix77Author Commented:
Here is the code:

ItemBound Code:

Sub gridTickets_ItemDataBound(ByVal s As Object, ByVal e As DataGridItemEventArgs)
      dim jsDataList as DataList
      dim drCurrentParent as DataRow
      dim drChildrenArray() as DataRow
      dim ds As DataSet
      dim relID as DataRelation
      
      If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
      
      
      drCurrentParent = ds.Tables(0).Rows.Find(gridTickets.DataKeys(e.Item.ItemIndex))
      drChildrenArray = drCurrentParent.GetChildRows(relID)
      
      jsDataList.DataSource = drChildrenArray
      jsDataList.DataBind()
      
      End If


      End Sub

Display Code:

<asp:DataGrid id="gridTickets" datakeyfield="jt_id" runat="server" AutoGenerateColumns="False" CellPadding="2" HeaderStyle-BackColor="gray" HeaderStyle-ForeColor="White" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True">

<Columns>
<asp:BoundColumn HeaderText="Ticket Number" DataField="jt_id" />
<asp:BoundColumn HeaderText="Date Created" DataField="jt_crt_date" />
<asp:BoundColumn HeaderText="Date Updated" DataField="jt_updt_date" />
<asp:BoundColumn HeaderText="Required Date" DataField="jt_required_date" />
<asp:BoundColumn HeaderText="Priority" DataField="jt_priority" />
<asp:BoundColumn HeaderText="DOT" DataField="jt_dot" />
<asp:BoundColumn HeaderText="Status" DataField="jt_status" />
<asp:BoundColumn HeaderText="Construction Type" DataField="jt_construction_type" />
<asp:BoundColumn HeaderText="Coordinator" DataField="jt_coordinator_id" />
<asp:BoundColumn HeaderText="Engineer" DataField="jt_contact_name" />
<asp:BoundColumn HeaderText="Engineer Phone" DataField="jt_phone" />
<asp:BoundColumn HeaderText="Engineer Ext" DataField="jt_phone_ext" />
<asp:BoundColumn HeaderText="County" DataField="jt_cnty_id" />
<asp:BoundColumn HeaderText="Place" DataField="jt_place" />
<asp:BoundColumn HeaderText="Location" DataField="jt_location" />
<asp:TemplateColumn>
  <ItemTemplate>
      <asp:DataList id="jsDataList" runat="server" RepeatDirection="Horizontal" RepeatLayout="Table">
      <ItemTemplate>
      <table>
      <tr>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_no") %>
      </td>      
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_job_id") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_lots") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_job_date") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_updt_date") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_feet") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_job_step") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_mem_id") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_y_or_n") %>
      </td>
      <td>
      <b><%# DataBinder.Eval(Container.DataItem, "jtjs_remarks") %>
      </td>
      </ItemTemplate
      </asp:DataList>
  </ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>

0
boulder_bumCommented:
If you're not getting any errors, my first guess is that you're either not getting any child rows, or there's something off with the binding mapping.

To test for these conditions, you can write drChildrenArray.Length to the trace (or to the page somewhere), and add some simple text to the ItemTemplate of the DataList so you can see if it's trying to bind to the elements (you should see your text repeated for each item in the DataSource).

This is where a debugger would really come in handy as you'd be able to see exactly what's going on when you're trying to bind. You may want to consider getting an evaluation copy shipped: http://msdn.microsoft.com/vstudio/productinfo/trial/ or even try downloading Visual Web Dev Express 2005 (a free tool with a debugger): http://lab.msdn.microsoft.com/express/vwd/default.aspx. With the latter option, you'd have to reconfigure your site to use Framework 1.1 (only takes a minute), and re-compile later, but it would help you track down the problem.
0
boulder_bumCommented:
Did you ever get this taken care of?

I can probably sit down and rig a test case at home to use as a template if you're still running into issues.
0
almix77Author Commented:
No, I couldn't get it working.  Its almost like my datalist doesn't have anything in it because it doesn't pull it up at all.  I even tried to not nest it.
0
almix77Author Commented:
Did you get a chance to take a look at this?
0
boulder_bumCommented:
Okay, I sat down to slam out a demo and here's what I came up with. It's in C# so I could code quickly (I'm much more comfortable with it), so if you need me to "translate" something let me know. Notably, I switched from a parent row find to a DataView to filter the child data because I didn't get the binding to work properly with a DataRow array (which doesn't mean it's not possible per se).

It's tested and it works, you'll just have to apply the pattern to your page/data.


.aspx:

<%@ Page language="c#" Codebehind="NestedList.aspx.cs" AutoEventWireup="false" Inherits="TempWeb.NestedList" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
  <HEAD>
    <title>NestedList</title>
    <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" Content="C#">
    <meta name=vs_defaultClientScript content="JavaScript">
    <meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
  </HEAD>
  <body MS_POSITIONING="GridLayout">
      
    <form id="Form1" method="post" runat="server"><asp:DataGrid id=dgTest style="Z-INDEX: 102; LEFT: 40px; POSITION: absolute; TOP: 32px" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="Number" HeaderText="Number"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="Child List">
<ItemTemplate>
<asp:DataList id=dlChild runat="server" RepeatDirection="Horizontal">
<ItemTemplate>
<asp:Label id=lblLetter runat="server"><%# DataBinder.Eval( Container.DataItem, "String" ) %></asp:Label>
</ItemTemplate>
</asp:DataList>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>

     </form>
      
  </body>
</HTML>








code-behind:


using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace TempWeb
{
      /// <summary>
      /// Summary description for NestedList.
      /// </summary>
      public class NestedList : System.Web.UI.Page
      {
            protected System.Web.UI.WebControls.DataGrid dgTest;
      
            private DataTable dtParent;
            private DataTable dtChild;
            private DataSet holder;

            private void Page_Load(object sender, System.EventArgs e)
            {
                  dtParent = new DataTable();
                  dtParent.Columns.Add( new DataColumn( "Number", typeof( int ) ) );
                  dtParent.PrimaryKey = new DataColumn[]{ dtParent.Columns[0] };

                  dtChild = new DataTable();
                  dtChild.Columns.Add( new DataColumn( "Number", typeof( int ) ) );
                  dtChild.Columns.Add( new DataColumn( "String", typeof( string ) ) );

                  holder = new DataSet();
                  holder.Tables.Add( dtParent );
                  holder.Tables.Add( dtChild );

                  DataColumn parent = dtParent.Columns[0];
                  DataColumn child = dtChild.Columns[0];

                  holder.Relations.Add( parent, child );

                  PopulateParent( dtParent );
                  PopulateChild( dtChild );

                  dgTest.DataSource = dtParent;
                  dgTest.DataBind();
            }

            private void PopulateParent( DataTable dtParent )
            {
                  DataRow dr;

                  for( int i = 0; i < 5; i++ )
                  {
                        dr = dtParent.NewRow();
                        dr["Number"] = i;

                        dtParent.Rows.Add( dr );
                  }
            }

            private void PopulateChild( DataTable dtChild )
            {
                  DataRow dr;

                  for( int i = 0; i < 5; i++ )
                  {
                        for( int j = i; j < 5; j++ )
                        {
                              dr = dtChild.NewRow();
                              dr["Number"] = i;
                              dr["String"] = "test " + j.ToString();

                              dtChild.Rows.Add( dr );
                        }
                  }
            }

            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.dgTest.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dgTest_ItemDataBound);
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

            private void dgTest_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
            {
                  if( e.Item.ItemType == ListItemType.Item ||
                        e.Item.ItemType == ListItemType.AlternatingItem )
                  {
                        int key = (int)((DataRowView)e.Item.DataItem)["Number"];

                        DataRow keyRow = dtParent.Rows.Find( key );

                        DataView dv = new DataView( dtChild );
                        dv.RowFilter = "Number = " + key.ToString();

                        //DataRow[] childRows = keyRow.GetChildRows( holder.Relations[0] );
                        DataList dlChild = (DataList)e.Item.FindControl( "dlChild" );

                        dlChild.DataSource = dv; //childRows;
                        dlChild.DataBind();
                  }
            }
      }
}
0
boulder_bumCommented:
I just figured out how the bind to the DataRow array will work:

You just need to uncomment the lines I commented out, remove the DataView stuff, and change the template item of the list to the VB.NET equivalent of:

<asp:Label id=lblLetter runat="server"><%# ((System.Data.DataRow)Container.DataItem)["String"]  %></asp:Label>

0
almix77Author Commented:
I appreciate all your help but I really don't understand the code behind stuff and everything.  I wasn't working and don't have VisualStudio.Net, so its kind of hard for me to put it together like you did.

Thanks again.
0
boulder_bumCommented:
Happy to help.

If you're using the code-inside method, basically you just have to take all the functions that are in my code-behind and put them in a Sub inside your <script></script> blocks (as you've already done for the ItemDataBound event handler).

You also might consider just setting up a code-behind as it's a good habit to get into anyway (the code is more organized, and it's the coding method 99% of ASP.NET developers use). Basically you create a .vb file to hold the code, and let the .aspx file hold the GUI markup. In other words, you pretty much just move anything that would have gone in the server-side <script></script> blocks (like Subs) to the code-behind (just prepend the Subs with the word "Protected" which is an access modifier).

It's easier than it sounds, trust me. ;-)

Here's a quick word on compiling a code-behind:
http://authors.aspalliance.com/aldotnet/examples/compilerbasics.aspx

Note that the .dll library goes in the application's "bin" directory, which you'll need to create.

Here are the file templates:

This is the empty .aspx file. You don't really need several of the tags, and some attributes, but I don't think they'll hurt.

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Report_Test.aspx.vb" Inherits="VBtemp.Report_Test"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
  <head>
    <title>Report_Test</title>
    <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
    <meta name=vs_defaultClientScript content="JavaScript">
    <meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
  </head>
  <body MS_POSITIONING="GridLayout">

    <form id="Form1" method="post" runat="server">

        <!-- content (like DataGrid) goes here. -->
        <asp:DataGrid id=DataGrid1 runat="server"></asp:DataGrid>

    </form>

  </body>
</html>



Here is the .vb file template. I've added a Sub so you can see how it would look:

Public Class Report_Test
    Inherits System.Web.UI.Page

    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

    Private designerPlaceholderDeclaration As System.Object

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

   Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
             'do binding here
    End Sub

    Protected Sub Pull_Report(s as object, e as EventArgs)
             'code
    End Sub
End Class

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.