Display values based on DayId

Question

0.00/5 (No votes)

See more:

I want to read subject names based on that dayanames.If values not present in that table at that place i want to display 'Na'..

I Want In This form:
MonDay-English
TueDay-Na
WedDay-Na
ThuDay-Science
FriDay-Na
SatDay-Na

I written this code for that:

C#

protected void Button1_Click(object sender, EventArgs e)
   {
       try
       {
           lblTable.ForeColor = System.Drawing.Color.Green;
           lblTable.Text = "This Is " + dlClass.SelectedValue + " TimeTable";
           DataSet ds = new DataSet();
           DataTable dt = new DataTable();
           dt.Columns.Add("DayName");
           dt.Columns.Add("Hour1");
           dt.Columns.Add("Hour2");
           dt.Columns.Add("Hour3");
           dt.Columns.Add("Hour4");
           List<string> Hour1 = new List<string>();
           SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ToString());
           con.Open();
           SqlCommand cmd = new SqlCommand("SELECT Subjects.SubName,DayTable.DayName FROM ClassRoom INNER JOIN Cst ON ClassRoom.ClassId = Cst.ClassId INNER JOIN newTimeTable ON Cst.CstId = newTimeTable.CstId INNER JOIN ClassHours ON newTimeTable.HourId = ClassHours.HourId INNER JOIN Subjects ON Cst.SubjectId = Subjects.SubjectId INNER JOIN DayTable ON newTimeTable.DayId = DayTable.DayId where HourName='1st Hour' and ClassName='" + dlClass.SelectedValue + "' ORDER BY DayTable.DayId", con);
           SqlDataReader dr = cmd.ExecuteReader();
           for (int i = 0; i < 6; i++)
           {
               if (dr.Read() == true)
               {
                   Hour1.Add(Convert.ToString(dr["SubName"]));
               }
               else
               {
                   Hour1.Add("Na");
               }
           }
           dr.Close();

           List<string> Hour2 = new List<string>();
           SqlCommand cmd1 = new SqlCommand("SELECT Subjects.SubName, DayTable.DayName FROM ClassRoom INNER JOIN Cst ON ClassRoom.ClassId = Cst.ClassId INNER JOIN newTimeTable ON Cst.CstId = newTimeTable.CstId INNER JOIN ClassHours ON newTimeTable.HourId = ClassHours.HourId INNER JOIN DayTable ON newTimeTable.DayId = DayTable.DayId INNER JOIN Subjects ON Cst.SubjectId = Subjects.SubjectId where ClassName='" + dlClass.SelectedValue + "' and HourName='2nd Hour' ORDER BY  DayTable.DayId", con);
           SqlDataReader dr1 = cmd1.ExecuteReader();
           for (int i = 0; i < 6; i++)
           {
               if (dr1.Read() == true)
               {
                   Hour2.Add(Convert.ToString(dr1["SubName"]));
               }
               else
               {
                   Hour2.Add("Na");
               }
           }
           dr1.Close();
           List<string> Hour3 = new List<string>();
           SqlCommand cmd2 = new SqlCommand("SELECT Subjects.SubName,DayTable.DayName FROM ClassRoom INNER JOIN Cst ON ClassRoom.ClassId = Cst.ClassId INNER JOIN newTimeTable ON Cst.CstId = newTimeTable.CstId INNER JOIN ClassHours ON newTimeTable.HourId = ClassHours.HourId INNER JOIN Subjects ON Cst.SubjectId = Subjects.SubjectId INNER JOIN DayTable ON newTimeTable.DayId = DayTable.DayId where HourName='3rd Hour' and ClassName='" + dlClass.SelectedValue + "' ORDER BY DayTable.DayId", con);
           SqlDataReader dr2 = cmd2.ExecuteReader();
           for (int i = 0; i < 6; i++)
           {
               if (dr2.Read() == true)
               {
                   Hour3.Add(Convert.ToString(dr2["SubName"]));
               }
               else
               {
                   Hour3.Add("Na");
               }
           }

           dr2.Close();
           List<string> Hour4 = new List<string>();
           SqlCommand cmd3 = new SqlCommand("SELECT Subjects.SubName,DayTable.DayName FROM ClassRoom INNER JOIN Cst ON ClassRoom.ClassId = Cst.ClassId INNER JOIN newTimeTable ON Cst.CstId = newTimeTable.CstId INNER JOIN ClassHours ON newTimeTable.HourId = ClassHours.HourId INNER JOIN Subjects ON Cst.SubjectId = Subjects.SubjectId INNER JOIN DayTable ON newTimeTable.DayId = DayTable.DayId where HourName='4th Hour' and ClassName='" + dlClass.SelectedValue + "' ORDER BY DayTable.DayId", con);
           SqlDataReader dr3 = cmd3.ExecuteReader();
           for (int i = 0; i < 6; i++)
           {
               if (dr3.Read() == true)
               {
                   Hour4.Add(Convert.ToString(dr3["SubName"]));
               }
               else
               {
                   Hour4.Add("Na");
               }
           }
           dr3.Close();

But this code does not display values based on DayId...
It Display all Read Values first and after That Na Value.. But It Doesnot Have any proper DayOredr Basis...

Now my output is like this:
MonDay-English
TuesDay-Science
WedDay-Na
ThuDay-Na
FriDay-Na
SatDay-Na

Pls Help me anyone.

Posted 22-Apr-12 23:16pm

shwetha1

Updated 22-Apr-12 23:31pm

Sandeep Mewara

v2

Add a Solution

Comments

[no name] 23-Apr-12 9:09am

What is it that you think "ORDER BY" does?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VJ Reddy · Answer 1 · 2012-04-23T23:55:00

From the question it appears that the ClassRoom, Subjects, DayTable and ClassHours are basically used as look up tables to get the full description of the corresponding id. The Cst and newTimeTable are the main data tables for constructing the time table as required. The DataTables can be populated from the database and then the LINQ to DataSet can be used to create the Time Table as shown below

C#

void Main()
{
    //The data tables are populated from the database
    //The datatables and sample data are created here to run the example
    DataTable cst = new DataTable();
    cst.Columns.Add("CstId",typeof(string),null);
    cst.Columns.Add("ClassId",typeof(string),null);
    cst.Columns.Add("SubjectId",typeof(string),null);
    
    DataTable newTimeTable = new DataTable();
    newTimeTable.Columns.Add("Id",typeof(string),null);
    newTimeTable.Columns.Add("CstId",typeof(string),null);
    newTimeTable.Columns.Add("HourId",typeof(string),null);
    newTimeTable.Columns.Add("DayId",typeof(string),null);
    
    cst.Rows.Add("cs1","C1","Eng");
    cst.Rows.Add("cs2","C1","Sci");
    cst.Rows.Add("cs3","C1","Mat");
    cst.Rows.Add("cs1","C2","Eng");
    cst.Rows.Add("cs2","C2","Sci");
    cst.Rows.Add("cs3","C2","Mat");
    
    DataTable subjects = new DataTable();
    subjects.Columns.Add("SubjectId",typeof(string),null);
    subjects.Columns.Add("SubjectName",typeof(string),null);
    subjects.PrimaryKey = new DataColumn[]{subjects.Columns[0]};
    
    subjects.Rows.Add("Eng","English");
    subjects.Rows.Add("Sci","Science");
    subjects.Rows.Add("mat","Mathematics");
    
    
    newTimeTable.Rows.Add("N1","cs1", "Hour1","D1");
    newTimeTable.Rows.Add("N2","cs2","Hour2","D4");
    newTimeTable.Rows.Add("N3","cs3","Hour4","D2");
    
    DataTable classTimeTable = new DataTable();
    classTimeTable.Columns.Add("DayId",typeof(string),null);
    classTimeTable.Columns.Add("DayName",typeof(string),null);
    classTimeTable.Columns.Add("Hour1",typeof(string),null);
    classTimeTable.Columns.Add("Hour2",typeof(string),null);
    classTimeTable.Columns.Add("Hour3",typeof(string),null);
    classTimeTable.Columns.Add("Hour4",typeof(string),null);
    classTimeTable.Columns[1].DefaultValue="";
    classTimeTable.Columns[2].DefaultValue="Na";
    classTimeTable.Columns[3].DefaultValue="Na";
    classTimeTable.Columns[4].DefaultValue="Na";	
    classTimeTable.Columns[5].DefaultValue="Na";	
    classTimeTable.Rows.Add("D1","Monday");
    classTimeTable.Rows.Add("D2","Tuesday");
    classTimeTable.Rows.Add("D3","Wednesday");
    classTimeTable.Rows.Add("D4","Thursday");
    classTimeTable.Rows.Add("D5","Friday");
    classTimeTable.Rows.Add("D6","Saturday");
    classTimeTable.PrimaryKey= new DataColumn[]{ classTimeTable.Columns[0]};
    		
    //Find the Subject corresponding to the concerned hour and day
    //and populate the classTimeTable DataTable
    var ct = cst.AsEnumerable().Where (c =>c.Field<string>("ClassId")=="C1" ).
        Join(newTimeTable.AsEnumerable(), cs => cs.Field<string>("CstId"),
    	nt => nt.Field<string>("CstId"),
    	(cs, nt) => {DataRow row = classTimeTable.Rows.Find(nt.Field<string>("DayId"));
  	row[nt.Field<string>("HourId")]=
            subjects.Rows.Find(cs.Field<string>("SubjectId")).
            Field<string>("SubjectName");
    	return row;}).Count ();
    for(int i=1; i<6; i++)
    {
    	Console.Write("{0,-20}",classTimeTable.Columns[i].ColumnName);
    }
    
    Console.WriteLine ("\n{0}",new string('-',80));
    foreach(DataRow row in classTimeTable.Rows){
    	for(int i=1; i<6; i++)
    		Console.Write("{0,-20}",row[i].ToString());
    	Console.WriteLine ();
    }
}
//DayName             Hour1               Hour2               Hour3               Hour4               
//-------------------------------------------------------------------------------------------
//Monday              English             Na                  Na                  Na                  
//Tuesday             Na                  Na                  Na                  Mathematics         
//Wednesday           Na                  Na                  Na                  Na                  
//Thursday            Na                  Science             Na                  Na                  
//Friday              Na                  Na                  Na                  Na                  
//Saturday            Na                  Na                  Na                  Na

Guirec · Answer 2 · 2012-04-23T14:48:00

Your loop for populating the List<> of hours is not doing what you are aiming at.

let's just focus on your fist Command...
If this commands brings 2 rows then the code will add these 2 rows at the beginning of your list (dr.read() is successful) then the 4 subsequent attempts to dr.Read() will fail and you will add NA to the end of your list.

You should consider create the List<> with 6 entries before doing the query.
something like:

C#

List<string> Hour1 = new List<string>()
for (int i = 0; i < 6; i++) Hour1.Add("Na");

// then execute your query
// and...
while(dr.Read())
{
     Hour1[int.Parse(dr["DayId"])] = dr["SubName"];
}
// should work if "DayId" is an int between 0 to 6</string></string>

Display values based on DayId

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0