位置：首页>> 网络编程>> Asp编程>> 如何操作Access数据库？

如何操作Access数据库？

　来源：asp之家　发布时间：2009-11-11 19:23:00　

标签：access,数据库,操作

本文实例介绍了asp对access数据库常见操作方法，查找记录，添加记录更新记录，删除记录等。

一、查找记录

find.htm
<HTML>
<HEAD>
<TITLE>精彩春风之Access数据库记录查找</TITLE>
</HEAD>
<BODY>
<FORM ACTION="find.asp" METHOD="POST" id=form1 name=form1>
  请输入您要查找的人的姓名: <BR>
  <INPUT TYPE="TEXT" NAME="man" SIZE=50><BR><BR>
   <INPUT TYPE="submit" NAME="send" VALUE="他／她的id是多少?">
</FORM>
</BODY>
</HTML>

find.asp
<％
  Option Explicit
％>

<HTML>
<BODY>
<％
  Dim strman, strfind , objConn
  strman = Request.Form("man")
  Dim objRS
  Set objconn = Server.CreateObject("ADODB.Connection")
  set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Man.mdb"))
objRS.Open "Names", objConn, adOpenStatic, adLockReadOnly, adCmdTable
  strfind = "Name='" & strman & "'"
  objRS.Find strfind
' 显示第一条相关记录
  If objRS.EOF Then
    Response.Write "哦,没有找到您要查找的人!" & _
                    strman
  Else
    Response.Write "他／她的姓名是: " & strman & "" & _
         "Id是:" & objRS("ID") & "<BR>" & _
  End If
  objRS.Close
  Set objRS = Nothing
  objConn.Close
  Set objConn = Nothing
％>
</BODY>
</HTML>

二、过滤记录

filter.asp
<％
  Option Explicit
％>

<HTML>
<HEAD>
<TITLE>精彩春风之Access数据库记录过滤</TITLE>
</HEAD>
<BODY>
<％
  Dim objRS, intLetter, strChar, strCriteria objConn
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Man.mdb"))
  objRS.Open "Names", objConn, adOpenStatic, adLockReadOnly,
adCmdTable
  For intLetter = 1 To 26
    strChar = Chr(intLetter+64)
    strCriteria = "Title LIKE '" & strChar & "*'"
    objRS.Filter = strCriteria
    If Not objRS.EOF Then
      Response.Write "<H2>" & strChar & "</H2>" & _
        "<TABLE BORDER=1><TR><TD><B>姓名<B></TD><TD>ID</TD></TR>"
      While Not objRS.EOF
        Response.Write "<TR><TD>" & objRS("Name") & "</TD>" & _
                           "<TD>" & objRS("ID") & "</TD></TR>"
        objRS.MoveNext
       Wend
      Response.Write "</TABLE>"
    End If
  Next
  objRS.Close
  Set objRS = Nothing
objConn.Close
Set objConn = Nothing
％>
</BODY>
</HTML>

三、添加记录

add.asp

<％
  Option Explicit
％>

<HTML>
<HEAD>
<TITLE>精彩春风之Access数据库记录添加</TITLE>
</HEAD>
<BODY>
<％
Dim objRS, intIDForNewRecord , objConn
Set objConn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Man.mdb"))
' 连接数据库
objRS.Open "Names", objConn, adOpenStatic, adLockOptimistic,
adCmdTable
  objRS.MoveLast
  intIDForNewRecord = objRS("ID") + 1
  objRS.AddNew
' 添加新记录
  objRS("ID") = intIDForNewRecord
  objRS("Names") = "ManD"
  objRS.Update
  objRS.Close
  objRS.Open "SELECT * FROM Names WHERE ID=" & intIDForNewRecord, _
                   ObjConn, adOpenForwardOnly, adLockReadOnly,
adCmdText
  If objRS.EOF Then
    Response.Write "噢,出错了!新记录没找到."
  Else
    Response.Write "恭喜,添加成功!新记录是:<BR> " & _
                   "Name = '" & objRS("Name") & "'<BR>" & _
                   "ID = '" & objRS("ID") & "'<BR>"
  End If
  objRS.Close
  Set objRS = Nothing
  objConn.Close
' 清空
  Set objConn = Nothing
％>
</BODY>
</HTML>

四、更新记录

和上面的“添加记录”一样，只需把“添加”命令改做“更新”就可以了：

objRS.Update

五、删除记录
This is the asp page, we will delete the record that we added ManD:

<％
  Option Explicit
％>

<HTML>
<HEAD>
<TITLE>精彩春风之Access数据库记录删除</TITLE>
</HEAD>
<BODY>
<％
  Dim objRS, intIDForNewRecord, objConn
Set objConn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Man.mdb"))
  objRS.Open "Names", objConn, adOpenStatic, adLockOptimistic,
adCmdTable
  objRS.Filter = "Name = ' ManD'"
  Response.Write "注意,以下记录将被删除:<BR> "
  While Not objRs.EOF
    Response.Write objRS("ID") & "<BR>"
    objRS.Delete
    objRS.MoveNext
  Wend
  objRS.Close
  Response.Write "<BR>核实删除记录:<BR>"
  objRS.Open "SELECT * FROM Names WHERE Name LIKE 'ManD'", _
                   objConn, adOpenForwardOnly, adLockReadOnly,
adCmdText
  If objRS.EOF Then
    Response.Write "ManD的所有记录都已被从数据库删除!<BR> "
  Else
    Response.Write "噢,删除失败!ManD的记录还在数据库里呢, " & _
                   "ID=" & objRS("ID")
  End If
  objRS.Close
  Set objRS = Nothing
  objConn.Close
  Set objConn = Nothing
％>
</BODY>
</HTML>

投稿

如何操作Access数据库？

猜你喜欢