Click here to Skip to main content
14,883,453 members
Please Sign up or sign in to vote.
3.67/5 (3 votes)
I have stored a timespan value in sqlserver database in Time(7) format but now i need to retieve that timespan value and perform some calculation i used this code but its showing error


C#
string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
sqlCommand tkk1 = new SqlCommand(tm1, con);
TimeSpan tm11 = tkk1.ExecuteScalar;
SqlCommand tkk2 = new SqlCommand(tm2, con);

and
C#
TimeSpan tm33 = TimeSpan.Parse(tkk3.ToString());



[edit]code blocks added[/edit]
Posted
Updated 17-Mar-13 0:03am
v2
Comments
Michael Haephrati 16-Mar-13 16:41pm
   
Another advice: prepare a small code sample of your problem (place there only what is necessary). Submit here this code sample along with the exact error messages you receive (even a screenshot).

1 solution

There are several mistakes you are doing.
TimeSpan is used to calculate the difference between two dates / times. You SQL query returns a single value - Test1Time.
This is probably either a string or a single date/time. So you don't need to use TimeSpan, and you normally won't store a Time Span in your database. Instead you would store a single date/time and use Time Span for example, to calculate the difference between the value you have saved in the database and the current date/time.

Assuming you have fixed that, First, to convert DateTime into TimeSpan use this:
C#
TimeSpan span1 = new TimeSpan(tm1.Now.Ticks),span2 = new TimeSpan(tm2.Now.Ticks),span3 = new TimeSpan(tm3.Now.Ticks);

To add them three, you need :
C#
TimeSpan span1=tm11;
TimeSpan total = span1.Add(span2);
total=total.Add(span3);
   
v2
Comments
Member 8780842 16-Mar-13 16:36pm
   
How to modify the sqlquery to get in time value and then add that please
Michael Haephrati 16-Mar-13 16:39pm
   
Change this line
TimeSpan tm11 = tkk1.ExecuteScalar;
into
DateTime tm11 = tkk1.ExecuteScalar;

Read here about TimeSpan and DateTime and the difference between them
http://msdn.microsoft.com/en-us/library/system.timespan.aspx
Member 8780842 16-Mar-13 16:43pm
   
Then also it is showing error in executescalar
Michael Haephrati 16-Mar-13 16:45pm
   
So it is saved as a string. Read the query result into a string argument and only then convert it or use it as is. Again, it would be best if you send full source code and exact error message
Member 8780842 16-Mar-13 16:49pm
   
string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
string tm2 = "Select Test2Time from Result where UserName='" + ref1.ToString() + "'";
string tm3 = "Select Test3Time from Result where UserName='" + ref1.ToString() + "'";
SqlCommand tkk1 = new SqlCommand(tm1, con);
DateTime tm11 = DateTime.Parse(tkk1.ExecuteScalar);
SqlCommand tkk2 = new SqlCommand(tm2, con);
TimeSpan tm22 = TimeSpan.Parse(tkk2.ToString());
SqlCommand tkk3 = new SqlCommand(tm3, con);
TimeSpan tm33 = TimeSpan.Parse(tkk3.ToString());
TimeSpan totaltime = (tm11.Add(tm22.Add(tm33)));

string insCmd13 = "Update Result Set Totaltime=@Test3Mark,totalmark=@Test3Time Where UserName=@UserName ";

SqlCommand insertUser13 = new SqlCommand(insCmd13, con);
insertUser13.Parameters.AddWithValue("@UserName", ref1.ToString());
insertUser13.Parameters.AddWithValue("@Test3Mark", totalmark.ToString());
insertUser13.Parameters.AddWithValue("@Test3Time", totaltime);
Michael Haephrati 16-Mar-13 16:57pm
   
And the error messages you receive?
Member 8780842 16-Mar-13 17:00pm
   
Now i used this code as per your suggestions

string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
string tm2 = "Select Test2Time from Result where UserName='" + ref1.ToString() + "'";
string tm3 = "Select Test3Time from Result where UserName='" + ref1.ToString() + "'";
SqlCommand tkk1 = new SqlCommand(tm1, con);
DateTime tm11 = Convert.ToDateTime(tkk1.ExecuteScalar().ToString());
SqlCommand tkk2 = new SqlCommand(tm2, con);
DateTime tm22 = Convert.ToDateTime(tkk2.ExecuteScalar().ToString());
SqlCommand tkk3 = new SqlCommand(tm3, con);
DateTime tm33 = Convert.ToDateTime(tkk3.ExecuteScalar().ToString());

TimeSpan totaltime = (tm11.Add(tm22.Add(tm33)));


error messages

1.Error 31 The best overloaded method match for 'System.DateTime.Add(System.TimeSpan)' has some invalid arguments D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 40 D:\Project march 16 2013\Project march 2 2013\

2.Error 32 Argument 1: cannot convert from 'System.DateTime' to 'System.TimeSpan' D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 49 D:\Project march 16 2013\Project march 2 2013\


3.Error 30 Argument 1: cannot convert from 'System.DateTime' to 'System.TimeSpan' D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 40 D:\Project march 16 2013\Project march 2 2013\


4.Error 29 The best overloaded method match for 'System.DateTime.Add(System.TimeSpan)' has some invalid arguments D:\Project march 16 2013\Project march 2 2013\Take3.aspx.cs 3252 31 D:\Project march 16 2013\Project march 2 2013\

Member 8780842 16-Mar-13 17:01pm
   
and in that Test1Time Test2Time and Test3Time the values will be like this "00:09:02" format
Michael Haephrati 16-Mar-13 17:04pm
   
All your errors are because you use TimeSpan which I already told you not to use. Just use strings to get the query results. You will receive something like "00:09:02". Remove everything else. You should then get no compilation errors. Then, tell me what you need to do and we will go to the next step
Member 8780842 16-Mar-13 17:09pm
   
string tm1 = "Select Test1Time from Result where UserName='" + ref1.ToString() + "'";
string tm2 = "Select Test2Time from Result where UserName='" + ref1.ToString() + "'";
string tm3 = "Select Test3Time from Result where UserName='" + ref1.ToString() + "'";

SqlCommand tkk1 = new SqlCommand(tm1, con);
DateTime tm11 = Convert.ToDateTime(tkk1.ExecuteScalar().ToString());
SqlCommand tkk2 = new SqlCommand(tm2, con);
DateTime tm22 = Convert.ToDateTime(tkk2.ExecuteScalar().ToString());
SqlCommand tkk3 = new SqlCommand(tm3, con);
DateTime tm33 = Convert.ToDateTime(tkk3.ExecuteScalar().ToString());


now I have got three time in tm11 and tm22 tm33 . i hav to add them three
Member 8780842 17-Mar-13 3:57am
   
Michael Haephrati there please tell me how can I do it
Michael Haephrati 17-Mar-13 3:59am
   
I will het back to you later
Michael Haephrati 17-Mar-13 5:35am
   
If you are now not getting any compilation errors you can go ahead:

First, to convert DateTime into TimeSpan use this:
TimeSpan span1 = new TimeSpan(tm1.Now.Ticks),span2 = new TimeSpan(tm2.Now.Ticks),span3 = new TimeSpan(tm3.Now.Ticks);

To add them three, you need :
TimeSpan span1=tm11;
TimeSpan total = span1.Add(span2);
total=total.Add(span3);

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