Home > Forum > ASP.NET > Search Record based on date selection

Search Record based on date selection

by Dave   on Sep 27, 2013   Category: ASP.NET  |  Views: 2436    |  Points: 5   |  Starter 
  Reply
Hi,
I want to search records Date wise and display the Results in GridView
Eg From Date 01/09/2013
To Date 30/09/2013
What would be the Store Procedure in Sql Server 2005
Due to some issue i cannot use Ajax calendar control so here's the design for calendar control
From Date: <asp:TextBox ID="txtfromdate" runat="server" AutoPostBack="True"
ontextchanged="TextBox1_TextChanged"></asp:TextBox>
 <asp:ImageButton ID="imgcalendar1" runat="server" Height="19px"
ImageUrl="~/images/calendar.png" onclick="ImageButton1_Click" Width="33px" />
<asp:Calendar ID="clr" runat="server" OnDayRender="Calendar1_DayRender" Style="top: 204px;
left: 203px; position: relative; height: 188px; width: 178px;" BackColor="#FFFFCC"
BorderColor="#FFCC66" BorderWidth="1px" DayNameFormat="Shortest" Font-Names="Verdana"
Font-Size="8pt" ForeColor="#663399" Height="200px" ShowGridLines="True" OnSelectionChanged="clr_SelectionChanged">
<SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
<SelectorStyle BackColor="#FFCC66" />
<TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#CC9966" />
<NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
<DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt" ForeColor="#FFFFCC" />
</asp:Calendar>
<br />
To Date: <asp:TextBox ID="txttodate" runat="server" AutoPostBack="True"
ontextchanged="TextBox1_TextChanged"></asp:TextBox>
 <asp:ImageButton ID="ImageButton1" runat="server" Height="19px"
ImageUrl="~/images/calendar.png" Width="33px"
onclick="ImageButton1_Click1" />

<asp:Calendar ID="Calendar1" runat="server"
onselectionchanged="Calendar1_SelectionChanged"
ondayrender="Calendar1_DayRender"

style="top: 32px; left: 236px; position: absolute; width: 161px; height: 170px"
BackColor="#FFFFCC" BorderColor="#FFCC66" BorderWidth="1px"
DayNameFormat="Shortest" Font-Names="Verdana" Font-Size="8pt"
ForeColor="#663399" Height="200px" ShowGridLines="True" Width="220px">
<SelectedDayStyle BackColor="#CCCCFF" Font-Bold="True" />
<SelectorStyle BackColor="#FFCC66" />
<TodayDayStyle BackColor="#FFCC66" ForeColor="White" />
<OtherMonthDayStyle ForeColor="#CC9966" />
<NextPrevStyle Font-Size="9pt" ForeColor="#FFFFCC" />
<DayHeaderStyle BackColor="#FFCC66" Font-Bold="True" Height="1px" />
<TitleStyle BackColor="#990000" Font-Bold="True" Font-Size="9pt"
ForeColor="#FFFFCC" />
</asp:Calendar>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Search"
style="position: relative" />

C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Calendar1.Visible = false;
clr.Visible = false;
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
if (Calendar1.Visible)
{
Calendar1.Visible = false;

}

else
{
Calendar1.Visible = true;
}
}
protected void Calendar1_SelectionChanged(object sender, EventArgs e)
{
txtfromdate.Text = Calendar1.SelectedDate.ToString("dd/MM/yyyy");
Calendar1.Visible = false;
}

protected void clr_SelectionChanged(object sender, EventArgs e)
{
txttodate.Text = clr.SelectedDate.ToString("dd/MM/yyyy");
clr.Visible = false;
}
protected void ImageButton1_Click1(object sender, ImageClickEventArgs e)
{
if (clr.Visible)
{
clr.Visible = false;

}

else
{
clr.Visible = true;
}
}

Bookmark and Share:
 
  User Reply  | Ask a question  |   Reply 
  Re :Search Record based on date selection   
by Thamil
on Sep 29 2013 12:13PM
Points : 10
Gold 
Hell Dev,

I have created the sample stored proc for this and i'm using tblcustomer table, you can change the table and select column also CreatedDt-column name in where condition based on your table.

CREATE PROC [dbo].[SP_Customer_Search]        
(
@pdtFrom datetime=null,
@pdtTo datetime=null,
@pIntErrDescOut int output
)
AS
BEGIN
DECLARE @sqlCmd nvarchar(1000)
begin
SET @sqlCmd = 'SELECT CUSTID,CUSTNAME,CUSTNAME,CUSTADDRESS1,CUSTADDRESS2,CUSTPHONE,CUSTEMAIL,ACTIVE FROM tblCustomer
WHERE Active=1';

if (@pdtFrom is not null and @pdtTo is not null)
begin
set @sqlCmd = @sqlCmd+' and CreatedDt between '''+convert(VARCHAR,@pdtFrom) + ''' and '''+convert(VARCHAR,@pdtTo) + '''';
--print @sqlCmd;
end;

--print @sqlCmd;
EXEC (@sqlCmd);
end
IF (@@ERROR <> 0)
BEGIN
SET @pIntErrDescOut = 1
END

END


Search with from and to date, pass the from and to date from front end side.

[SP_Customer_Search] '01-jun-2013' ,'30-jun-2013',0

Search without from and to date , pass the null value for from and to date from front end side.

[SP_Customer_Search] null ,null,0

i hope this will help you.

Recent Post

Latest Posts