Tôi có chương trình. Khi tôi tìm kiếm theo nội dung thì nó hiển thị nhiều kết quả mà phải sang trang trên GV. Nhưng khi tôi bấm sang trang thì nó lại không hiển thị kết quả mình vừa tìm kiếm
Đây là source của tôi
SQL code
Mã:
USE [iDOC]
GO
/****** Object: UserDefinedFunction [dbo].[fPhanTrangSQL] Script Date: 06/07/2016 15:47:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fPhanTrangSQL](
@Total int,
@currPage int ,
@PageSize int,
@rowperpage int )
Returns nvarchar(1000)
AS
Begin
If @currPage=0 Set @currPage=1
DECLARE @PageNumber int SET @PageNumber=1
DECLARE @i int
SET @i=1
DECLARE @TotalPage int
IF @Total%@rowperpage>0
SET @TotalPage=(@Total/@rowperpage)+1
ELSE
SET @TotalPage=@Total/@rowperpage
DECLARE @Start int SET @Start=0
DECLARE @SQL nvarchar(4000) SET @SQL=''
If @TotalPage >1
BEGIN
SET @SQL=@SQL+ N'<div id=''paging''> Trang: '
IF @currPage<=@TotalPage
BEGIN
IF @currPage=1
BEGIN
SET @PageNumber=@PageSize
IF @PageNumber>@TotalPage SET @PageNumber=@TotalPage
SET @Start=1
END
ELSE
BEGIN
SET @SQL=@SQL+ N' <a href=''?page=1''> Trang đầu </a> '
SET @SQL=@SQL+ ' <a href=?page='+
Cast((@currPage-1) AS nvarchar(4))+N'>«</a> '
IF(@TotalPage-@currPage)<@PageSize/2
BEGIN
SET @Start=(@TotalPage-@PageSize)+1
IF @Start<=0 SET @Start=1
SET @PageNumber = @TotalPage
END
ELSE
BEGIN
IF (@currPage-(@PageSize/2))=0
BEGIN
SET @Start=1
SET @PageNumber=@currPage+(@PageSize/2)+1
IF @TotalPage<@PageNumber
SET @PageNumber=@TotalPage
END
ELSE
BEGIN
SET @Start=@currPage-(@PageSize/2)
IF @Start<=0 SET @Start=1
SET @PageNumber=@currPage+(@PageSize/2)
IF @TotalPage<@PageNumber
SET @PageNumber=@TotalPage
ELSE
IF @PageNumber <@PageSize
SET @PageNumber=@PageSize
END
END
END
SET @i=@Start
WHILE @i<=@PageNumber
BEGIN
IF @i=@currPage
SET @SQL=@SQL+'
['+Cast(Cast(@i AS int) AS nvarchar(4))+']'
ELSE
SET @SQL=@SQL+'
<a href=''?page='+Cast(@i AS nvarchar(4))+'''>'
+Cast(@i AS nvarchar(4))+'</a> '
SET @i=@i+1
END
IF @currPage<@TotalPage
BEGIN
SET @SQL=@SQL+ N'
<a href=''?page='+Cast((@currPage+1)
AS nvarchar(4))+N'''>»</a> '
SET @SQL=@SQL+ N'
<a href=''?page='+cast(@TotalPage AS nvarchar(6))+
N'''> Trang cuối </a>'
End
Set @SQL=@SQL+'</div>'
End
END
Return @SQL
END
Mã:
USE [iDOC]
GO
/****** Object: StoredProcedure [dbo].[spTB_CongVan_Search_UserName_Paging] Script Date: 06/07/2016 16:01:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spTB_CongVan_Search_UserName_Paging]
@UserName NVARCHAR(50),
@SoCV NVARCHAR(100),
@Keyword NVARCHAR(200),
@idLoaiCV NVARCHAR(50),
@FromDate NVARCHAR(20),
@ToDate NVARCHAR(20),
@currpage int,
@rowperpage INT,
@Pagesize int
AS
BEGIN
DECLARE @SQL NVARCHAR(4000),@SQLDK NVARCHAR(1000),@Order nvarchar(300),@form int, @end INT,@Total int
SET @Order=''
Set @form=((@currPage - 1)*@rowperpage)+1
IF @form IS NULL SET @form=0
SET @end=@currPage*@rowperpage
IF @end IS NULL SET @end=0
SET @SQLDK=''
SET @SQL='
BEGIN
WITH s AS
(
SELECT ROW_NUMBER()
OVER(ORDER BY TB_CongVan.idCV desc ) As RowNum,TB_CongVan.idCV, TB_CongVan.SoCV, TB_CongVan.idLoaiCV,
TB_CongVan.TrichYeu, TB_CongVan.NgayKy, TB_CongVan.NgayNhan,
TB_CongVan.NgayDangCV, TB_CongVan.FileToanVan, TB_CongVan.DonViThucHien,
TB_CongVan.TrangThai,TB_LoaiCV.TenLoaiCV
FROM TB_CongVan INNER JOIN TB_LoaiCV ON TB_CongVan.idLoaiCV = TB_LoaiCV.idLoaiCV WHERE CHARINDEX('''+@UserName+''',TB_CongVan.UserDoc)>0'
IF @SoCV<>''
SET @SQLDK= ' And TB_CongVan.SoCV like N''%'+@SoCV+'%'''
IF @Keyword<>''
SET @SQLDK=@SQLDK+ ' and dbo.fChuyenCoDauThanhKhongDau(TB_CongVan.TrichYeu) like N''%'+dbo.fChuyenCoDauThanhKhongDau(@Keyword)+'%'''
IF @idLoaiCV<>''
SET @SQLDK=@SQLDK+ ' and TB_CongVan.idLoaiCV like N''%'+@idLoaiCV+'%'''
IF @FromDate<>''
SET @SQLDK = @SQLDK + ' and dbo.ChuyenTextSangDateTime(CONVERT(varchar(20), TB_CongVan.NgayDangCV,103)) >= dbo.ChuyenTextSangDateTime(''' + @FromDate + ''')'
IF @ToDate<>''
SET @SQLDK = @SQLDK + ' and dbo.ChuyenTextSangDateTime(CONVERT(varchar(20), TB_CongVan.NgayDangCV,103)) <= dbo.ChuyenTextSangDateTime(''' + @ToDate + ''')'
SET @Order=@Order+'
)
Select * From s
Where RowNum Between '+cast(@form AS varchar(10))+' AND '+cast(@end AS varchar(10))+'
END
'
EXEC (@SQL+@SQLDK+@Order)
--PRINT @SQL+@SQLDK+@Order
SELECT @Total=COUNT(*) FROM TB_CongVan tcv
SELECT dbo.fPhanTrangSQL(@Total, @currPage, @PageSize, @rowperpage) AS PhanTrang
END
html code
Mã:
<table cellpadding="5" cellspacing="5" width="900px" <tr>
<td align="left">
Số công văn:
<asp:TextBox ID="txtSoCV" Width="150px" runat="server"></asp:TextBox>
Từ khóa:
<asp:TextBox ID="txtKeyword" Width="250px" runat="server"></asp:TextBox>
</td>
<td align="right">
<asp:LinkButton ID="lbtFind" runat="server" OnClick="lbtFind_Click">[IMG]../images/find.png[/IMG] Tìm kiếm</asp:LinkButton>
| <a href="../Template/Default.aspx">
[IMG]../images/signout.png[/IMG]
Thoát</a>
</td>
</tr>
<tr>
<td colspan="2" align="left">
<asp:Panel ID="pnlFind_Advance" Style="cursor: pointer;" runat="server">
<asp:ImageButton ID="Find_ToggleImage" runat="server" ImageUrl="~/images/expand.jpg"
AlternateText="Mở rộng" />
<asp:Label ID="Label1" BackColor="#D9EF95" Text="Mở rộng..." runat="server"></asp:Label>
</asp:Panel>
<asp:Panel ID="pnlFind_Content" Style="overflow: hidden;" Height="0px" runat="server">
<table cellpadding="0" cellspacing="5" border="0" width="100%" <tr>
<td align="left">
Theo loại công văn:
</td>
<td align="left">
<asp:DropDownList ID="cboLoaiCV" AppendDataBoundItems="true" SkinID="SkinDefault"
Width="190px" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td align="left">
Theo ngày đăng:
</td>
<td align="left">
từ ngày:
<asp:TextBox ID="txtFromDate" Width="80px" runat="server"></asp:TextBox>
<ajaxToolkit:CalendarExtender ID="txtFromDate_CalendarExtender" runat="server" Enabled="True"
Format="dd/MM/yyyy" TargetControlID="txtFromDate">
</ajaxToolkit:CalendarExtender>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" ControlToValidate="txtFromDate"
ValidationGroup="CheckFind" ValidationExpression="^(?=\d{2}([-.,\/])\d{2}\1\d{4}$)(?:0[1-9]|1\d|[2][0-8]|29(?!.02.(?!(?!(?:[02468][1-35-79]|[13579][0-13-57-9])00)\d{2}(?:[02468][048]|[13579][26])))|30(?!.02)|31(?=.(?:0[13578]|10|12))).(?:0[1-9]|1[012]).\d{4}$"
runat="server" ErrorMessage="Sai định dạng dd/MM/yyyy" ForeColor="Red"></asp:RegularExpressionValidator>
đến ngày:
<asp:TextBox ID="txtToDate" Width="80px" runat="server"></asp:TextBox>
<ajaxToolkit:CalendarExtender ID="txtToDate_CalendarExtender" runat="server" Enabled="True"
Format="dd/MM/yyyy" TargetControlID="txtToDate">
</ajaxToolkit:CalendarExtender>
<asp:RegularExpressionValidator ID="RegularExpressionValidator2" ControlToValidate="txtToDate"
ValidationGroup="CheckFind" ValidationExpression="^(?=\d{2}([-.,\/])\d{2}\1\d{4}$)(?:0[1-9]|1\d|[2][0-8]|29(?!.02.(?!(?!(?:[02468][1-35-79]|[13579][0-13-57-9])00)\d{2}(?:[02468][048]|[13579][26])))|30(?!.02)|31(?=.(?:0[13578]|10|12))).(?:0[1-9]|1[012]).\d{4}$"
runat="server" ErrorMessage="Sai định dạng dd/MM/yyyy" ForeColor="Red"></asp:RegularExpressionValidator>
</td>
</tr>
</table>
</asp:Panel>
<ajaxToolkit:CollapsiblePanelExtender ID="cpeFind" runat="server" TargetControlID="pnlFind_Content"
ExpandControlID="pnlFind_Advance" CollapseControlID="pnlFind_Advance" Collapsed="true"
ImageControlID="Find_ToggleImage" ExpandedImage="~/images/collapse.jpg" CollapsedImage="~/images/expand.jpg"
ExpandedText="Thu hẹp ..." CollapsedText="Mở rộng ..." TextLabelID="Label1" SuppressPostBack="true"
SkinID="SkinDefault" />
</td>
</tr>
</table>
</asp:Panel>
</div>
<div id="Grid_CV" <asp:Panel ID="pnlDanhSach" Width="100%" runat="server">
<table cellpadding="5px" cellspacing="5px" width="900px" <tr>
<td id="TableGrid" align="left">
<asp:GridView ID="grvDanhSach" AutoGenerateColumns="False" SkinID="SkinDefault" OnRowDataBound="grvDanhSach_RowDataBound"
BorderStyle="None" BorderWidth="1px" Width="100%" runat="server">
<Columns>
<asp:TemplateField HeaderText="idCV" SortExpression="idCV" Visible="False">
<ItemTemplate>
<asp:Label ID="lblidCV" runat="server" Text='<%# Bind("idCV") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Số công văn" SortExpression="SoCV">
<ItemTemplate>
<asp:LinkButton ID="lbtSoCV" Text='<%# Bind("SoCV") %>' CommandArgument='<%#Eval("FileToanVan")%>'
OnClick="OpenPDF" runat="server" />
<asp:Image ID="imgSoCV" runat="server" ImageUrl="~/imagesew.gif" />
</ItemTemplate>
<HeaderStyle Width="200px" />
</asp:TemplateField>
<asp:BoundField DataField="RowNum" HeaderText="RowNum" SortExpression="RowNum" Visible="false" />
<asp:BoundField DataField="FileToanVan" HeaderText="FileToanVan" SortExpression="FileToanVan"
Visible="false" />
<asp:BoundField DataField="TrichYeu" HeaderText="Trích yếu" SortExpression="TrichYeu" />
<asp:BoundField DataField="NgayNhan" HeaderText="Ngày nhận" SortExpression="NgayNhan"
DataFormatString="{0:dd/MM/yyyy}">
<HeaderStyle Width="80px" />
</asp:BoundField>
<asp:TemplateField ItemStyle-HorizontalAlign="Center" HeaderText="Đánh dấu">
<ItemTemplate>
<asp:LinkButton ID="lbtMark" runat="server" OnClick="lbtMark_Click">[IMG]../images/mark-task.png[/IMG]</asp:LinkButton>
</ItemTemplate>
<HeaderStyle Width="70px" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField ItemStyle-HorizontalAlign="Center" HeaderText="Chuyển ->">
<ItemTemplate>
<asp:LinkButton ID="lbtChuyen" runat="server" OnClick="lbtChuyen_Click">[IMG]../images/change_process.png[/IMG]</asp:LinkButton>
</ItemTemplate>
<HeaderStyle Width="80px" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="TrangThai" SortExpression="TrangThai" Visible="False">
<ItemTemplate>
<asp:Label ID="lblTrangThai" runat="server" Text='<%# Bind("TrangThai") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td align="right">
<asp:HiddenField ID="hidUserID" runat="server" />
<asp:Literal ID="ltlPhanTrang" runat="server"></asp:Literal>
</td>
</tr>
</table>
c# code
Mã:
public DataSet CongVan_SelectbyUserName(
string UserName,
string SoCongVan,
string Keyword,
string idLoaiCV,
string FromDate,
string ToDate,
int currpage,
int rowperpage,
int Pagesize)
{
DataSet set = new DataSet();
SqlParameter[] arrParam = {
new SqlParameter("@UserName", SqlDbType.NVarChar),
new SqlParameter("@SoCV", SqlDbType.NVarChar),
new SqlParameter("@Keyword", SqlDbType.NVarChar),
new SqlParameter("@idLoaiCV", SqlDbType.NVarChar),
new SqlParameter("@FromDate", SqlDbType.NVarChar),
new SqlParameter("@ToDate", SqlDbType.NVarChar),
new SqlParameter("@currpage", SqlDbType.Int),
new SqlParameter("@rowperpage", SqlDbType.Int),
new SqlParameter("@Pagesize", SqlDbType.Int)
};
arrParam[0].Value = UserName;
arrParam[1].Value = SoCongVan;
arrParam[2].Value = Keyword;
arrParam[3].Value = idLoaiCV;
arrParam[4].Value = FromDate;
arrParam[5].Value = ToDate;
arrParam[6].Value = currpage;
arrParam[7].Value = rowperpage;
arrParam[8].Value = Pagesize;
return ThucThiStoreTraVeDataSet("spTB_CongVan_Search_UserName_Paging", arrParam);
}
private void LoadData(string UserName,string SoCV, string Keyword, string idLoaiCV, string FromDate, string ToDate,int currpage, int rowperpage, int Pagesize)
{
Services.cTB_CongVan mCongVan = new Services.cTB_CongVan();
DataSet ds = mCongVan.CongVan_SelectbyUserName(UserName,SoCV, Keyword, idLoaiCV, FromDate, ToDate, currpage, rowperpage, Pagesize);
DataTable dtbData = ds.Tables[0];
DataTable dtbPhanTrang = ds.Tables[1];
if (dtbData.Rows.Count > 0)
{
pnlDanhSach.GroupingText = "Tìm thấy " + dtbData.Rows.Count.ToString() + " công văn";
grvDanhSach.DataSource = dtbData;
grvDanhSach.DataBind();
if (dtbPhanTrang.Rows.Count > 0)
{
ltlPhanTrang.Text = dtbPhanTrang.Rows[0]["PhanTrang"] + "";
}
}
else
{
pnlDanhSach.GroupingText = "Tìm thấy 0 công văn";
grvDanhSach.DataBind();
}
mCongVan = null;
}
protected void lbtFind_Click(object sender, EventArgs e)
{
grvDanhSach.DataSource = null;
LoadData(hidUserID.Value, txtSoCV.Text + "", txtKeyword.Text + "", cboLoaiCV.SelectedValue, txtFromDate.Text + "", txtToDate.Text + "", currPage, 30, 5);
}
Bạn nào biết lỗi ở đâu chỉ tôi với. Cảm ơn!
View more random threads:
Thông qua phân tích dữ liệu Google từ 86 quốc gia, mới đây, một công ty tại Anh đã công bố bảng xếp hạng kích tấc "cậu nhỏ" của các nước trên thế giới. Kết quả, hầu hết các nước xếp ở nhóm đầu của...
"Chim" của chàng trai Việt thuộc...