Click here to Skip to main content
15,849,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all, im trying to search data in my MySQL database by date range from 2 textboxes (ie "tbStartDate", "tbEndDate"), but the user must only be allowed to search up to a maximum of only 31 days anything over that a error must be thrown, I have a query that retrieves all the information shown below but now need that date range search from a button click to show only certain record please help

SQL
("SELECT transactions.transaction_id, transactions.date_time, transaction_type.transaction_type_description, transactions.amount FROM `transactions`" +
        "INNER JOIN transaction_type ON transaction_type.transaction_type_id = transactions.transaction_type_id INNER JOIN member_detail ON member_detail.member_id = transactions.account_or_member_id " +
        "WHERE account_or_member_id = @member_id and is_member = '1' and transactions.transaction_type_id = '5' " +
        "Order by transactions.date_time ASC",con2)


any help would be appreciated, been battling with this function.
Wes
Posted
Comments
Rockstar_ 4-Oct-12 1:59am    
Count the difference of the dates based on the result you can execute the query.Use built in datetime function for finding the difference.
Venkatesh Mookkan 4-Oct-12 2:18am    
Why can't you restrict the TextBox to have the validation of 31 days and pass the selected dates to the query?
Wes101 4-Oct-12 3:12am    
thanks it worked im retrieving data between whatever dates i choose only, but now im stuck on how to limit it only to 31 days if it more than 31 days a message box must pop up and say something like "Choose 31 days or less"

1 solution

DateTime dt = Convert.ToDateTime(tbstartdate.Text))
DateTime dta = Convert.ToDateTime(tbenddate.Text))
TimeSpan pp = dta - dt
Integer days = pp.Days

if (days > 31)
{
messegebox.show("U can Not Have More Than 31 days")
return
}


SQL
("SELECT transactions.transaction_id, transactions.date_time, transaction_type.transaction_type_description, transactions.amount FROM `transactions`" +
        "INNER JOIN transaction_type ON transaction_type.transaction_type_id = transactions.transaction_type_id INNER JOIN member_detail ON member_detail.member_id = transactions.account_or_member_id " +
        "WHERE account_or_member_id = @member_id and is_member = '1' and  transactions.date_time > = @tbstartdate and transactions.date_time <=  @tbenddate and @tbtransactions.transaction_type_id = '5' " +
        "Order by transactions.date_time ASC",con2)
 
Share this answer
 
Comments
Wes101 4-Oct-12 4:44am    
thanks, it worked perfectly, appreciated alot
[no name] 4-Oct-12 4:55am    
Ur Welcome :)

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