Visual Studio .NET - How to show data in parent table and child table in Gridview or datalist.

Asked By azhar rahi on 22-Aug-08 03:53 PM

I am working in ASP.Net 2.0.

I have three tables in Database which are inter-linked by foriegn keys. 

1. Portfolio (PortfoilioID,PortfolioTitle,PortfolioDescription)

2. SubPortfolio (SubPortfolioID, SubPortfolioTitle, SubPortfolioDescription, PortfolioID)

3. SubPortfolioDetails (SubPortfolioDetailID, SubPortfolioDetail, SubPortfolioID)

Now the Primary key of Portfolio is foreign key in SubPortfolio, while  Primary Key of SubPortfolio is foreign key in SubPortfolioDetails.

Now I want to show data on the form such that in the first row, then show the data of SubPortfoloio related to Portfolio. And make the Subportfolio data a hyperlink. and then show the data of SubPortfolioDetails related to SubPortfolio on next page when click on the SubPortfolio Hyperlink.

Such that:

1. Portfolio Title+  Description 

SubPortfolio Title (Hyperlink)

2. Portfolio Title+ Description

SubPortfolio Title (Hyperlink)

and so on .

It just like datalist or gridview.

How can I do it. Any Idea please?

Try this - ram kumar replied to azhar rahi on 22-Aug-08 08:59 PM

Hi,

Populating the GridView Control:

The first step is to populate the GridView control. We will use the Northwind database available in SQL SERVER 2000. Here is the code that is used to populate the GridView control.

private void BindData()
        {
            string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
            SqlConnection myConnection = new SqlConnection(connectionString);
            SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Categories", myConnection);
            DataSet ds = new DataSet();
            ad.Fill(ds);

            gvCategories.DataSource = ds;
            gvCategories.DataBind();
        }

Here is the code for the ASPX page which contains the GridView control. I have removed the style attributes for clarity.

<asp:GridView ID="gvCategories" runat="server" AutoGenerateColumns="False"
>
 
    <Columns>  
      
     <asp:TemplateField>
    <ItemTemplate>
    <input type="checkbox" id='chkSelect_<%# Container.DataItemIndex + 1 %>' onclick='getProducts("chkSelect_<%# Container.DataItemIndex + 1 %>",<%# Eval("id") %>,"divDetail_<%# Container.DataItemIndex + 1 %>")' />
    <%# Eval("CategoryName") %>
   
    <div style="display:none;" id='divDetail_<%# Container.DataItemIndex + 1 %>'>
   
    </div>  
   
    </ItemTemplate>
    </asp:TemplateField>
   
    </Columns>   
    </asp:GridView>

As, you can see there is only have a single column inside the GridView control which is a TemplateField column. Inside the TemplateField column we display the checkbox and data from the column “CategoryName”.

The screenshot below shows how the GridView will look like once it is populated.


There is a lot more happening inside the GridView column. First, the checkbox is created with a unique “ID” to make sure which checkbox is clicked. The binding expression <%# Container.DataItemIndex + 1 %> returns an incremental row number for the GridView rows. The checkbox also exposes an “onclick” function which takes three parameters. The first parameter is the “id” of the checkbox, the second parameter is the primary key of the database table which in this case is “id”. And the final parameter is the “id” of the DIV element which is used to display the nested/child data.

<input type="checkbox" id='chkSelect_<%# Container.DataItemIndex + 1 %>' onclick='getProducts("chkSelect_<%# Container.DataItemIndex + 1 %>",<%# Eval("id") %>,"divDetail_<%# Container.DataItemIndex + 1 %>")' />

<div style="display:none;" id='divDetail_<%# Container.DataItemIndex + 1 %>'>


The JavaScript Function:

Let’s take a look at the getProducts function which is fired whenever a checkbox is checked inside the GridView control.

function getProducts(chkSelectId,categoryId,detailDivId)
{   
    if(document.getElementById(chkSelectId).checked)
    {

    DemoJQueryWebApps._Default.getProducts(categoryId,function(response)
    {
         $(document.getElementById(detailDivId)).show("slow");          
                 
         document.getElementById(detailDivId).innerHTML = response.value;
    }
    );
    
    }
   
    else
    {
        $(document.getElementById(detailDivId)).hide("slow");
    }
}

Inside the function we check if the checkbox is checked or unchecked. If it is checked then we make an asynchronous call to the server side method and retrieve the child data else we hide the nested data.

We have also used little bit of JQuery to create nifty animation effects.

Server Side Method:

The server side is responsible for pulling the data out of the database table. The method named is “getProducts”.

[AjaxPro.AjaxMethod]
        public string getProducts(int categoryId)
        {
            NorthwindDataContext northwind = new NorthwindDataContext();                    

            var products = from p in northwind.Products
                           where p.CategoryID == categoryId
                           select p;

            Table table = null;

            if (products.Count() > 0)
            {
                table = new Table();

                foreach (var product in products)
                {
                    TableRow row = new TableRow();
                    TableCell cell = new TableCell();
                    cell.Text = product.ProductName;
                    row.Cells.Add(cell);
                    table.Rows.Add(row);
                }
            }

            if (table != null)
                return ConvertControlToHTML(table);
            else return "No records found!";
        }   

We have also used LINQ to Classes to make our dynamic DAL layer. The products are retrieved based on the categoryID and are inserted in a dynamically generated HTML table. Finally, the table is converted to HTML using the custom ConvertControlToHTML method and returned to the client.


use relations.add,base on primary key - pravin kumar S replied to azhar rahi on 23-Aug-08 01:26 AM

objConn.Open()

Dim dset As New DataSet

Dim strCustomers As String = "select * from employee"

Dim strOrders As String = "select * from customer"

Dim dadapter As New SqlDataAdapter(strCustomers, objConn)

dadapter.Fill(dset, "emp")

dadapter = New SqlDataAdapter(strOrders, objConn)

dadapter.Fill(dset, "cus")

dset.Relations.Add("Customer Orders", dset.Tables("employee").Columns("emp_no"), dset.Tables("customer").Columns("emp_no"))

DataGrid1.DataSource = dset.Tables("employee")


objConn.Close()

See this - Sagar P replied to azhar rahi on 23-Aug-08 02:48 AM

You can acheive this by using gridview inside a gridview. So just try to do something like that.

Just take a gridview and add columns into it like;

Portfolio Title , Desc and hyperlink of subportfolio;

write this query to fill gridview;

select P.PortfolioTitle,P.PortfolioDescription,S.SubPortfolioTitle From Portfolio P, SubPortfolio S where P.PortfoilioID=S.PortfoilioID;

Now take another gridview inside tihs gridview, and when we click on hyperlink show the data of SubPortfolioDetails into that.

and so on..................

Here is the perfect example of the same. Just go thr this.

This will solve your problem;

http://www.codeproject.com/KB/webforms/GridViewInsideGridView.aspx

http://www.codeproject.com/KB/aspnet/SkinSample.aspx

http://www.vbdotnetheaven.com/UploadFile/nikhil_be_it/GridViewInsideGridView05232006004636AM/GridViewInsideGridView.aspx

http://www.codeguru.com/columns/vb/article.php/c12647/

Best Luck!!!!!!!!!!
Sujit.

But I want to show data from above to below manner - azhar rahi replied to Sagar P on 23-Aug-08 04:09 PM
I want to show data in the manner as presented in following manner:
http://www.fruchtmaneng.com/Pages/Portfolio.php
here
'Failure Analysis, Forensic Engineering & Insurance Investigations'
is title of Portfolio.
'Due to the sensitivity of legal cases, a partial case list is provided rather than a comprehensive list.'
is description of Portfolio.
While
'http://www.fruchtmaneng.com/Portfolio/partial-case-list.php'
is SubPortfolio.
Now I want to present data in Master-Child relation in same manner, from above to below. What you have given me the links are in left to right manner in a column of gridview.

I think I can use datalist into a datalist or gridview into datalist. Or else?
Use this code... - Atul Shinde replied to azhar rahi on 25-Aug-08 12:36 AM
 

objConn.Open()

Dim dset As New DataSet

Dim strCustomers As String = "select * from employee"

Dim strOrders As String = "select * from customer"

Dim dadapter As New SqlDataAdapter(strCustomers, objConn)

dadapter.Fill(dset, "emp")

dadapter = New SqlDataAdapter(strOrders, objConn)

dadapter.Fill(dset, "cus")

dset.Relations.Add("Customer Orders", dset.Tables("employee").Columns("emp_no"), dset.Tables("customer").Columns("emp_no"))

DataGrid1.DataSource = dset.Tables("employee")


objConn.Close()

Try this... - Atul Shinde replied to azhar rahi on 25-Aug-08 12:37 AM

You can acheive this by using gridview inside a gridview. So just try to do something like that.

Just take a gridview and add columns into it like;

Portfolio Title , Desc and hyperlink of subportfolio;

write this query to fill gridview;

select P.PortfolioTitle,P.PortfolioDescription,S.SubPortfolioTitle From Portfolio P, SubPortfolio S where P.PortfoilioID=S.PortfoilioID;

Now take another gridview inside tihs gridview, and when we click on hyperlink show the data of SubPortfolioDetails into that.

and so on..................

Here is the perfect example of the same. Just go thr this.

This will solve your problem;

http://www.codeproject.com/KB/webforms/GridViewInsideGridView.aspx

See this - Sagar P replied to azhar rahi on 25-Aug-08 04:58 AM

You can write some logic for this. Like you can add a table and in that you can add gridviews. Like in first row one gridview which will fill details, and in second take another one. Do something like this. Or i think you can use simple table also to show it like you want.

You can acheive it by using grid inside grid in the row like.

You can also go for a Expandable gridview.

Just go thr these links.

http://www.denisbauer.com/ASPNETControls/HierarGrid.aspx

http://www.codeproject.com/KB/custom-controls/extgridview.aspx

Best Luck!!!!!!!!!!!!!!!!!
Sujit.