Click here to Skip to main content
14,982,024 members
Please Sign up or sign in to vote.
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
Updated 22-Apr-12 23:31pm
v2
Comments
[no name] 23-Apr-12 9:09am
   
What is it that you think "ORDER BY" does?

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 
   
v4
Comments
shwetha1 24-Apr-12 6:24am
   
Its nice but it display values statically...
I want to Select values programmatically from that tables and bind those values into gridview....
VJ Reddy 24-Apr-12 7:36am
   
The data tables can be populated from database in normal way. I think the following Code Project article may be helpful.
http://www.codeproject.com/Articles/341274/General-purpose-class-to-fill-DataTable-s-from-Dat
After populating the DataTables, the LINQ query can be used to create the TimeTable DataTable as shown.
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>
   
Comments
shwetha1 24-Apr-12 4:35am
   
But It will Display error like this:

Error 3 The best overloaded method match for 'int.Parse(string)' has some invalid arguments...

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900