Using a GridView to display multiple sub items

boyslik

Hello ,
I'm a beginner in visual studio 2008 using c# language

I Have this task that I seem to be stuck in !

I've created a database using sql server express 2005 of 4 tables :"BOOKS , AUTHORS , CATEGORIES and PUBLISHERS ".

I've connected this database to my gridview in visual studio , it shows me all the books like this : " BOOK publisher , TITLE, CATEGORY "

the problem is that my task is to show what every author has in terms of books .
and I cant figure out how to do that !!!!

can u tell me if any of this code that I'm using is helpfull or not ???

if it is what's missing ???????????????????

Expand|Select|Wrap|Line Numbers

 
public partial class _Default : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

        string ConnectionString = "Data Source=-------;Initial Catalog=boyslik;Integrated Security=True";

        string selectSQL = "SELECT  [AUTHORID] FROM [AUTHORS] ";

        Console.WriteLine("AUTHOR ID"  );
 
    }

    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

    {

         if (!this.IsPostBack)

        {
 
            string ConnectionString ="Data Source=----------;Initial Catalog=boyslik;Integrated Security=True";

            string selectSQL = "SELECT [category], [title], [publisher] FROM [books JOIN AUTHORS] ORDER BY [title], [category], [publisher]" GROUP BY  author name;
 
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("boyslikCS");

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(selectSQL, con);

            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
 
            adapter.Fill(ds, "BOOKS ");
 
            GridView1.DataSource = ds;

            GridView1.DataBind();

        }

    }

}

tomasjons

Hi

Your grouping is not right like this.
What you want to do is to just list out your query without doing any grouping.

Here is an article wich might help

http://aspalliance.com/1268_CodeSnip...ew_Control.all

Frinavale

Hi Boyslik,

You cannot use a DataSet as a DataSource for a GridView. A DataSet could possibly contain more than one table and a GridView is designed in such a way to only display one table.

Your application requires that you display data from two tables....and the GridView control does not do this by default.

To display the data you need to display a table (with book information) within a table row (representing an author)....so, in your case, you're going to require a GridView (or Repeater) (for the book information) to be displayed with a GridView Row (displaying the author information).

Here's a simple example of how to do it (sorry it's using VB.NET). The example includes JavaScript that is used to expand the details section (the books section). I was using an example based on what someone else needed help with to form an answer for you and this is why you got some JavaScript "niceties" added to your answer. You don't have to keep it if you don't want to.

ASPX code:

Expand|Select|Wrap|Line Numbers

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>
 
    <script type="text/javascript">

        function ExpandCollapse(detailsSectionID) {

            var detailsSection = document.getElementById(detailsSectionID);

            if (detailsSection) {

                if (detailsSection.style.display == "none") {

                    detailsSection.style.display = "block";

                } else {

                    detailsSection.style.display = "none";

                }

            }

        }

        function ExpandAll() {

            var allDivElements = document.getElementsByTagName("div");

            var numElements = allDivElements.length;
 
            var pattern = new RegExp("ItemDetails")

            for (var i = 0; i < numElements; i++) {

                if (pattern.test(allDivElements[i].id)) {

                    allDivElements[i].style.display = "block";

                }

            }
 
        }
 
        function CollapseAll() {

            var allDivElements = document.getElementsByTagName("div");

            var numElements = allDivElements.length;
 
            var pattern = new RegExp("ItemDetails")

            for (var i = 0; i < numElements; i++) {

                if (pattern.test(allDivElements[i].id)) {

                    allDivElements[i].style.display = "none";

                }

            }
 
        }    

    </script>
 
</head>

<body>

    <form id="form1" runat="server">

    <asp:ScriptManager ID="SM" runat="server">

    </asp:ScriptManager>

    <div>

        <input type="button" id="expandAll" runat="server" onclick="ExpandAll();" value="Expand All" />

        <input type="button" id="collapseAll" runat="server" onclick="CollapseAll();" value="Collapse All" />

        <asp:GridView ID="ItemsGrid" runat="server" AutoGenerateColumns="false">

            <Columns>

                <asp:TemplateField HeaderText="Expand Details">

                    <ItemTemplate>

                        <asp:HyperLink ID="ExpandCollapse" runat="server" Text="+/-"></asp:HyperLink>

                    </ItemTemplate>

                </asp:TemplateField>

                <asp:TemplateField HeaderText="Items">

                    <ItemTemplate>

                        <div style="width: 100%; text-align: center">

                            <%#Eval("authorName")%></div>

                        <asp:Panel ID="ItemDetails" runat="server" Style="display: none;">

                            <asp:GridView ID="SubGridView" runat="server" AutoGenerateColumns="false">

                                <Columns>

                                    <asp:BoundField DataField="authorID" HeaderText="authorID" />

                                    <asp:BoundField DataField="bookName" HeaderText="bookName" />

                                </Columns>

                            </asp:GridView>

                        </asp:Panel>

                    </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

    </div>

    </form>

</body>

</html>

VB.NET Code:

Expand|Select|Wrap|Line Numbers

 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
   'Since I don't have a database to connect to....

   'Here I'm creating 2 tables: one to represent the Authors,

   'and one to represent the author's books.

   'These two tables are related: on the Author's ID 

   '(the primary key for the authors table)
 
        Dim authorTable As New DataTable

        authorTable.Columns.Add("authorID") 'Primary key for author's table

        authorTable.Columns.Add("authorName")
 
        Dim booksTable As New DataTable

        booksTable.Columns.Add("bookName")

        booksTable.Columns.Add("authorID") 'Foriegn key
 
    'Here I'm populating the Authors table and the table that holds the Books

    'I'm doing in such a way that each author will have 2 books

        For i As Integer = 0 To 5
 
            Dim authorTableDR As DataRow

            authorTableDR = authorTable.NewRow

            authorTableDR("authorName") = "author" + i.ToString

            authorTableDR("authorID") = "authorID" + i.ToString

            authorTable.Rows.Add(authorTableDR)
 
            Dim booksTableDr As DataRow

            booksTableDr = booksTable.NewRow

            booksTableDr("authorID") = "authorID" + i.ToString

            booksTableDr("bookName") = "bookName" + i.ToString
 
            Dim booksTableDr2 As DataRow

            booksTableDr2 = booksTable.NewRow

            booksTableDr2("authorID") = "authorID" + i.ToString

            booksTableDr2("bookName") = "bookName" + i.ToString + "a"
 
            booksTable.Rows.Add(booksTableDr)

            booksTable.Rows.Add(booksTableDr2)

        Next
 
    'Here I'm defining the relationship between the two tables
 
        Dim Parent_PKColumns(0) As DataColumn

        Parent_PKColumns(0) = authorTable.Columns("authorID")

        authorTable.PrimaryKey = Parent_PKColumns
 
        Dim Child_FKColumns(0) As DataColumn

        Child_FKColumns(0) = booksTable.Columns("authorID")
 
   'I'm adding the tables to a DataSet

        Dim ds As New DataSet

        ds.Tables.Add(authorTable)

        ds.Tables.Add(booksTable)
 
   'I'm naming the relationship between the two tables so that 

   'I can access it later

        ds.Relations.Add("ParentChild", Parent_PKColumns, Child_FKColumns)

   'I'm setting the main GridView to the Authors Table in the DataSet

   'and I'm binding the GridView to the data.

        ItemsGrid.DataSource = ds.Tables(0)

        ItemsGrid.DataBind()

    End Sub
 
    Private Sub ItemsGrid_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles ItemsGrid.RowDataBound
 
   'The following is used to apply the JavaScript responsible

   'expanding details

        Dim expandCollapseLink As HyperLink = CType(e.Row.FindControl("ExpandCollapse"), HyperLink)

        Dim detailsSection As Panel = CType(e.Row.FindControl("ItemDetails"), Panel)

        If expandCollapseLink IsNot Nothing AndAlso detailsSection IsNot Nothing Then

            expandCollapseLink.Attributes.Add("onclick", "ExpandCollapse('" + detailsSection.ClientID + "');")

        End If
 
    'Ok this is important to you: I'm retrieving the child GridView used

    'to display the author's books information

        Dim subGridView = CType(e.Row.FindControl("SubGridView"), GridView)
 
    'If I was able to retrieve the GridView, then I'm binding it to a

    'child view created based on the relationship I defined above.

        If subGridView IsNot Nothing Then

            subGridView.DataSource = CType(e.Row.DataItem, DataRowView).CreateChildView("ParentChild")

            subGridView.DataBind()

        End If
 
    End Sub

If you have any questions feel free to ask....but before you say "I need an answer in C#" try opening a new project (VB.NET), add this code, step through it, and read my comments to try understand what I'm doing.

-Frinny

Using a GridView to display multiple sub items

re: Using a GridView to display multiple sub items

re: Using a GridView to display multiple sub items

Similar ASP.NET bytes

/bytes/development

/bytes/it

/bytes/about