Displaying Table Data in Calendar Asp.net C#

Question

I want to display time intime out & status columns into Calendar.... This is the Back-Code for calendar: protected void Calendar1_DayRender ( object sender  DayRenderEventArgs e ) { DataSet ds = GetData (); //string link =  string s = e . Day . Date . ToShortDateString (); e . Cell . Text = e . Day . Date . Day . ToString () +   ; LiteralControl l = new LiteralControl (); l . Text = e . Day . Date . Day . ToString () +   ; e . Cell . Controls . Add ( l ); foreach ( DataRow row in ds . Tables [ 0 ]. Rows ) { string Transaction_date = Convert . ToDateTime ( row [ transaction_date ]). ToShortDateString (); if ( Transaction_date . Equals ( s )) { LinkButton lb = new LinkButton (); lb . Text = IN: + row [ actual_time_in ] as String +   + row [ actual_time_out ] as String + row [ status ] as String +   +   ; e . Cell . Controls . Add ( lb ); } } private DataSet GetData () { SqlConnection myConnection = new SqlConnection ( ConfigurationManager . ConnectionStrings [ conString ]. ConnectionString ); SqlDataAdapter ad = new SqlDataAdapter ( CalendarAttendance  myConnection ); DataSet ds = new DataSet (); ad . Fill ( ds ); return ds ; } This is the stored Procedure:CalendarAttendance ALTER PROCEDURE [dbo].[CalendarAttendance] @Employeeid int @status varchar(5) @transaction_date datetime @actual_time_in datetime @actual_time_out datetime AS BEGIN SET NOCOUNT ON ; SELECT actual_time_in = @actual_time_in  actual_time_out = @actual_time_out  status = @status FROM tblschedule where Employeeid = @Employeeid END Im getting an error : Procedure or function 'CalendarAttendance' expects parameter '@Employeeid' which was not supplied. I want the Calendar to display time_intime_out & status according to respective days and of that employee who has logged in. Plz correct the code & Stored Procedure wherever necessary..

ali tuncer · Answer

First thing you need to make sureyouget desired data from stored procedure I don't have your database so you need tofindout. That's why you need to check if your dataset has values in it..You can put a breakpoint.. for examplethis code : foreach (DataRow row in ds.Tables[0].Rows) { LinkButton lb = new LinkButton(); // your code } if you dataset is empty you won't go inside foreach...but just by looking at the code I won't know this..that's why you need to check..

Viraj Kawthankar · Answer

@AliTuncer.... U know what i need & I have posted the code & procedure above could you rectify where i can make changes by just modifying my code where changes are expected Here's my updated Code : private DataSet GetData() { SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionString].ConnectionString); SqlDataAdapter da = new SqlDataAdapter(CalendarAttendance myConnection); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add(@Employeeid SqlDbType.Int).Value = 52674; //here you pass employee id DataSet ds = new DataSet(); da.Fill(ds); return ds; } protected void Calendar1_DayRender(object sender DayRenderEventArgs e) { DataSet ds = GetData(); //string link =  string s = e.Day.Date.ToShortDateString(); e.Cell.Text = e.Day.Date.Day.ToString() +  ; LiteralControl l = new LiteralControl(); l.Text = e.Day.Date.Day.ToString() +  ; e.Cell.Controls.Add(l); foreach (DataRow row in ds.Tables[0].Rows) { string Transaction_date = Convert.ToDateTime(row[transaction_date]).ToShortDateString(); if (Transaction_date.Equals(s)) { LinkButton lb = new LinkButton(); lb.Text = IN: + row[actual_time_in] as String +   + OUT: + row[actual_time_out] as String +   + Status: + row[status] as String +   +  ; e.Cell.Controls.Add(lb); } } And the transacation_date is the column from where the dates will be retrieved and the data will be posted on that date in the cell of calendar grid... So i need this field for displaying time_intime_out & status... Plz have a look at the code & make modifications ...Thanks in advance :)

ali tuncer · Answer

Why do you need to pass time_intime_outstatus? I think you only need to pass employee id as parameter and you will retrieve his/her time_in time_outstatus Isn't that right? If you wanted to show it for every employee in the table in that case you wouldn't need to pass employee id.. I guess you keep Employee id in a session when a user logs in the system.. Don't pass transaction_date as a parameterit sounds like you don't have a column named transaction_date.

Viraj Kawthankar · Answer

Hi @alituncer .... I have lots of employees say 1000 so how do i pass their time_intime_out & status according to their session: da.SelectCommand.Parameters.Add(@Employeeid SqlDbType.Int).Value = 52674; So how do i write the code above...Correct it & one more thing im getting this error now: Column 'transaction_date' does not belong to table Table .Now which table it is referring to... I;ve given the connection string [connectionString] Which i've passed in web.config: My table's name is tblschedule which has the 3 columns time intime out & status which i need to display

ali tuncer · Answer

for demonstration purpose I passed '1' for employee id you are still passing 1 are you sure you have record for employeeid=1 in your database? in tblschedule table..

Viraj Kawthankar · Answer

yes... im providing the GetData() method to the data set 'ds' which has the connection string connected to my database through web config : private DataSet GetData() { SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[conString].ConnectionString); SqlDataAdapter da = new SqlDataAdapter(CalendarAttendance myConnection); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add(@Employeeid SqlDbType.Int).Value = 1; //here you pass employee id da.SelectCommand.Parameters.Add(@status SqlDbType.Int).Value = 1; da.SelectCommand.Parameters.Add(@actual_time_in SqlDbType.Int).Value = 1; da.SelectCommand.Parameters.Add(@actual_time_out SqlDbType.Int).Value = 1; da.SelectCommand.Parameters.Add(@transaction_date SqlDbType.Int).Value = 1; DataSet ds = new DataSet(); da.Fill(ds); return ds; }

ali tuncer · Answer

Do you have data in your dataset (ds) ?

Viraj Kawthankar · Answer

Hello @ali tuncer  i have passed all parameters as u said : private DataSet GetData() { SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[conString].ConnectionString); SqlDataAdapter da = new SqlDataAdapter(CalendarAttendance myConnection); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add(@Employeeid SqlDbType.Int).Value = 1; //here you pass employee id da.SelectCommand.Parameters.Add(@status SqlDbType.Int).Value = 1; da.SelectCommand.Parameters.Add(@actual_time_in SqlDbType.Int).Value = 1; da.SelectCommand.Parameters.Add(@actual_time_out SqlDbType.Int).Value = 1; da.SelectCommand.Parameters.Add(@transaction_date SqlDbType.Int).Value = 1; DataSet ds = new DataSet(); da.Fill(ds); return ds; } Also i have passed it in the label of the calendar but its not showing in the calendar grid: foreach (DataRow row in ds.Tables[0].Rows) { string Transaction_date = Convert.ToDateTime(row[transaction_date]).ToShortDateString(); if (Transaction_date.Equals(s)) { LinkButton lb = new LinkButton(); lb.Text = IN: + row[actual_time_in] as String +   + OUT: + row[actual_time_out] as String +   + WR_HRS: + row[status] as String +   +  ; e.Cell.Controls.Add(lb); } } All i want is to display these 3 into each cell of the calendar grid of the employee who is logged in : actual_time_in='2015-01-01 09:30:00.000' actual_time_out=' 2015-01-01 15:30:00.000' status='PR' Right Now the calendar doesnt show anything & i want to take the transaction_date field for the date in the calendar

ali tuncer · Answer

I think you stored procedure query has to be like this : SELECT actual_time_in actual_time_outstatus FROM tblschedule where Employeeid = @Employeeid Thenyou need to pass Employeeid as a parameter to stored procedure. SqlDataAdapter da = new SqlDataAdapter(CalendarAttendancemyConnection); da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.Add(@Employeeid SqlDbType.Int).Value = 1 //here you pass employee id DataSet ds = new DataSet(); da.Fill(ds); return ds; I don't know where you keep employee idyou need the set it there..I passed 1 in the code above..