操作数据库添加、修改、删除、查询的代码

第一种：这种也是Dreamweaver里默认用的

查询：

<%

' 先声明数据库连接

Dim MM_dbconn_STRING
MM_dbconn_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("/db/lxdb.mdb")

%>

 

<%

Dim Recordset1
Dim Recordset1_cmd

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_dbconn_STRING
Recordset1_cmd.CommandText = "SELECT * FROM ceshi" 
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
%>

显示表里面的其中一个字段：<%=(Recordset1.Fields.Item("id").Value)%>

<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

 

添加：  （这段代码有些地方要修改，整体是对的）

<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

Dim MM_abortEdit
MM_abortEdit = false
%>
<%
If (CStr(Request("MM_insert")) = "form1") Then
  If (Not MM_abortEdit) Then
    Dim MM_editCmd

    Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_dbconn_STRING
    MM_editCmd.CommandText = "INSERT INTO ceshi (UserName, Pwd) VALUES (?, ?)" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 50, Request.Form("textfield")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 50, Request.Form("textfield2")) ' adVarWChar
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    ' append the query string to the redirect URL
    Dim MM_editRedirectUrl
    MM_editRedirectUrl = "Add.asp"
    If (Request.QueryString <> "") Then
      If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
        MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      Else
        MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
      End If
    End If
    Response.Redirect(MM_editRedirectUrl)
  End If
End If
%>

 （修改和删除类似上面的添加）

 

第二种：这是用 connection 和 recordset 来实现的：

声明数据库连接：

<%
set conn=server.CreateObject("adodb.connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("db/lxdb.mdb")
%>

查询：

set rs=server.CreateObject("adodb.recordset")
rs.open "select * from ceshi",conn,1,1
response.Write(rs("UserName"))
rs.close
set rs=nothing

 

添加：

 添加有两种方法，一种是conn.execute，还有就是用 rs.open 的方法：

直接一句： conn.execute "insert into ceshi (UserName,pwd) values ('"&request.Form("textfield")&"','"&request.Form("textfield2")&"')

rs.open 的：

set rs=server.CreateObject("adodb.recordset")
rs.open "select * from ceshi",conn,1,3
rs.addnew
rs("UserName")=request.Form("textfield")
rs("pwd")=request.Form("textfield2")
rs.update
rs.close
set rs=nothing

 修改和删除也同上，一般建议添加、删除、修改，用 conn.execute 的方法，查询用 rs。

rs 的修改和删除与添加时有点不一样：

修改时没有 rs.addnew ，但要加where，所以：

set rs=server.CreateObject("adodb.recordset")
rs.open "select * from ceshi where id=2",conn,1,3
rs("UserName")=request.Form("textfield")
rs("pwd")=request.Form("textfield2")
rs.update
rs.close
set rs=nothing

然后是删除的：
set rs=server.CreateObject("adodb.recordset")
rs.open "select * from ceshi where id="&id,conn,1,3
rs.delete
rs.update
rs.close
set rs=nothing