[原创][VB]迷迷糊糊做成的DataList的分页--关键字查询,自动编号,URL链接
关键
1.要用PUBLIC 保存函数间的值
2.用VIEWSTATE保存变量，类似SESSION
1.要用PUBLIC 保存函数间的值
2.用VIEWSTATE保存变量，类似SESSION
1
<%@ Page Language="VB" AutoEventWireup="True"%>
2<%@ Import Namespace="System.Data" %>
3<%@ Import Namespace="system.Data.SqlClient"%>
4<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head id="Head1" runat="server">
7 <title>DataListPager</title>
8 <script language="vb" runat="server">
9 Public str As String
10 Public key As String
11 Public currentPage As Int32 '定义变量用来保存当前页索引
12 Public i As Integer
13 Public PageSize As Integer
14 Public objPage As New PagedDataSource '创建PAGE对象
15 Public currentPageIndex As Integer
16 Public emplyID As String
17
18
19
20 Sub Page_load(ByVal sender As Object, ByVal e As EventArgs)
21 emplyID = Request.QueryString("emplyID")
22
23
24 If Not Me.IsPostBack Then
25 ''第一次执行,未获得TEXTBOX的值时
26
27
28
29 If emplyID <> "" Then
30 str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]<='" + emplyID + "'"
31 ViewState("Key") = emplyID
32 Else
33 str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"
34 ViewState("Key") = 100
35 End If
36
37
38 '判断是否具有页面跳转的请求 Begin
39 Dim topage As Integer
40 topage = Request.QueryString("topage")
41
42 If topage <> 0 Then
43 currentPageIndex = topage - 1
44
45 Else
46 currentPageIndex = 0 '默认为0
47 End If
48
49 '判断是否具有页面跳转的请求 Begin
50
51 Response.Write("<font color='red'>如果显示这行则表明为第一次显示及没有获得BTN事件!</font>")
52
53 DataListDataBind(str, ViewState("Key"), currentPageIndex)
54
55 Else
56
57
58
59
60 If emplyID <> "" Then
61 str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]<='" + emplyID + "'"
62 Else
63 str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"
64 End If
65
66 ' str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"
67 ' ViewState("Key") = 100
68 ViewState("Key") = emplyID
69
70 currentPageIndex = 0
71
72 DataListDataBind(str, ViewState("Key"), currentPageIndex)
73
74 End If
75
76 End Sub
77
78
79
80
81 Sub DataListDataBind(ByVal str As String, ByVal key As String, ByVal currentPageIndex As Integer)
82
83
84
85
86 Dim conn As SqlConnection
87 Dim ds As DataSet
88 Dim adapter As SqlDataAdapter
89 PageSize = 2
90
91 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
92 ''str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]='1' "
93 Me.strReBind.Text = "<b>" + str + "</b><br>"
94
95 adapter = New SqlDataAdapter(str, conn)
96
97 ds = New DataSet()
98 '' adapter.Fill(ds, startIndex, PageSize, "employees")
99 adapter.Fill(ds, "employees")
100
101
102 ''Dim i As New PagedDataSource
103 objPage.DataSource = ds.Tables("employees").DefaultView '设置数据源
104 objPage.AllowPaging = True
105 objPage.PageSize = PageSize
106
107
108
109
110 objPage.CurrentPageIndex = currentPageIndex ''设置当前页的索引 默认为 0
111 '显示状态信息
112 Label1.Text = "共有[" + objPage.PageCount.ToString() + "]页,当前页为:" + (currentPageIndex + 1).ToString() + "页;"
113
114
115
116 If objPage.IsFirstPage = False Then
117 linkPre.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=" + Convert.ToString(currentPageIndex + 1 - 1)
118 linkFirst.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=1"
119
120 End If
121
122 If objPage.IsLastPage = False Then
123 Me.linkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=" + (currentPageIndex + 1 + 1).ToString
124 Me.linkLast.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=" + objPage.PageCount.ToString
125
126 End If
127
128
129 Me.myDataList.DataSource = objPage
130 Me.myDataList.DataBind()
131
132
133
134 End Sub
135
136 ' Sub R1_ItemCommand(ByVal Sender As Object, ByVal e As DataListCommandEventArgs)
137 'Dim i As Int16 = Convert.ToInt16(e.Item.ItemIndex)
138
139 'End Sub
140
141 Sub btnSearch(ByVal sender As Object, ByVal e As EventArgs)
142 Dim str As String
143 'Dim key As String
144
145 ViewState("Key") = keywords.Text
146
147 '' Response.Write(key.ToString + "<br>")
148
149
150 If ViewState("Key") <> "" Then
151 str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]<='" + ViewState("Key") + "'"
152
153 Else
154 str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"
155 End If
156 Me.Labelstr.Text = "BTN查询后的事件" + str
157 currentPageIndex = 0
158 DataListDataBind(str, ViewState("Key"), currentPageIndex)
159
160
161 End Sub
162
163 Sub DataList_PageIndex(ByVal sender As Object, ByVal e As DataListItemEventArgs)
164 ' Dim l As Label = CType(e.Item.FindControl("PageIndex"), Label)
165 Dim l As Label
166 l = e.Item.FindControl("PageIndex")
167
168 If Not (l Is Nothing) Then
169
170 l.Text = (currentPageIndex + 1) * PageSize - PageSize + e.Item.ItemIndex + 1
171
172 End If
173
174 End Sub
175
176 'num 是 当前页码值,（自己定义）
177 '*32 是要乘以分页的行数，（如：每页32行）
178 'ItemIndex 是行索引
179 '+ 1 是因为索引值从0开始的
180 '# (num-1)*30+Container.ItemIndex + 1
181 '(currentPage-1)*2++Container.ItemIndex + 1
182</script>
183</head>
184<body>
185 <form id="form1" runat="server">
186 <div>
187 关键字:<asp:TextBox ID="keywords" runat="server"></asp:TextBox>
188 <asp:Button ID="btn" OnClick="btnSearch" runat="server" Text="Search" />
189 <br />
190 <asp:Label ID="strReBind" runat="server" Text=""></asp:Label>
191 <br>
192 <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
193 <br />
194 <asp:Label ID="Labelstr" runat="server" Text=""></asp:Label>
195 <br/>
196 <asp:HyperLink ID="HyperLink1" NavigateUrl="DataListPager.aspx?emplyID=1" runat="server">[ID1]</asp:HyperLink>
197 | <asp:HyperLink ID="HyperLink2" NavigateUrl="DataListPager.aspx?emplyID=2" runat="server">[ID2]</asp:HyperLink>
198 | <asp:HyperLink ID="HyperLink3" NavigateUrl="DataListPager.aspx?emplyID=3" runat="server">[ID3]</asp:HyperLink>
199
200 <br/>
201
202
203 <asp:HyperLink ID="linkFirst" runat="server">首页</asp:HyperLink>
204 |<asp:HyperLink ID="linkPre" runat="server">上一页</asp:HyperLink>
205 |<asp:HyperLink ID="linkNext" runat="server">下一页</asp:HyperLink>
206 |<asp:HyperLink ID="linkLast" runat="server">末页</asp:HyperLink>
207 <br />
208 <br />
209 <asp:DataList OnItemCreated="DataList_PageIndex" ID="myDataList" runat="server" CellPadding="4" ForeColor="#333333" ShowFooter="False" ShowHeader="False">
210
211 <ItemTemplate>
212
213 <li>
214 <asp:Label ID="PageIndex" Runat="server"></asp:Label>--
215
216 <%#(currentPageIndex + 1 - 1) * 2 + +Container.ItemIndex + 1%>
217
218 | <%#Eval("LastName") + "--" + Eval("FirstName")%>
219 </li>
220 </ItemTemplate>
221 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
222 <SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
223 <AlternatingItemStyle BackColor="White" />
224 <ItemStyle BackColor="#EFF3FB" />
225 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
226 </asp:DataList>
227
228
229 </div>
230 </form>
231</body>
232</html>
233
<%@ Page Language="VB" AutoEventWireup="True"%>2
<%@ Import Namespace="System.Data" %>3
<%@ Import Namespace="system.Data.SqlClient"%>4
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">5
<html xmlns="http://www.w3.org/1999/xhtml" >6
<head id="Head1" runat="server">7
<title>DataListPager</title>8
<script language="vb" runat="server">9
Public str As String10
Public key As String11
Public currentPage As Int32 '定义变量用来保存当前页索引12
Public i As Integer13
Public PageSize As Integer14
Public objPage As New PagedDataSource '创建PAGE对象15
Public currentPageIndex As Integer16
Public emplyID As String17
18
19
20
Sub Page_load(ByVal sender As Object, ByVal e As EventArgs)21
emplyID = Request.QueryString("emplyID")22
23
24
If Not Me.IsPostBack Then25
''第一次执行,未获得TEXTBOX的值时26
27
28
29
If emplyID <> "" Then30
str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]<='" + emplyID + "'"31
ViewState("Key") = emplyID32
Else33
str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"34
ViewState("Key") = 10035
End If36
37
38
'判断是否具有页面跳转的请求 Begin39
Dim topage As Integer40
topage = Request.QueryString("topage")41
42
If topage <> 0 Then43
currentPageIndex = topage - 144
45
Else46
currentPageIndex = 0 '默认为047
End If48
49
'判断是否具有页面跳转的请求 Begin50
51
Response.Write("<font color='red'>如果显示这行则表明为第一次显示及没有获得BTN事件!</font>")52
53
DataListDataBind(str, ViewState("Key"), currentPageIndex)54
55
Else56
57
58
59
60
If emplyID <> "" Then61
str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]<='" + emplyID + "'"62
Else63
str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"64
End If65
66
' str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"67
' ViewState("Key") = 10068
ViewState("Key") = emplyID69
70
currentPageIndex = 0 71
72
DataListDataBind(str, ViewState("Key"), currentPageIndex)73
74
End If75
76
End Sub77
78
79
80
81
Sub DataListDataBind(ByVal str As String, ByVal key As String, ByVal currentPageIndex As Integer)82
83
84
85
86
Dim conn As SqlConnection87
Dim ds As DataSet88
Dim adapter As SqlDataAdapter89
PageSize = 290
91
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("Northwind").ConnectionString)92
''str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]='1' "93
Me.strReBind.Text = "<b>" + str + "</b><br>"94
95
adapter = New SqlDataAdapter(str, conn)96
97
ds = New DataSet()98
'' adapter.Fill(ds, startIndex, PageSize, "employees")99
adapter.Fill(ds, "employees")100
101
102
''Dim i As New PagedDataSource103
objPage.DataSource = ds.Tables("employees").DefaultView '设置数据源104
objPage.AllowPaging = True105
objPage.PageSize = PageSize106
107
108
109
110
objPage.CurrentPageIndex = currentPageIndex ''设置当前页的索引 默认为 0 111
'显示状态信息112
Label1.Text = "共有[" + objPage.PageCount.ToString() + "]页,当前页为:" + (currentPageIndex + 1).ToString() + "页;"113
114
115
116
If objPage.IsFirstPage = False Then117
linkPre.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=" + Convert.ToString(currentPageIndex + 1 - 1)118
linkFirst.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=1"119
120
End If121
122
If objPage.IsLastPage = False Then123
Me.linkNext.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=" + (currentPageIndex + 1 + 1).ToString124
Me.linkLast.NavigateUrl = Request.CurrentExecutionFilePath + "?emplyID=" + key + "&toPage=" + objPage.PageCount.ToString125
126
End If127
128
129
Me.myDataList.DataSource = objPage130
Me.myDataList.DataBind()131
132
133
134
End Sub135
136
' Sub R1_ItemCommand(ByVal Sender As Object, ByVal e As DataListCommandEventArgs)137
'Dim i As Int16 = Convert.ToInt16(e.Item.ItemIndex)138
139
'End Sub140
141
Sub btnSearch(ByVal sender As Object, ByVal e As EventArgs)142
Dim str As String143
'Dim key As String144
145
ViewState("Key") = keywords.Text146
147
'' Response.Write(key.ToString + "<br>")148
149
150
If ViewState("Key") <> "" Then151
str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees] where [EmployeeID]<='" + ViewState("Key") + "'"152
153
Else154
str = "SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"155
End If 156
Me.Labelstr.Text = "BTN查询后的事件" + str157
currentPageIndex = 0158
DataListDataBind(str, ViewState("Key"), currentPageIndex)159
160
161
End Sub162
163
Sub DataList_PageIndex(ByVal sender As Object, ByVal e As DataListItemEventArgs)164
' Dim l As Label = CType(e.Item.FindControl("PageIndex"), Label)165
Dim l As Label166
l = e.Item.FindControl("PageIndex")167
168
If Not (l Is Nothing) Then169
170
l.Text = (currentPageIndex + 1) * PageSize - PageSize + e.Item.ItemIndex + 1171
172
End If173
174
End Sub175
176
'num 是 当前页码值,（自己定义）177
'*32 是要乘以分页的行数，（如：每页32行）178
'ItemIndex 是行索引179
'+ 1 是因为索引值从0开始的180
'# (num-1)*30+Container.ItemIndex + 1181
'(currentPage-1)*2++Container.ItemIndex + 1182
</script>183
</head>184
<body>185
<form id="form1" runat="server">186
<div>187
关键字:<asp:TextBox ID="keywords" runat="server"></asp:TextBox>188
<asp:Button ID="btn" OnClick="btnSearch" runat="server" Text="Search" />189
<br />190
<asp:Label ID="strReBind" runat="server" Text=""></asp:Label>191
<br>192
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>193
<br />194
<asp:Label ID="Labelstr" runat="server" Text=""></asp:Label>195
<br/>196
<asp:HyperLink ID="HyperLink1" NavigateUrl="DataListPager.aspx?emplyID=1" runat="server">[ID1]</asp:HyperLink>197
| <asp:HyperLink ID="HyperLink2" NavigateUrl="DataListPager.aspx?emplyID=2" runat="server">[ID2]</asp:HyperLink>198
| <asp:HyperLink ID="HyperLink3" NavigateUrl="DataListPager.aspx?emplyID=3" runat="server">[ID3]</asp:HyperLink>199
200
<br/>201
202
203
<asp:HyperLink ID="linkFirst" runat="server">首页</asp:HyperLink> 204
|<asp:HyperLink ID="linkPre" runat="server">上一页</asp:HyperLink> 205
|<asp:HyperLink ID="linkNext" runat="server">下一页</asp:HyperLink>206
|<asp:HyperLink ID="linkLast" runat="server">末页</asp:HyperLink> 207
<br />208
<br />209
<asp:DataList OnItemCreated="DataList_PageIndex" ID="myDataList" runat="server" CellPadding="4" ForeColor="#333333" ShowFooter="False" ShowHeader="False">210
211
<ItemTemplate>212
213
<li>214
<asp:Label ID="PageIndex" Runat="server"></asp:Label>--215
216
<%#(currentPageIndex + 1 - 1) * 2 + +Container.ItemIndex + 1%>217
218
| <%#Eval("LastName") + "--" + Eval("FirstName")%>219
</li>220
</ItemTemplate>221
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />222
<SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />223
<AlternatingItemStyle BackColor="White" />224
<ItemStyle BackColor="#EFF3FB" />225
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />226
</asp:DataList>227
228
229
</div>230
</form>231
</body>232
</html>233
