Problem with displaying query data in ".aspx" page

[JFKJr]

Hello everyone,

I have been trying to solve this problem from past two days. I am working for my project and stuck with this problem.

Please help me out, your help will be greatly appreciated.

Well, this is what I am trying to do:

1. I have following 3 tables:

a) Course
   - CourseID, int, autoincrement
   - CourseName, nvarchar(50)
   - WeeksPerCourse, int
   - ClassesPerWeek, int
 
b) Weeks
    - WeekID, int, autoincrement
    - WeekNumber, int
    - CourseID
 
c) ClassTopics
    - ClassTopicID, int, autoincrement
    - ClassNumber, int
    - TopicName, nvarchar(100)
    - WeekID, int

Each course may have multiple weeks and each week may have multiple class topics.

And, now I want to display data from the above mentioned tables as follows in my "Index.aspx" page.

int weeks = "Select * from Weeks Where CourseID=1" //Say CourseID =1
<table>
//I want to repeat <tr> upto the number of weeks that the CourseID "1" has in Weeks table
<tr>
//I want to repeat <td> upto the number of classes for each week with CourseID "1"  
<td> data </td> //I want to display "TopicName" of each class from "ClassTopics" table in between <td> cell
</tr>
</table>

Please kindly give me an idea on how to implement the above illustrated procedure.

Please let me know if I confused you.

I have tried with repeater control but unable to implement the above mentioned procedure.

Your help will be greatly appreciated. Thanks a million in advance.

[OuTCasT]

Are u using visual studio ??

[Frinavale]

Have you considered using a GridView to display the data?

[JFKJr]

Thanks for the replies.

I am using Visual Studio 2008 and C# to create/design ".aspx" pages.

After hours of tweaking, I have suceeded in implementing the above mentioned procedure using DataList, GridView and Repeater controls.

But, ran into another problem.

Your help/idea will be greatly appreciated.

Well, this is what I am trying to do:

1. I have following 3 tables:

a) Weeks 
    - WeekID, int, autoincrement 
    - WeekNumber, int 
    - CourseID 
 
b) CourseMaterials
    - CMFile_ID, int, autoincrement
    - CMFile_UID, uniqueidentifier
    - CMFile_Name1, nvarchar(Max)
    - CMFile_Name2, nvarchar(50)
    - CMFile_Content, image
    - CMContent_Type, nvarchar(50)
    - WeekID, int
 
c) CMLinks
    - CMLink_ID, int, autoincrement
    - CMLink_Name, nvarchar(50)
    - CMLink_URL, nvarchar(Max)
    - WeekID, int

2. "Index.aspx" page

<asp:DataList ID="CourseDataList" runat="server">
<ItemTemplate>
    <tr>
       <td><%#DataBinder.Eval(Container.DataItem, "WeekNumber")%></td>
       <td>
          <asp:Repeater ID="CMRepeater" runat="server" DataSource='<%#DataBinder.Eval(Container, "DataItem.WeekCMs")%>'>
          <ItemTemplate>
             <li><%#DataBinder.Eval(Container.DataItem, "CMFile_Name2")%></li>
          </ItemTemplate>
          </asp:Repeater>
       </td>
    </tr>
</ItemTemplate>
</asp:DataList>

3. "Index.aspx.cs" Page

private int courseID=1;
protected void Page_Load(object sender, EventArgs e)
{
   bindData();
}
private void bindData()
{
   SqlConnection con = new SqlConnection();
   con.ConnectionString = "" // Defined my connection string here
   con.Open();
 
   SqlCommand sql1 = new SqlCommand("Select * From Weeks Where CourseID='" + courseID + "'", con);
   sql1.CommandType = CommandType.Text;
   SqlDataAdapter adapter1 = new SqlDataAdapter(sql1);
   adapter1.TableMappings.Add("Table", "Weeks");
   DataSet ds = new DataSet("Weeks");
   adapter1.Fill(ds);
 
   SqlCommand sql2 = new SqlCommand("Select * From CourseMaterials", con);
   sql2.CommandType = CommandType.Text;
   SqlDataAdapter adapter2 = new SqlDataAdapter(sql2);
   adapter2.TableMappings.Add("Table", "CourseMaterials");
   adapter2.Fill(ds);
 
   DataColumn parent = ds.Tables["Weeks"].Columns["WeekID"];
   DataColumn child = ds.Tables["CourseMaterials"].Columns["WeekID"];
   ds.Relations.Add("WeekCMs", parent, child);
 
   CourseDataList.DataSource = ds;
   CourseDataList.DataBind();
 
   con.Close();
}

The above functionality is working perfectly fine. I am able to display each week "WeekNumber" and course materials in a course timeline.

But, I also want to display each week course links from the above "CMLinks" table by including the following line in the above repeater control at line #9 (in "index.aspx" page)

<li><%#DataBinder.Eval(Container.DataItem, "CMLink_Name")%></li>

Any thoughts/ideas on how to display course links along with course materials for each week in the course timeline?

Please help me out. Thanks a million in advance.