Retrieving Values from DataReader RSS

• • Reply
  

  poonam219

  Member

  406 Points

  268 Posts

  Retrieving Values from DataReader

  May 02, 2012 08:57 AM|LINK

  Hello everyone

  I am stuck at a place. I want to retrieve values from datareader. my sql query is:- 

  select empcode,empname,todaydate,signIn,signout,remarks from empattendencesheet

  No i want to retrieve all the values of signIn and signout cloumn. values could be 100 or 200 or more than this.

  How can i do this. i know how to get valiues from datareader but i always did it for one value condition. dont know how to do it for multiple values.

  Plus after retrieving all the value i need to check each and every value and need to update remarks column after checking each value. also at the point of checking the value there are 4 conditions so i need to check value for each contion and then will update the renarks column.

  Example - if sign in time = 10 am then update remarks value = 1, if sign in time = 10:30 am then update remarks value = 2,  if sign in time = 11 am then update remarks value = 3,  if sign in time = 11:30 am then update remarks value = 4

  Any help will be appreciated. Its a bit urgent. Thank you in advance

  ORM | nutritioouslife
  
  Poonam Malik
• • Reply
  

  jamshed alam

  Member

  319 Points

  105 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:08 AM|LINK

  hi

  read msdn tutorial

  http://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.71).aspx

  jamshed
• • Reply
  

  poonam219

  Member

  406 Points

  268 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:12 AM|LINK

  I read this tutorial before after not getting any solution , i posted my problem here...... m still confused

  ORM | nutritioouslife
  
  Poonam Malik
• • Reply
  

  poonam219

  Member

  406 Points

  268 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:14 AM|LINK

  i can also use data table..... its not cumlsory to use data reader

  ORM | nutritioouslife
  
  Poonam Malik
• • Reply
  

  karthicks

  All-Star

  31374 Points

  5420 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:15 AM|LINK

  hi, refer below code

  SqlConnection conUpdate = new SqlConnection("server=test;database=master;user id=user1;passsword=user@1");
  		SqlCommand commandUpdate = new SqlCommand("update empattendencesheet set remarks@remarks", conUpdate);
  		commandUpdate.Parameters.Add("@remarks", SqlDbType.Int).Value = 1;
  		SqlConnection con = new SqlConnection("server=test;database=master;user id=user1;passsword=user@1");
  		SqlCommand command = new SqlCommand("select empcode,empname,todaydate,signIn,signout,remarks from empattendencesheet", con);
  		con.Open();
  		conUpdate.Open();
  		SqlDataReader dataRader = command.ExecuteReader();
  		if (dataRader.HasRows)
  			while (dataRader.Read())
  			{
  				switch (dataRader["signIn"].ToString())
  				{
  					case "10 am":
  						commandUpdate.Parameters["@remarks"].Value = 1;
  						break;
  					case "10:30 am":
  						commandUpdate.Parameters["@remarks"].Value = 2;
  						break;
  					case "11 am":
  						commandUpdate.Parameters["@remarks"].Value = 3;
  						break;
  					case "11:30 am":
  						commandUpdate.Parameters["@remarks"].Value = 4;
  						break;
  				}
  				commandUpdate.ExecuteNonQuery();
  			}
  		con.Close();
  		conUpdate.Close();

  Thanks,
  Karthick S
  • Marked as answer by poonam219 on May 02, 2012 10:54 AM
• • Reply
  

  poonam219

  Member

  406 Points

  268 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:20 AM|LINK

  Hi,

  Thanks for your reply...... i want to ask you if it will update all the records.

  Plus there is one more problem. There are already values in database. and the values are of DateTime type

  so i have to compare the time as well . like the value in variable is 01/04/2012 10:10:00:00 .... so now i want to compare it with my own value lets say 11 am. So if(11 > value_in_column) then rem,arks = 1. Hope you are getting me.... thanks again

  ORM | nutritioouslife
  
  Poonam Malik
• • Reply
  

  bhaskar.mule

  Contributor

  2270 Points

  659 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:21 AM|LINK

  hi

  here the it will show how to retrive the values from  excel file using  datareader.

  http://csharpektroncmssql.blogspot.in/2012/04/how-to-read-data-from-excel-file-in.html

  Bhaskar.Mule
  Site:Rare technical solutions
• • Reply
  

  basheerkal

  Star

  10672 Points

  2426 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:25 AM|LINK

  This is the basic method to use DataReader

  con = new SqlConnection(ConfigurationManager.ConnectionStrings["YourString"].ConnectionString);
              con.Open();
              string str = "SELECT Field1.Field2. field3 FROM table";
              SqlCommand cmd = new SqlCommand(str, con);
              SllDataReader dr = cmd.ExecuteReader();
              dr.Read();
              YouRGridView.DataSource = dr;
              YourGridView,DataBind();
              con.Close();

  To get  a field from reader,

   If it is a string value in fiirst column it can be obtained as

   dr.GetString(0)

   

   

  Kindly mark this post as "Answer", if it helped you.
  
  (Talk less..Work more)
• • Reply
  

  poonam219

  Member

  406 Points

  268 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:27 AM|LINK

  i know the basic method but there are so many conditions thats why i am not able to do this.

  Please look at my last reply.....

  ORM | nutritioouslife
  
  Poonam Malik
• • Reply
  

  karthicks

  All-Star

  31374 Points

  5420 Posts

  Re: Retrieving Values from DataReader

  May 02, 2012 09:38 AM|LINK

  TimeSpan ts10 = new TimeSpan(10, 0, 0);
  		TimeSpan ts1030 = new TimeSpan(10, 30, 0);
  		TimeSpan ts11 = new TimeSpan(11, 0, 0);
  		TimeSpan ts1130 = new TimeSpan(11, 30, 0);
  
  		SqlConnection conUpdate = new SqlConnection("server=test;database=master;user id=user1;passsword=user@1");
  		SqlCommand commandUpdate = new SqlCommand("update empattendencesheet set remarks@remarks", conUpdate);
  		commandUpdate.Parameters.Add("@remarks", SqlDbType.Int).Value = 1;
  		SqlConnection con = new SqlConnection("server=test;database=master;user id=user1;passsword=user@1");
  		SqlCommand command = new SqlCommand("select empcode,empname,todaydate,signIn,signout,remarks from empattendencesheet", con);
  		con.Open();
  		conUpdate.Open();
  		SqlDataReader dataRader = command.ExecuteReader();
  		if (dataRader.HasRows)
  			while (dataRader.Read())
  			{
  				TimeSpan ts = Convert.ToDateTime(dataRader["signIn"]).TimeOfDay;
  				if (ts <= ts10)
  					commandUpdate.Parameters["@remarks"].Value = 1;
  				else if (ts <= ts1030)
  					commandUpdate.Parameters["@remarks"].Value = 2;
  				else if (ts <= ts11)
  					commandUpdate.Parameters["@remarks"].Value = 3;
  				else
  					commandUpdate.Parameters["@remarks"].Value = 4;
  				commandUpdate.ExecuteNonQuery();
  			}
  		con.Close();
  		conUpdate.Close();

  
  hi, below should help you

   

  mark my both the replies as answered one

  Thanks,
  Karthick S
  • Marked as answer by poonam219 on May 02, 2012 10:53 AM