asp连接sql数据库的增删改查完整代码

Question

如题所述

Answer 1

<%
dim conn,db
dim connstr
db="db.mdb" ' access数据库文件路径
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&server.MapPath(""&db&"")&";Persist Security Info=False;"
set conn=server.createobject("ADODB.CONNECTION")

'连接数据库
conn.open connstr 'open datebase

'数据库结构
'文件名db.mdb
'表名admin
'字段id，自动编号
'字段username，文本
'字段userpass，文本

' 增
    set Rs=server.createobject("adodb.recordset")
    Sql="select * from admin"
    Rs.open Sql,conn,1,3
    Rs.addnew
    Rs("username")="用户名"
    Rs("userpass")="用户密码"
    Rs.update
    Rs.close
    set Rs=nothing
    response.Write "<script language='javascript'>alert('管理员增加成功!');'</script>"

' 删
    s_id=1 '假设有个用户数据的id为1
    Sql="delete * from admin where id="&s_id&""
    set Rs=server.CreateObject("ADODB.recordset")
    rs.open Sql,conn,1,3
    response.Write("<script>alert('帐号删除成功！');</script>")
    rs.close
    set Rs=nothing

' 改
    s_id=1 '假设有个用户数据的id为1
    set Rs=server.createobject("adodb.recordset")
    Sql="select * from admin where id="&s_id&""
    Rs.open Sql,conn,1,3
    if Rs.eof and Rs.bof then
        Rs.close
        set Rs=nothing
        response.Write("<script language='javascript'>alert('此用户不存在!');history.back();</script>")
    else
        Rs("Password")="新密码"
        Rs.update
        Rs.close
        set Rs=nothing
        response.Write ("<script language='javascript'>alert('密码修改成功!');</script>")
    End If

' 查单个
    s_username="admin" '假设有个用户名为admin的用户数据
    set Rs=server.createobject("adodb.recordset")
    Sql="select * from admin where username='"&s_username&"'"
    Rs.open Sql,conn,1,3
    if Rs.eof and Rs.bof then
        Rs.close
        set Rs=nothing
        response.Write("<script language='javascript'>alert('此用户不存在!');history.back();</script>")
    else
        s_name=Rs("username")
        s_pass=Rs("userpass")
        Rs.close
        set Rs=nothing

        ' 输出查询到的信息
        response.Write ("<script language='javascript'>alert('你查询的用户名"&s_name&"的密码为："&s_pass&"');</script>")
    End If

' 查列表
    set Rs=server.CreateObject("Adodb.recordset")
    sql="select * from admin order by id desc"
    Rs.open sql,conn,1,1
    if not Rs.eof then

        ' 循环输出查询到的信息
        do while not Rs.eof 
            response.Write "id："&Rs("id")&",用户名："&Rs("username")&",密码："&Rs("userpass")&"<br />"
        Rs.movenext
        loop

    end if
    Rs.close
    set Rs=nothing


' 使用完数据库后要关闭连接资源
    conn.close
    set conn=nothing
%>

Answer 2

<%'增加数据-------------------------------------------------------
action = request.QueryString("action")
if action = "add" then
set rs = server.CreateObject("ADODB.RecordSet")
sql = "select * from class"
rs.open sql, conn, 3, 3
rs.AddNew
rs("class") = request.Form("classa")
rs("sort") = request.Form("sorta")
rs.Update
rs.close
set rs = nothing
conn.close
set conn = nothing
response.Redirect("music.asp")
end if'增加数据结束------------------------------------------------------- '修改数据-------------------------------------------------------if action = "xiugai" then
nID = request.QueryString("id")
set rs = server.CreateObject("ADODB.RecordSet")
sql = "select * from class where id = "&nID
rs.open sql, conn, 1, 3
rs("class") = request.Form("class")
rs("sort") = request.Form("sort")
rs.Update
rs.close
set rs = nothing
conn.close
set conn = nothing
response.Redirect("class.asp")
end if'修改数据结束------------------------------------------------------- '删除数据-------------------------------------------------------if action = "delete" then
nID = request.QueryString("id")
sql = "delete from class where id = "&nID
conn.Execute(sql)
conn.close
set conn = nothing
response.Redirect("class.asp")
end if'删除数据结束-------------------------------------------------------
%> 这里都是┞梦里飘┩的回答 我只是怕楼住看不明白，帮助划分了一下！