Search From start date to end date with textboxes

Question

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 3-Oct-12 19:50pm

Wes101

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vijay bisht · Answer 1 · 2012-10-03T22:34:00

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)