I've got a little web app that I'm trying to get a timespan between the dates of each successive record in a recordset, and I can't seem to get 'er done.
So, I figure I'd iterate through the repeater and fire a query that gives me the most current record, and also the previous one. Each of these will have a date field, and I then want to determine (and display) the timespan between each record. For the newest
record, the timespan will be between that record and the current date. (For older records, the timespan will be between records.)
so, you see, each one is looking at the previous record for its baseline date for the timespan.
Here's my code right now:
foreach (RepeaterItem item in StringChangeRepeater.Items)
{
if (item.ItemType == ListItemType.Item || (item.ItemType == ListItemType.AlternatingItem))
{
HiddenField stringchangeIDHidden = item.FindControl("stringchangeID") as HiddenField;
int stringchangeID = Convert.ToInt32(stringchangeIDHidden.Value);
var getcurrentrecord = (from s in db.stringchange_view
where s.ID == stringchangeID
select new
{
s.changedate
}).First();
Label timeBetweenChangesLabel = item.FindControl("timeBetweenChangesLabel") as Label;
DateTime changedate1 = Convert.ToDateTime(getcurrentrecord.changedate);
TimeSpan changedateSpan = DateTime.Now - changedate1;
TimeSpan changedateSpan2 = changedate1 - Convert.ToDateTime(getcurrentrecord.changedate);
timeBetweenChangesLabel.Text = changedateSpan.Days.ToString();
}
}
I'm able to get the dates, but they all calculate from TODAY. I want them to calculate from the previous record. (Except the MOST CURRENT record; that one I DO want to calculate from today.
Make sense?
(if you hadn't guessed, it's an app to record guitar string changes, and calculate time they're on the guitar. This was easy for me in Classic ASP, but I can't figure out how to do it in .NET!)
I display it in decending order, with the most recent days on top.
So, if I was looking at the page today, I'd see it's only been one day since Guitar #1's previous string change. (Yesterday.) And then it would show that it was 20 days between the changes of 12/23 and 12/3 before that. And then it would display that it
was only two days between the 12/3 and 12/1 changes. And so on.
Does that make sense? Hopefully if you look at the classic ASP code it should be clear what I'm trying to do. I could take a screenshot of my existing page, if that helps.
Ok, this is pasted from the current app... it's in Classic ASP.
String changes
Collings MT Blackface
9/10/2012 (106 days)
Elixir Mandolin Medium
11-40
8/21/2012 (20 days)
Thomastik-Infeld
11-34
8/5/2012 (16 days)
Elixir Mandolin Medium
11-40
6/15/2012 (51 days)
Elixir Mandolin Medium
11-40
4/16/2012 (60 days)
Elixir Mandolin Medium
11-40
2/12/2012 (64 days)
Elixir Mandolin Medium
11-40
12/23/2011 (51 days)
Elixir Mandolin Medium
11-40
12/9/2011 (14 days)
d'Addario J74
11-40
12/8/2011 (1 days)
Elixir Mandolin Light
10-34
11/7/2011 (31 days)
Elixir Mandolin Medium
11-40
10/6/2011 (32 days)
d'Addario FW74
11-36
9/9/2011 (27 days)
d'Addario J74
11-40
8/9/2011 (31 days)
d'Addario J74
11-40
7/22/2011 (18 days)
d'Addario J74
11-40
6/19/2011 (33 days)
d'Addario J74
11-40
5/24/2011 (26 days)
d'Addario J74
11-40
Kentucky KM-505
8/7/2012 (140 days)
Elixir Mandolin Medium
11-40
3/4/2012 (156 days)
d'Addario J74
11-40
11/4/2011 (121 days)
d'Addario FW74
11-36
9/9/2011 (56 days)
d'Addario J74
11-40
Martin 000-15S
4/12/2012 (257 days)
Pearse 80/20 Lights
12-53
10/5/2011 (190 days)
Pearse 80/20 Lights
12-53
7/1/2011 (96 days)
Pearse 80/20 Lights
12-53
3/15/2011 (108 days)
Pearse 80/20 Lights
12-53
10/15/2010 (151 days)
Pearse 80/20 Lights
12-53
7/20/2010 (87 days)
Pearse 80/20 Lights
12-53
3/18/2010 (124 days)
Pearse 80/20 Lights
12-53
Martin 000-18GE
11/11/2012 (44 days)
d'Addario EJ12
13-56
3/7/2012 (249 days)
Pearse 80/20 Mediums
13-56
6/22/2011 (259 days)
Pearse 80/20 Mediums
13-56
3/26/2011 (88 days)
Pearse 80/20 Mediums
13-56
1/2/2011 (83 days)
Pearse 80/20 Mediums
13-56
10/19/2010 (75 days)
Pearse 80/20 Mediums
13-56
9/24/2010 (25 days)
Pearse 80/20 Mediums
13-56
8/27/2010 (28 days)
Pearse 80/20 Mediums
13-56
7/29/2010 (29 days)
Pearse 80/20 Mediums
13-56
4/28/2010 (92 days)
Pearse 80/20 Mediums
13-56
1/16/2010 (102 days)
Pearse 80/20 Mediums
13-56
Martin D-18VS
10/31/2012 (55 days)
d'Addario EJ12
13-56
5/30/2012 (154 days)
Pearse 80/20 Mediums
13-56
2/26/2012 (94 days)
Pearse 80/20 Mediums
13-56
9/9/2011 (170 days)
Pearse 80/20 Mediums
13-56
6/21/2011 (80 days)
Pearse 80/20 Mediums
13-56
5/19/2011 (33 days)
Pearse 80/20 Mediums
13-56
4/24/2011 (25 days)
Pearse 80/20 Mediums
13-56
3/3/2011 (52 days)
Pearse 80/20 Mediums
13-56
1/4/2011 (58 days)
Pearse 80/20 Mediums
13-56
11/25/2010 (40 days)
Pearse 80/20 Mediums
13-56
10/24/2010 (32 days)
Pearse 80/20 Mediums
13-56
9/30/2010 (24 days)
Pearse 80/20 Mediums
13-56
So, the first record in each group (it's grouped by instrument) shows the number of days from TODAY. Subsequent records calcluate the timespan from the next record. So what I'm doing is calculating the number of days the strings were on the instrument for each
string change. Does that make sense?
(you can ignore the last field; it's just the gauge of strings.)
dezinnia
Member
18 Points
144 Posts
linq... get most current record, and also the previous one (inside a repeater)
Dec 18, 2012 07:58 PM|LINK
I've got a little web app that I'm trying to get a timespan between the dates of each successive record in a recordset, and I can't seem to get 'er done.
So, I figure I'd iterate through the repeater and fire a query that gives me the most current record, and also the previous one. Each of these will have a date field, and I then want to determine (and display) the timespan between each record. For the newest record, the timespan will be between that record and the current date. (For older records, the timespan will be between records.)
So the idea is this:
If today is Dec 18, then...
Event date
12/11/2012 (7 days)
12/3/2012 (8 days)
11/3/2012 (30 days)
so, you see, each one is looking at the previous record for its baseline date for the timespan.
Here's my code right now:
foreach (RepeaterItem item in StringChangeRepeater.Items) { if (item.ItemType == ListItemType.Item || (item.ItemType == ListItemType.AlternatingItem)) { HiddenField stringchangeIDHidden = item.FindControl("stringchangeID") as HiddenField; int stringchangeID = Convert.ToInt32(stringchangeIDHidden.Value); var getcurrentrecord = (from s in db.stringchange_view where s.ID == stringchangeID select new { s.changedate }).First(); Label timeBetweenChangesLabel = item.FindControl("timeBetweenChangesLabel") as Label; DateTime changedate1 = Convert.ToDateTime(getcurrentrecord.changedate); TimeSpan changedateSpan = DateTime.Now - changedate1; TimeSpan changedateSpan2 = changedate1 - Convert.ToDateTime(getcurrentrecord.changedate); timeBetweenChangesLabel.Text = changedateSpan.Days.ToString(); } }I'm able to get the dates, but they all calculate from TODAY. I want them to calculate from the previous record. (Except the MOST CURRENT record; that one I DO want to calculate from today.
Make sense?
(if you hadn't guessed, it's an app to record guitar string changes, and calculate time they're on the guitar. This was easy for me in Classic ASP, but I can't figure out how to do it in .NET!)
thaicarrot
Contributor
5132 Points
1465 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 19, 2012 06:27 AM|LINK
Use DateTime.AddDay(-1); //Return the day before today.
Weera
dezinnia
Member
18 Points
144 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 19, 2012 02:39 PM|LINK
hmm... not sure I get what you're suggesting.
I don't want to return the day before today. I want to return the timespan between successive records.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 24, 2012 12:37 AM|LINK
What do you mean by "previous record"?
How did you design your tables' structures?
And do you have anything relationship between one record with another one to find out the specify "latest record"?
dezinnia
Member
18 Points
144 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 24, 2012 02:54 PM|LINK
Sorry... ok, see the very first post...
That classic ASP example uses "oil changes"... but my current app is for "Guitar String Changes."
I want to be able to display the number of days between changes.
The table, simplified, has this structure:
RecordID | GuitarID | StringChangeDate
So, let's say I have records like this:
RecordID | GuitarID | StringChangeDate 1 | 1 | 11/1/2012 2 | 2 | 11/6/2012 3 | 1 | 12/1/2012 4 | 1 | 12/3/2012 5 | 2 | 12/6/2012 6 | 1 | 12/23/2012I display it in decending order, with the most recent days on top.
So, if I was looking at the page today, I'd see it's only been one day since Guitar #1's previous string change. (Yesterday.) And then it would show that it was 20 days between the changes of 12/23 and 12/3 before that. And then it would display that it was only two days between the 12/3 and 12/1 changes. And so on.
Does that make sense? Hopefully if you look at the classic ASP code it should be clear what I'm trying to do. I could take a screenshot of my existing page, if that helps.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 25, 2012 12:02 AM|LINK
Hi again,
I still cannot understand what you mean……
Can you give us a example:
1) What's the date today?
2) How can you find the previous datetime in your given table?
3) What's the algorithm?
dezinnia
Member
18 Points
144 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 26, 2012 03:35 AM|LINK
Ok, this is pasted from the current app... it's in Classic ASP.
So, the first record in each group (it's grouped by instrument) shows the number of days from TODAY. Subsequent records calcluate the timespan from the next record. So what I'm doing is calculating the number of days the strings were on the instrument for each string change. Does that make sense?
(you can ignore the last field; it's just the gauge of strings.)
css-chlukito
Member
376 Points
48 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Dec 27, 2012 02:46 AM|LINK
Hi,
So if I'm correct, below code should generate timespan as you want.
DateTime changedateX = DateTime.Now; foreach (RepeaterItem item in StringChangeRepeater.Items) { if (item.ItemType == ListItemType.Item || (item.ItemType == ListItemType.AlternatingItem)) { HiddenField stringchangeIDHidden = item.FindControl("stringchangeID") as HiddenField; int stringchangeID = Convert.ToInt32(stringchangeIDHidden.Value); var getcurrentrecord = (from s in db.stringchange_view where s.ID == stringchangeID select new { s.changedate }).First(); Label timeBetweenChangesLabel = item.FindControl("timeBetweenChangesLabel") as Label; DateTime changedate1 = Convert.ToDateTime(getcurrentrecord.changedate); TimeSpan changedateSpan = changedateX - changedate1; TimeSpan changedateSpan2 = changedate1 - Convert.ToDateTime(getcurrentrecord.changedate); timeBetweenChangesLabel.Text = changedateSpan.Days.ToString(); changedateX = changedate1; } }I use "changedateX" as intermediate variable.
Please 'Mark as Answer' if this post helps you.
dezinnia
Member
18 Points
144 Posts
Re: linq... get most current record, and also the previous one (inside a repeater)
Jan 04, 2013 06:39 PM|LINK
EXCELLENT! Thank you!
Sorry for the delay in my reply; just getting back to this project!
Cheers!