Help me to solve this query

I have a table storing information on auctions. The fields are AuctionID, Location, StartDateTime, EndDateTime, Status etc.

I need a query to display auctions for an entire week (excluding weekends) as shown in the figure. I am using VS 2008 gridview and fetching data from SQL Server 2005.

The grid shows auctions for the specific date according to locations column wise. For ex. There are five auctions on Location 11开发者_开发技巧, 12,13,14,15 with different times on Mon 14th Feb.

Can anybody solve my problem.

Thanks for sharing your time.

Create an Auction class as @Karel suggests. E.g.

public class Auction{
    public int ID{get;set;}
    public DateTime StartDate{get;set;}
    public DateTime EndDate{get;set;}
    public string Location{get;set}
    //Add other member variables for all Auction columns. 
}

Then write some code to populate a collection of classes from the database that match the time range.

So in SQL you need a query or stored procedure that does something like:

SELECT * FROM Auction WHERE (StartDateTime > @StartDate AND EndDateTime < @EndDate) --As appropriate for your RDBMS.


`/*Get Data into an SQLDataReader (for speed) or DataTable/Set if you prefer*/`
List<Auction> myAuctions = new List<Auction>();

while (rdr.Read()){
    Auction auc = new Auction();
    auc.AuctionId = (int)rdr["AuctionId"];
    //set all properties.
    myAuctions.Add(auc);
}

In your code you'll need to Sort your List by StartDate (use `List.OrderBy(...)') and then bind to a control.

HTH.

You could just create a class with the 5 public properties, one for evey day of the week and load a list of that class with a simple query.

If you realy want a query, you could use a switch in you select clause