ASP中调用Access中的查询_asp多表查询access-优快云博客

用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
%>