search by date

Question

3.00/5 (2 votes)

See more:

I am looking for some example code for search between to dates with c#

I created one stored procedure and one view.

The view is

select r.BUSINESSNAME, r.CONTACT, r.ADDRESS, r.CITY, r.STATE, r.ZIPCODE, r.PHONE, r.FAX, r.EMAIL, r.CITYCSBCERT, r.CSBxDate, r.CITYFBECERT, r.FBExDate, r.CITYMBECERT, r.MBExDATE, r.EDGECERT, r.EdgeExDate, r.DBE, r.DBExDate, r.SBE, r.SBExDate, r.CMHA, 
r.CMHAExp, r.OTHER, r.OxDate, r.OtherDocs0, r.OtherDoc0Exp, r.OtherDocs1, r.OtherDocs1Exp from Registration r
where CMHAExp between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE())or
 r.CSBxDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
 r.FBExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.MBExDATE between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.DBExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.EdgeExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.STATEMBExDATE between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.OtherDoc0Exp between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.OtherDocs1Exp between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.OxDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE()) or
r.SBExDate between DATEADD([day],-60, GETDATE()) and DATEADD([day],60, GETDATE())

This gives me 60 day window for expired data renewal.

I have this populate on page load.

The stored procedure gives the the ability to search out further in the future.

ASM

Create Procedure [dbo].[SearchExpirationDate]

@startDate smalldatetime,
@endDate smalldatetime
as

select r.BUSINESSNAME, r.CONTACT, r.ADDRESS, r.CITY, r.STATE, r.ZIPCODE, r.PHONE, r.FAX, r.EMAIL, r.CITYCSBCERT, r.CSBxDate, r.CITYFBECERT, r.FBExDate, r.CITYMBECERT, r.MBExDATE, r.EDGECERT, r.EdgeExDate, r.DBE, r.DBExDate, r.SBE, r.SBExDate, r.CMHA,
r.CMHAExp, r.OTHER, r.OxDate, r.OtherDocs0, r.OtherDoc0Exp, r.OtherDocs1, r.OtherDocs1Exp from Registration r
where CMHAExp between @startDate and @endDate or
 r.CSBxDate between @startDate and @endDate or
 r.FBExDate between @startDate and @endDate or
r.MBExDATE between @startDate and @endDate or
r.DBExDate between @startDate and @endDate or
r.EdgeExDate between @startDate and @endDate or
r.STATEMBExDATE between @startDate and @endDate or
r.OtherDoc0Exp between @startDate and @endDate or
r.OtherDocs1Exp between @startDate and @endDate or
r.OxDate between @startDate and @endDate or
r.SBExDate between @startDate and @endDate

I have written the code manually to connect to the grid view to populate on load.

I have 2 input text box startDate and endDate with a search button

I can manually write the code to access the stored procedure with now problem

I need to now how link my startDate box and endDate box that the stored procedure would see the dates that are entered.

I know this is a beginners question. So one day I hope to be a junior. :-D :-D

Posted 29-Nov-10 3:03am

postonoh

Add a Solution

Comments

Abdul Quader Mamun 29-Nov-10 9:12am

Thanks for your question. But how can it be tested. Neither with database nor with complete View or Store procedure.

postonoh 29-Nov-10 9:39am

I have tested the view against the database. The view gives me 60 days forward and 60 in the past in case a something was missed.

I just need sample code on when I click the search button the stored procedure uses the startdate and enddate as the keys to re-populate the gridview.

2 solutions

Solution 2

Here[^] is an example of using a parameterized stored procedure from c#.

If you'd like to search for other examples use the boldened part as a search phrase. (just stick c# in front to narrow the results.)

Good luck. :)

Posted 29-Nov-10 3:37am

Henry Minute

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)

Manfred Rudolf Bihy · Accepted Answer · 2010-11-29T03:34:00

A little something to get you started :) !

SqlConnection con   = new SqlConnection(...);
SqlCommand command  = new SqlCommand("dbo.SearchExpirationDate", con);
command.CommandType = CommandType.StoredProcedure;
SqlParameter startDateParam = new SqlParameter("@startDate",SqlDbType.DateTime));
SqlParameter endDateParam =  new SqlParameter("@startDate",SqlDbType.DateTime));

command.Parameters.Add(startDateParam);
command.Parameters.Add(endDateParam);
endDateParam.Value = DateTime.Parse(textBoxStartDate.Text);
startDateParam.Value = DateTime.Parse(textBoxEndDate.Text);

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    // Do stuff with what you read from your DB
}

Cheers

Manfred