Question:

I’m relatively new to asp.net 3.5, especially in relation to data. My previous experience has been with old asp and then somewhat with .net 1.0.

I’m building a new application with Visual Studio 2008. This application is for entering financial transactions on a website to share data between people “in the field” and the home office.  It is not intended to be full functioning accounting solution.  The database is MS SQL Server 2005 that is located on a different server.

I mostly need help with a SQL statement for a TableAdapter.  I want to be able to get all the records in the table based on time periods.  As a small example,  let’s say I have these fields:

TransID (key)
CatID
Date
Description
Amount

And I have these records:
1 – 1 – 1/1/2010 – Rent – $500.00
2 – 2 – 1/15/2010 – Food – $100.00
3 – 1 – 2/1/2010 – Rent – $500.00
4 – 2 – 2/3/2010 – Gas – $30.00
5 – 2 – 2/4/2010 – Food – $100.00

I would like to add queries to the TableAdapter like “GetTransactionsbyMonth” and “GetTransactionsbyYear”.  So if the user chooses “February 2010″ as the month then it would return rows 3, 4, and 5.  If they choose “January 2010″ then it would return rows 1 and 2.  I know how to use parameters to do the categories, but I don’t know how to do it based on the month/year of the date.

I hope I explained that well enough.

Thanks in advance for your help!

Rick

Solution:

The SQL part might look something like this:

select TransID, CatID, Date, Description, Amount from YourTable

Where Datepart(month, [Date]) = @VarialbleHoldingMonthNumberSelected

and datepart(year, [Date]) = @VariableHoldingYearNumberSelected

-G