Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Apr 16, 2012 05:09 PM by tdmca
Participant
778 Points
225 Posts
Apr 16, 2012 04:10 PM|LINK
I developed the Search Box which search the DataBase. and Database Tablename is "Register"
Now Register has the Columns Name, Age, Submit Date
like :
Name --------|--------AGE-------|------SubmitDate----|
John----------|---------25--------|-----04/12/2012-----|
Ronn----------|---------28--------|-----03/25/2012----|
Now, I wanna Search The those Persons who are Submitted at the period From 04/01/2012 To 04/30/2012 (i know which is "John")
but how can i Search for this. where Date Difference..Please Resolve my Issue.
Thanks`
Regards`
Contributor
3712 Points
838 Posts
Apr 16, 2012 04:12 PM|LINK
Please be more specific, how are you accessing your database? Entity Framework, Linq2SQL, ADO.NET, NHibernate, etc... ?
878 Points
264 Posts
Apr 16, 2012 04:25 PM|LINK
you can handle these things into sp itself and you can use the function datediff
for that see the link bellow
http://msdn.microsoft.com/en-us/library/ms189794.aspx
asp.net using sql server 05 and using dataset
with query "select * from register where name = ' + Textbox.text + '
now i want FromDate And ToDate Search Parameters and Filters i wanna Add whats the procedure if you made any Search Box can u give me the idea for it?
3970 Points
1096 Posts
Apr 16, 2012 04:28 PM|LINK
select yourfields
from register
where submitdate >= @startdate
and submitdate < the day after @enddate
Apr 16, 2012 04:35 PM|LINK
where cast(submitdate as date) >= cast(@startdate as date) and cast(submitdate as date) < cast(@enddate as date)+1
The cast stuff is to remove the timepart of the query since i guess you are only interested i matching on the date and not the time
4351 Points
1277 Posts
Apr 16, 2012 04:49 PM|LINK
hi
use between
below code is my sample search code...pls check and try
public DataView SearchQueuedApplication(string SurName, string FromDate, string Todate, string StatusCode) { using (SqlConnection con = new SqlConnection(connection)) { string strSearchQueuedApplication = " QueuedApplications.EligibilityStatusCode=@StatusCode and"; string strSearchQueuedApplication1 = " QueuedApplications.SurName=@SurName and "; string strSearchQueuedApplication2 = " QueuedApplications.AddedDate=@FromDate and "; string strSearchQueuedApplication3 = " QueuedApplications.AddedDate=@Todate and"; string strSearchQueuedApplication4 = " QueuedApplications.AddedDate between @FromDate and @Todate and"; string strSearchQueuedApplication5 = @"select * from QueuedApplications inner join EligibilityStatus on EligibilityStatus.EligibilityStatusCode=QueuedApplications.EligibilityStatusCode where "; if (SurName == "" && FromDate == "" && Todate == "" && StatusCode == "-2") { strSearchQueuedApplication5 = strSearchQueuedApplication5.ToString(); strSearchQueuedApplication5 = strSearchQueuedApplication5.Substring(0, strSearchQueuedApplication5.Length - 6); } else { if (StatusCode != "-2") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication; } if (SurName != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication1; } if (FromDate != "" && Todate != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication4; } else { if (FromDate != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication2; } if (Todate != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication3; } } strSearchQueuedApplication5 = strSearchQueuedApplication5.Substring(0, strSearchQueuedApplication5.Length - 4); } SqlCommand cmd = new SqlCommand(strSearchQueuedApplication5, con); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@SurName", SurName); cmd.Parameters.AddWithValue("@FromDate", FromDate); cmd.Parameters.AddWithValue("@Todate", Todate); cmd.Parameters.AddWithValue("@StatusCode", StatusCode); con.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); return ds.Tables[0].DefaultView; } }
thank u
Apr 16, 2012 04:53 PM|LINK
u like try this
"Select * from Register where SubmitDate between '"+txtStart.Text+"' and '"+TxtEnd.Text+"'"
2396 Points
661 Posts
Apr 16, 2012 05:09 PM|LINK
select * from Register where SubmitDate between '04-01-2012' and '04-30-2012'
MahadPK
Participant
778 Points
225 Posts
Start Date End Date
Apr 16, 2012 04:10 PM|LINK
I developed the Search Box which search the DataBase. and Database Tablename is "Register"
Now Register has the Columns Name, Age, Submit Date
like :
Name --------|--------AGE-------|------SubmitDate----|
John----------|---------25--------|-----04/12/2012-----|
Ronn----------|---------28--------|-----03/25/2012----|
Now, I wanna Search The those Persons who are Submitted at the period From 04/01/2012 To 04/30/2012 (i know which is "John")
but how can i Search for this. where Date Difference..Please Resolve my Issue.
Thanks`
Regards`
Knecke
Contributor
3712 Points
838 Posts
Re: Start Date End Date
Apr 16, 2012 04:12 PM|LINK
Please be more specific, how are you accessing your database? Entity Framework, Linq2SQL, ADO.NET, NHibernate, etc... ?
.NET Developer (ASP.NET, MVC, WPF) MCTS .NET 4 (Web, WCF)
Blog | Twitter
ZeeshanAnsar...
Participant
878 Points
264 Posts
Re: Start Date End Date
Apr 16, 2012 04:25 PM|LINK
you can handle these things into sp itself and you can use the function datediff
for that see the link bellow
http://msdn.microsoft.com/en-us/library/ms189794.aspx
Please 'Mark as Answer' if this post helps you.
MahadPK
Participant
778 Points
225 Posts
Re: Start Date End Date
Apr 16, 2012 04:25 PM|LINK
asp.net using sql server 05 and using dataset
with query "select * from register where name = ' + Textbox.text + '
now i want FromDate And ToDate Search Parameters and Filters i wanna Add whats the procedure if you made any Search Box can u give me the idea for it?
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: Start Date End Date
Apr 16, 2012 04:28 PM|LINK
select yourfields
from register
where submitdate >= @startdate
and submitdate < the day after @enddate
Knecke
Contributor
3712 Points
838 Posts
Re: Start Date End Date
Apr 16, 2012 04:35 PM|LINK
where cast(submitdate as date) >= cast(@startdate as date)
and cast(submitdate as date) < cast(@enddate as date)+1
The cast stuff is to remove the timepart of the query since i guess you are only interested i matching on the date and not the time
.NET Developer (ASP.NET, MVC, WPF) MCTS .NET 4 (Web, WCF)
Blog | Twitter
sriramabi
Contributor
4351 Points
1277 Posts
Re: Start Date End Date
Apr 16, 2012 04:49 PM|LINK
hi
use between
below code is my sample search code...pls check and try
public DataView SearchQueuedApplication(string SurName, string FromDate, string Todate, string StatusCode) { using (SqlConnection con = new SqlConnection(connection)) { string strSearchQueuedApplication = " QueuedApplications.EligibilityStatusCode=@StatusCode and"; string strSearchQueuedApplication1 = " QueuedApplications.SurName=@SurName and "; string strSearchQueuedApplication2 = " QueuedApplications.AddedDate=@FromDate and "; string strSearchQueuedApplication3 = " QueuedApplications.AddedDate=@Todate and"; string strSearchQueuedApplication4 = " QueuedApplications.AddedDate between @FromDate and @Todate and"; string strSearchQueuedApplication5 = @"select * from QueuedApplications inner join EligibilityStatus on EligibilityStatus.EligibilityStatusCode=QueuedApplications.EligibilityStatusCode where "; if (SurName == "" && FromDate == "" && Todate == "" && StatusCode == "-2") { strSearchQueuedApplication5 = strSearchQueuedApplication5.ToString(); strSearchQueuedApplication5 = strSearchQueuedApplication5.Substring(0, strSearchQueuedApplication5.Length - 6); } else { if (StatusCode != "-2") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication; } if (SurName != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication1; } if (FromDate != "" && Todate != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication4; } else { if (FromDate != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication2; } if (Todate != "") { strSearchQueuedApplication5 = strSearchQueuedApplication5 + strSearchQueuedApplication3; } } strSearchQueuedApplication5 = strSearchQueuedApplication5.Substring(0, strSearchQueuedApplication5.Length - 4); } SqlCommand cmd = new SqlCommand(strSearchQueuedApplication5, con); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@SurName", SurName); cmd.Parameters.AddWithValue("@FromDate", FromDate); cmd.Parameters.AddWithValue("@Todate", Todate); cmd.Parameters.AddWithValue("@StatusCode", StatusCode); con.Open(); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); return ds.Tables[0].DefaultView; } }thank u
sriramabi
Contributor
4351 Points
1277 Posts
Re: Start Date End Date
Apr 16, 2012 04:53 PM|LINK
hi
u like try this
"Select * from Register where SubmitDate between '"+txtStart.Text+"' and '"+TxtEnd.Text+"'"
thank u
tdmca
Contributor
2396 Points
661 Posts
Re: Start Date End Date
Apr 16, 2012 05:09 PM|LINK
select * from Register where SubmitDate between '04-01-2012' and '04-30-2012'