用Asp 写程序时经常会用到 Access 做为后台数据库,Access 相对SQL Server 来说 功能是弱了一点,
其中就是数据库段编程功能,前一段时间在网上看到一些用Command 掉用Access 查询的方法,参数传递用到数组,稍显麻烦,
昨天看到NB文章系统里的写法,更简洁明了,于是就整理了一下添出来,请留意红字部分
<%
'===============================================
' 数据库操作类,记录集中以二维数组的形式返回
' 数组第一维是列,第二维是记录行
' Access 中的查询调用
' 如在 Access 中有如下查寻 vi_Hot_Aritcle
' Select Title,Author,AddTime,Hits From [Article] Where
' Title Like v_Title And (AddTime Between v_sTime And v_eTime)
' And Hits > v_Hits
' And Passed=v_Passed Order By AddTime Desc,Hits Desc
' 则调用代码如下
' SQL="Exec vi_Hot_Article " & " """ & Title & """ " & "," _
' & "#" & s_Time & "#" & "," & "#" & e_Time & "#" & "," _
' & Hits & "," & -1
'============================================
' 说明:
' 时间用 #2005-11-11#,
' 字符串用'abc'或"%abc%"(%为通配符)
' 数字 直接写
' 是否 否为0,是为-1
'============================================
' 执行方式
' Rs.Open SQL,conn,1,1 '分页
' Set Rs=conn.Execute(SQL) '返回全部
' conn.Execute(SQL) '无返回值
'==========================================
' 注意
' 查询在Access里面(通过双击运行)
' 时间输入 2005-1-15 则可
' 字符 直接输入
' 通配符 用 *
' 是否 也是 0与-1
'==============================================
class Cls_DB_Operator
private Rs
private SQL
private Sub Class_Initialize()
Set Rs=Server.CreateObject("ADODB.RecordSet")
End Sub
private Sub Class_Terminate()
If Rs.State=1 Then Rs.Close()
Set Rs=Nothing
End Sub
Public Function DB_GetScaleQuery(sSQL)
Set Rs=conn.Execute(sSQL)
If Not Rs.EOF Then
DB_GetScaleQuery=Rs(0)
Else
DB_GetScaleQuery=Null
End If
Rs.Close()
End Function
Public Function DB_Execute(sSQL)
conn.Execute(sSQL)
End Function
Public Function DB_Query(sSQL)
Set Rs=conn.Execute(sSQL)
If Not Rs.EOF Then
DB_Query=Rs.GetRows()
Else
DB_Query=0
End IF
Rs.Close()
End Function
Public Function DB_CutPageQuery(sSQL,pageNum,pageSize)
If Rs.State=1 Then Rs.Close()
Rs.Open SQL,conn,1,1
If Not Rs.EOF Then
Rs.AbsolutePosition=Rs.AbsolutePosition +((Abs(PageNum)-1) * pageSize)
DB_CutPageQuery=Rs.GetRows(pageSize)
Else
DB_CutPageQuery=0
End If
Rs.Close()
End Function
End Class
%>
<%
'//测试代码
Dim DbOP,Num,SQL,Arr
Set DbOP=New Cls_DB_Operator
SQL="Exec vi_Hot_Article " & """%%""," & "#2005-8-29#" & "," & "#2006-12-1#" & "," & 0 & _
"," & -1
Arr=DbOP.DB_Query(SQL)
If IsArray(Arr) Then
For i=0 To Ubound(Arr,2)
Response.Write(Arr(0,i) & "<br>")
Next
Else
'// No Return
End If
%>