Solved

How to Use Top Date in asp.net

Posted on 2011-03-07
14
252 Views
Last Modified: 2012-06-27
I am building a web app, where the requirement is to make a form in which admin will be adidng information 3 times a day and the front user willl be reading from this Gridview. I was going to use only one Gridview with update fields, but they want to run a report every month will all entered ata

So I build a separate page for user and admin, and thought i used insert command  , plus a timestamp fiedl, so the idea is to pull only the data entered on that same day they are opening the document, in other words only view the last update data not the whole data from the table?

Much help is appreciated
0
Comment
Question by:TonyReba
  • 7
  • 6
14 Comments
 
LVL 10

Expert Comment

by:himanshut
ID: 35064074
based on whtever is your columnname and tablename:

select TOP 1 * from tablename
order by timestamp_col DESC

this will give you only 1 record with the very latest time in your timestamp column

Cheers!
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35064125
actually my table looks like this


Id, DateTime, Col1, COl2 Col3, Col4


So it will be populated everyday , and I am trying to find the way to pull all columns but only the latest values (could be 10 values) on the day they are opening the Read only GridView, any more suggestions....
Thanks
0
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 35064277
select TOP 10 * from tablename
order by  DateTime
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35064353
Can I use top without specifyng the number of records? 10 was only a say, but I could be any number of rows inserted on a day and the system must pull all of that day

e.g.
 03/03/2011    value1 value2 value3NO
03/03/2011    value1 value2 value3NO
03/03/2011    value1 value2 value3NO

03/04/2011    value1 value2 value3            YES
0
 
LVL 10

Expert Comment

by:himanshut
ID: 35064415
nope,

top has to be used with some number.

Instead you can specific the current date which will pull up everything for that particular day.
0
 
LVL 10

Expert Comment

by:himanshut
ID: 35064458
ideally,

select * from tablename
WHERE DATEDIFF(day, tablename.datetime_col, GETDATE()) = 0

should do the job for you.

Cheers!
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35064498
so only select where date currentdate()??

how do i syntax this?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 10

Accepted Solution

by:
himanshut earned 500 total points
ID: 35064553
select * from tablename
WHERE DATEDIFF(day, tablename.datetime_col, GETDATE()) = 0
order by tablename.datetime_col DESC

in this where condition, it takes the the datetime from your table and gets the difference from CURRENTDATE i.e. GETDATE() ,
where ever it has difference as 0 i.e. 2011-03-08  MINUS 2011-03-08 will be ZERO :-
it will display those records

0
 
LVL 9

Author Comment

by:TonyReba
ID: 35064857
great will try tomorrow, so, in this case should I stick with date only or date and time to make it more accurate? how would this be?


this is the code that feeds my date time column in the database

dateLabel.Text = DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm");
0
 
LVL 10

Expert Comment

by:himanshut
ID: 35064867
well if you put in date it will start from very first millisecond of that day till the latest record you have for that date
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35064958
oh one more concern, so the admin will be updating, lets say like a hotel, that you need to show available rooms, so if  room status was occupied , and the latest status is available I want only show that status,,,,,, sorry I totally forgot this, so I am thinking a mix of Top and Date statement??
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35064994
select TOP 1 * from tablename
WHERE DATEDIFF(day, tablename.datetime_col, GETDATE()) = 0
order by tablename.datetime_col DESC


????
0
 
LVL 10

Expert Comment

by:himanshut
ID: 35065677
I didnt get you this comment

---------------
oh one more concern, so the admin will be updating, lets say like a hotel, that you need to show available rooms, so if  room status was occupied , and the latest status is available I want only show that status,,,,,, sorry I totally forgot this, so I am thinking a mix of Top and Date statement??
----------------
0
 
LVL 9

Author Comment

by:TonyReba
ID: 35069498
There is two pages on the site, one for users , and one for administrator

the admin will be entering the room and the availability several times a day, so the users should see only the last entered information for each room,  (status).  

Also this have to show the data for that day , since the room had other statuses a day before ,,,,
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calling web service and its methods dynamically 2 42
xpath sql query 2008 8 44
SQL Login 17 40
1 FROM DUAL wont work with additional columns ?? 4 26
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now