A general method for paging display records with ASP and SQL Server2000 programming

server|server2000|sql| Programming | paging | display

A general method for paging display records with ASP and SQL Server2000 programming

In the work, because needs, needs a network inquiry system, therefore has carried on the webpage design. Used to do web programming often use the PHP language and MySQL database, this time, because the restrictions, must use the ASP language and SQL Server2000, so this has been a number of reading. found that SQL Server and ASP are not matched like MySQL

Select ... limit ...

It is more inconvenient for a statement to start with only the required records from a record.

To query the information, browse the record, sometimes a large number of records, need to be paged display. I made a general function that is very convenient to use. Define the header in the Web page, using this function to generate query results based on query criteria.

I've just intercepted a part of my program and made some changes, which is enough to illustrate this feature. It's mostly down here.

F_multipage_disp

Procedure and call it when you need it. One of the features of my program is modularity. All statements for database table operations are in a file, such as test_dbf.asp. In this way, query debugging is very convenient.

I do not have much comment on the program, because it is too simple, I believe you can understand.

Of course, you can also change this, what good methods and suggestions can be sent to me mail, I am very grateful.

test_dbf.asp file:

<%
' Author: Wang Bongwu
' Date: 2007, 5, 29
' Objective: To distribute the common methods of displaying Web pages
' Contact: darlingpeng@sina.com; Pengwuwang@sohu.com
' Language: ASP, SQL Server 2000

Set l_conn = Server.CreateObject ("ADODB. Connection ")" Defines the connection
L_conn. Open "Filedsn=c:\program Files\Common Files\ODBC\Data Sources\warehouse.dsn; Uid=sa; Pwd=123 "' Open connection
Set l_rs = Server.CreateObject ("ADODB. Recordset ")" produces a recordset

function F_multipage_disp (L_url, L_seek, L_value, L_action, L_page, L_count) ' multi-page display
If L_rs. RecordCount <= 0 Then Response.End ' no information

L_rs. PageSize = G_page_size ' g_page_size for the page size of the manifesto
L_pages = (l_rs. RecordCount \ G_page_size)
If L_rs. RecordCount MoD g_page_size <> 0 Then l_pages = l_pages + 1 ' find pages
Select Case L_action
Case "Home" L_page = 1
Case "front page" l_page = l_page-1
Case "Back page" l_page = l_page + 1
Case "last" l_page = L_pages
End Select
If L_page < 1 then l_page = 1
If L_page > l_pages then l_page = l_pages
L_rs. AbsolutePage = L_page
L_ret = "<tr><td colspan= '" + CStr (l_count) + "' align= ' right ' >"
L_ret = L_ret + "<form method= ' POST ' action= '" + L_url + "' >"
L_ret = L_ret + "First" + CStr (l_page) + "/" + CStr (l_pages) + "page"
L_ret = L_ret + "<input type= ' hidden ' name= ' page ' value= '" + CStr (l_page) + "' >"
If l_pages > 1 Then
If L_page > 1 Then
L_ret = L_ret + "<input type= ' submit ' name= ' do ' value= ' home ' >"
L_ret = L_ret + "<input type= ' submit ' name= ' do ' value= ' front page ' >"
End If
If L_page < l_pages Then
L_ret = L_ret + "<input type= ' submit ' name= ' do ' value= ' after page ' >"
L_ret = L_ret + "<input type= ' submit ' name= ' do ' value= ' last ' >"
End If
End If
L_ret = L_ret + "</form>"
L_ret = L_ret + "</td></tr>"
F_multipage_disp = L_ret
End Function

Sub F_disp_value (L_seek, L_value, L_action, l_page) ' Display information
Str_where = ""
Select Case L_seek
Case "Name"
Str_where = "where f_name like '%" + l_value + "%"
Case "School"
Str_where = "where f_school like '%" + l_value + "%"
End Select
L_sql = "Select F_name as name, f_gender as sex, F_school as school, f_city as city, F_pcode as zip code, f_email as mailbox, F_tel as phone, f_addr as Address, F_memo as memo from T_test "+ Str_where +" ORDER by F_name,f_gender
L_rs. Open L_sql, L_conn, 1, 1
L_ret = F_multipage_disp ("t_test2.asp", L_seek, L_value, L_action, L_page, 9) ' Call function, note last parameter 9
Dim l_s (8)
j = 0
Do, not l_rs.eof and J < G_page_size
j = j + 1
For I=0 to 8
l_s (i) = L_rs (i)
If IsNull (l_s (i)) then l_s (i) = ""
Next
Response.Write ("<tr bgcolor= ' eeeeee ' valign= ' top ' ><td>" _
+ l_s (0) + "</td><td>" _
+ l_s (1) + "</td><td>" _
+ l_s (2) + "</td><td>" _
+ l_s (3) + "</td><td>" _
+ l_s (4) + "</td><td>" _
+ l_s (5) + "</td><td>" _
+ l_s (6) + "</td><td>" _
+ l_s (7) + "</td><td>" _
+ l_s (8) + "</td></tr>")
L_rs. MoveNext
Loop
Response.Write (L_ret)
L_rs. Close
End Sub
%>

test.asp file

<%
If Request.ServerVariables ("request_method") = "POST" Then
Session ("Si_msel") = Request.Form ("Select_value")
Session ("si_mval") = Request.Form ("Txt_value")
L_seek = Session ("Si_msel")
L_value = Session ("Si_mval")
L_page = Request.Form ("page")
L_action = Request.Form ("Do")
End If
%>

<title> Query System </title>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<link href= "Css/normal.css" rel= "stylesheet" type= "Text/css" >
<body>
<div align= "center" class= "Cap_text" > Information Enquiry Form
<table width= "90%" border= "0" cellspacing= "0" cellpadding= "0" >
<tr>
<td><form name= "Form1" method= "Post" action= "test.asp" >
Query criteria:
<select name= "Select_value" >
<option <% If session ("Si_msel") = "name" Then Response.Write ("selected")%>> name </option>
<option <% If session ("Si_msel") = "School" Then Response.Write ("selected")%>> school </option>
</select>
<input type= "text" name= "Txt_value" value= "<% =session (" Si_oval ")%>" >
<input type= "Submit" name= "Submit" value= "Enquiry" >
</form></td>
</tr>
</table>
</div>
<table width= "90%" border= "0" align= "center" cellpadding= "2" >
<tr bgcolor= "#CCCCCC" >
&LT;TD width= "12%" > Name </td>
&LT;TD width= "5%" > Sex </td>
&LT;TD width= "10%" > School </td>
&LT;TD width= "15%" > City </td>
&LT;TD width= "5%" > Zip </td>
&LT;TD width= "10%" > Mailbox </td>
&LT;TD width= "8%" > Tel </td>
&LT;TD width= "11%" > Address </td>
&LT;TD width= "26%" > Notes </td>
</tr>
<!--#include file=test_dbf.asp-->
<%
Call F_disp_value (L_seek, L_value, L_action, L_page)
%>
</table>
</body>