自编DataBase函数库,内有参数查询及分页操作

下面是DataBase.asp的完整代码。内有许多不相干的自编的偷懒函数(大家不要学我,我是嫌VB太啰嗦),你不需要可以去掉

 

<%
'以下代码为通用代码
Option Explicit
Const SqlUserID = "sa"         'SQL数据库用户名
Const SqlPassword = "gdcc"       'SQL数据库用户密码
Const SqlDatabaseName = "Test"  'SQL数据库名
Const SqlHostIP = "(local)"      'SQL主机IP地址。本地(指网站与数据库在同一台服务器上)可用“(local)”或“127.0.0.1”,非本机(指网站与数据库分别在不同的服务器上)请填写数据库服务器的真实IP)

DIM QUERY_STRING
QUERY_STRING = Request.ServerVariables("QUERY_STRING")
DIM PATH_INFO '当前文件路径
PATH_INFO = Request.ServerVariables("PATH_INFO")

'用于分页查询的全局变量
Dim TableName,KeyID,FieldList,Cond,Desc

'以下代码请勿改动
Dim Conn ,rs, sql,i
Sub OpenConn()
    'On Error Resume Next
 if IsObject(Conn) then exit sub
    Dim ConnStr
    ConnStr = "Provider = Sqloledb; User ID = " & SqlUserID & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlHostIP & ";"
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.open ConnStr
End Sub

Sub CloseConn()
    If IsObject(Conn) Then
        Conn.Close
        Set Conn = Nothing
    End If
End Sub

'the Content below added by wang 2008-3-13
'准备参数
private function parpareCmd(sql,ps)
 OpenConn
 dim p, cmd, rs , pnum
 set cmd = Server.CreateObject("Adodb.Command")
 cmd.CommandText = sql
 cmd.ActiveConnection = Conn
 pnum = ParamNum(sql)
 
 '检查参数个数是否小于必需数目。
 'CreateParameter([Name] , [type] , [Direction] , [Size] , [Value] )
 if (not isnull(ps)) then
  if  (ubound(ps)+1) < pnum then
   Response.write "参数个数应该是 " & pnum & ". 但实际只传递了" & (ubound(ps)+1)
   Response.end
  end if
  for i = 0 to pnum-1
   set p = cmd.CreateParameter( , 130, ,chkLength(Ps(i)), Ps(i))
   'response.write "P" & I & "=" & Ps(i) & "L:" &chkLength(Ps(i)) & "<br />"
   cmd.Parameters.Append p
  next
 end if
 set parpareCmd = cmd
end function

'通过sql中'?'号的个数得知参数个数
function ParamNum(sql)
 ParamNum = 0
 for i=1 to LEN(sql)
  if mid(sql,i,1)="?" then ParamNum = ParamNum + 1
 next
end function

'执行更新查询
function ExecNonQuery(sql,ps)
 dim cmd,rs
 set cmd = parpareCmd(sql,ps)
 set rs = cmd.Execute
 set cmd=nothing
 set rs=nothing
end function

'返回单个整数,如果为空则当作0处理
function ExecInt(sql, ps)
 dim cmd,rs
 set cmd = parpareCmd(sql,ps)
 set rs = cmd.Execute
 ExecInt=TurnInt(Rs(0))
 set rs=nothing
 set cmd=nothing
end function

'返回单个字符串,如果为空则当作空串处理
function ExecStr(sql, ps)
 dim cmd,rs
 set cmd = parpareCmd(sql,ps)
 set rs = cmd.Execute
 if isnull(Rs(0)) then
  ExecStr = ""
 else
  ExecStr=CStr(Rs(0))
 end if
 set rs=nothing
 set cmd=nothing
end function

'返回一个记录集
function ExecRs(sql, ps)
 dim cmd,rs
 set cmd = parpareCmd(sql,ps)
 set rs = cmd.Execute()
 set ExecRs=rs
 set cmd=nothing
end function

function chkLength(s)
 if isnull(s) then
  chkLength = 2
 else
  chkLength = max(2,len(s))
 end if
end function

function ChkFormNull(fn)
 ChkFormNull = trim(RF(fn))
 if ChkFormNull = "" then  ChkFormNull = null
end function

function ChkNull(s)
 ChkNull = trim(s)
 if ChkNull = "" then  ChkNull = null
end function

function iif(a,b,c)
 if a then iif=b else iif=c
end function

'将数字格式化成带前导0的字符串
function fill0(n,znum)
 dim sn,i
 sn = cstr(n)
 if len(sn) > znum then
  fill0 = "####"
 else
  for i=1 to znum-len(sn)
   sn= "0" + sn
  next
  fill0 = sn
 end if
end function

'数字转换,将null值视为0处理
function TurnInt(c)
 if isnull(c) or c="" or not IsNumeric(c) then
  TurnInt=0
 else
  TurnInt=CLng(c)
 end if
end function

function max(i,j)
 max = iif(i>j,i,j)
end function

'用正则表达式测试strng中是否有patrn
Function CheckExp(patrn, strng)
 Dim regEx, matches '建立变量。
 Set regEx = New RegExp '建立正则表达式。
 regEx.Pattern = patrn '设置模式。
 regEx.IgnoreCase = true '设置是否区分字符大小写。
 regEx.Global = True '设置全局可用性。
 Matches = regEx.test(strng) '执行搜索。
 CheckExp = matches
End Function

''''''''''''''''''''''''Cookies操作'''''''''''''''
Sub SetCookie(n, s)
 Response.Cookies("Toers")(n) = s
end Sub

Sub SetCookieDate(d)
 Response.Cookies("Toers").Expires = Date + d
End Sub

function GetCookie(n)
 GetCookie = Request.Cookies("Toers")(n)
end function

'文件类型
function FType(fn)
 FType = right(fn,len(fn)-InStrRev(fn,"."))
end function

'代码过滤,在进入数据库不需要过滤,在显示时需要
function HtmEncode(str)
 dim result
 dim l
 if isNULL(str) then
  HtmEncode=""
  exit function
 end if
 l=len(str)
 result=""
 dim i
 for i = 1 to l
 select case mid(str,i,1)
  case "<"
   result=result+"&lt;"
  case ">"
   result=result+"&gt;"
  case chr(13)
   result=result+"<br>"
  case chr(34)
   result=result+"&quot;"
  case "&"
   result=result+"&amp;"
  case chr(32) 
   'result=result+"&nbsp;"
   if i+1<=l and i-1>0 then
    if mid(str,i+1,1)=chr(32) or mid(str,i+1,1)=chr(9) or mid(str,i-1,1)=chr(32) or mid(str,i-1,1)=chr(9)  then 
     result=result+"&nbsp;"
    else
     result=result+" "
    end if
   else
    result=result+"&nbsp;" 
   end if
  case chr(9)
   result=result+"    "
  case else
   result=result+mid(str,i,1)
 end select
 next
 HtmEncode=result
end function

''''''''''''对Rs("XXX")的处理
function RsStr(n)
 if isnull(rs(n)) then
  RsStr=""
 else
  rsStr =Trim(rs(n))
 end if
end function

function RsLongStr(n)
 if isnull(rs(n)) then
  RsLongStr=""
 else
  RsLongStr=HtmEncode(rs(n))
 end if
end function

function RsBool(n)
 if isnull(rs(n)) then
  RsBool =false
 else
  RsBool = CBool(rs(n))
 end if
end function

function RsDate(n)
 if isnull(rs(n)) then
  RsDate =""
 else
  RsDate = FormatDateTime(rs(n),vbShortDate)
 end if
end function

function RsInt(n)
  if isnull(rs(n)) then
   RsInt=0
 else
  RsInt=CLng(rs(n))
 end if
end function
''''''''''''对表单的处理''''''''''''''''''
function RQ(n)
 RQ = trim(Request.QueryString(n))
end function

function RF(n)
 RF = trim(Request.Form(n))
end function

' 将 1,2,3,4,5 转换成 '1','2','3','4','5'
function AddQuot(s)
 s  = replace(s," ","")
 AddQuot = "'" + replace(s,",","','") + "'"
end function

''''''''''''以下是对分页的处理''''''''''''''''''''''
'用于翻页的变量
DIM Page '页号
DIM PSize '页大小
DIM PCount '页数
DIM RCount '记录总数
Page= TurnInt(RQ("Page"))
if Page<1 then Page=1
if (PSize="") then PSize=15 Else PSize=CLng(PSize)

''''通用分页函数,返回页号为Page的记录集''''''
'TableName-表名
'KeyId-排序关键字名
'FieldList-要查询的字段列表
'Cond-条件
'DESC-是否降序
'Page-当前页号
'PSize-页大小
'RCount-记录总数
'Ps -查询要用到的参数数组
FUNCTION RsPage(TableName, KeyID, FieldList, Cond, Desc, Page, PSize, RCount,Ps)
 Dim boundID,Descs,MaxMinKeyID, GtLt,pcnt
 if Desc then
  Descs="ORDER BY " + KeyID +" DESC"
  MaxMinKeyID = "MIN("+KeyID+")"
  GtLt = "<"
 else
  Descs="ORDER BY " + KeyID
  MaxMinKeyID = "MAX("+KeyID+")"
  GtLt = ">"
 end if
 
 Sql = "SELECT COUNT("+KeyID+") FROM "+TableName+" WHERE "+ Cond
 RCount = ExecInt(sql,Ps)
 pcnt = (RCount-1) / PSize + 1
 if Page<1 then Page=1
 if Page>Pcnt then page=Pcnt
 sql = "SELECT "+MaxMinKeyID+" FROM (SELECT TOP "& (Page-1)*PSize & " " & KeyID &" FROM "+TableName+" WHERE ("+Cond+ ")"+DESCs + ") " & Left(KeyID,1)
 boundID=ExecStr(sql,Ps)
 if  boundID = "" then GtLt="<>"
 sql = "SELECT Top "&PSize & " " & FieldList & " FROM " & TableName &" WHERE (" & KeyID & GtLt &"'"& boundID & "') and (" & Cond & ")" & Descs
 set RsPage=ExecRs(sql,Ps)
End Function

'通用翻页子程序,用到了Page,PCount,PSize,RCount等全局变量。
SUB ShowPage(itemName)
 Dim re
 if QUERY_STRING <> "" then
  Set re = New RegExp
  re.Pattern = "[&/?]?Page=/d*"
  QUERY_STRING = re.Replace(QUERY_STRING,"")
 end if
 
 if QUERY_STRING <>"" then
  QUERY_STRING = "?" & QUERY_STRING & "&Page="
 else
  QUERY_STRING = "?Page="
 end if
 PCount = (RCount-1) / PSize + 1

 if PCount<=1 then Exit Sub

 Response.write " <div class='show_page'>共 <b>"&RCount&"</b> 个 " &itemName & "&nbsp;&nbsp;"
 Response.write "   <a href='"&QUERY_STRING&"1'>首页</a> "
 Response.write iif(Page>1,"  <a href='"&QUERY_STRING&(Page-1)&"'>上一页</a>", "上一页")
 Response.write iif(Page<PCount," <a href='"&QUERY_STRING&(Page+1)&"'>下一页</a>", "下一页")
 Response.write "  <a href='"&QUERY_STRING&PCount&"'>尾页</a> "
 Response.write " 页次:<strong><font color=red>"&Page&"</font>/"&PCount&"</strong>页 &nbsp;&nbsp;&nbsp;"
 if (PCount<20) then
    Response.write " 转到:<select name='page' size='1' οnchange=""location='"&QUERY_STRING&"'+this.options[this.selectedIndex].value;"">"
    For i = 1 To PCount
     Response.write "<option value='" & i & "'"
     If Page = i Then Response.write " selected='selected' "
     Response.write ">第" & i & "页</option>"
    Next
    Response.write "</select>"
 else
  Response.write " 转到第<input type='text' name='Page' size='2' maxlength='5' value='"&Page&"'"
  Response.write " onKeyPress=""if (event.keyCode==13)location='"&QUERY_STRING&"'+this.value;"" />页</div>"
 end if
End SUB
%>

  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏

bwangel

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者