网页链接ACCESS数据库方法
发布网友
共1个回答
热心网友
给你一段代码，连接一个Access数据库，添加、查询、修改、删除，在一个页面都搞定。
<%
'作用：
Sub Aerlt_Redirect(msg,url)
Dim t
t = "<script>"
t = t & "alert('" & msg & "');"
t = t & "location.href='" & url & "';"
t = t & "</script>"
Response.write t
End Sub
'作用：
Dim sql,conn,rs,connstr,dbpath
Set conn=Server.CreateObject("adodb.connection")
Set rs=Server.CreateObject("adodb.recordset")
dbpath="./adoAll.mdb"
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.mapPath(dbpath)
conn.connectionstring=connstr
%>
<html>
<head>
<title>ado全功能测试</title>
<script type="text/javascript">
<!--
function checkForm()
{
var u_name=document.f.u_name.value;
var u_pass1=document.f.u_pass1.value;
var u_pass2=document.f.u_pass2.value;
var u_email=document.f.u_email.value;
var u_birth=document.f.u_birth.value;
if (u_name.length<3)
{
alert("用户名不得少于3个字母！");
document.f.u_name.focus();
return false;
}
//...
return true;
}
function myDo(f,act)
{
var frm=eval("document." + f);
frm.act.value=act;
frm.submit();
}
//-->
</script>
</head>
<body>
<%
Dim act
act=Trim(request.Form("act"))
Select Case act
Case "new"
u_name=Trim(Request.Form("u_name"))
u_pass1=Trim(Request.Form("u_pass1"))
u_pass2=Trim(Request.Form("u_pass2"))
u_email=Trim(Request.Form("u_email"))
u_birth=Trim(Request.Form("u_birth"))
u_sex=Trim(Request.Form("u_sex"))
'作用：
If Len(u_name)<3 Then
Response.write "用户名不得少于3个字母！"
Response.End
End If
'....
'作用：
'sql="insert into [member](u_name,u_pass,...) values('" & u_name & "','" & u_pass & "','..."
'conn.open
'conn.execute sql
sql="select top 1 * from [member]"
conn.open
rs.open sql,conn,1,3
rs.Addnew
rs("u_name")=u_name
rs("u_pass")=u_pass1
rs("u_email")=u_email
rs("u_birth")=u_birth
rs("u_sex")=u_sex
rs.update
rs.close
conn.close
Call Aerlt_Redirect("添加成功！","adoAll.asp")
Case "mdf"
u_id=Trim(Request.Form("u_id"))
u_name=Trim(Request.Form("u_name"))
u_pass=Trim(Request.Form("u_pass"))
u_email=Trim(Request.Form("u_email"))
u_birth=Trim(Request.Form("u_birth"))
u_sex=Trim(Request.Form("u_sex"))
'作用：
If Len(u_name)<3 Then
Response.write "用户名不得少于3个字母！"
Response.End
End If
If Not IsNumeric(u_id) Then
Response.write "u_id格式错误"
Response.End
End If
'....
'作用：
sql="select top 1 * from [member] where u_id=" & u_id
conn.open
rs.open sql,conn,1,3
'If Not rs.eof Then rs.delete
rs("u_name")=u_name
rs("u_pass")=u_pass
rs("u_email")=u_email
rs("u_birth")=u_birth
rs("u_sex")=u_sex
rs.update
rs.close
conn.close
Call Aerlt_Redirect("修改成功！","adoAll.asp")
'作用：
Case "del"
u_id=Trim(Request.Form("u_id"))
sql="delete * from [member] where u_id=?"
conn.open
Set c=Server.CreateObject("adodb.command")
with c
Set .ActiveConnection=conn
.CommandText=sql
.CommandType=1
.Parameters.Append .CreateParameter("u_id",3,1,4,u_id)
.Execute
Set .ActiveConnection=Nothing
End with
conn.close
Call Aerlt_Redirect("删除成功！","adoAll.asp")
'作用：
Case "search"
u_name=Trim(Request.Form("u_name"))
u_sex=Trim(Request.Form("u_sex"))
'...
If u_name<>"" Then
ext=" and [u_name]='" & u_name & "'"
End If
If u_sex<>"" Then
ext=ext & " and [u_sex]=" & u_sex
End If
sql="select * from [member] where 1=1 " & ext
sql=sql & " order by u_id "
session("sql")=sql
Case Else
If session("sql")="" Then
sql="select * from [member] order by u_id "
session("sql")=sql
End If
End Select
%>
<form name="f" method="post" action="?" onsubmit="return checkForm()">
<input type="hidden" name="act" value="new">
<table>
<tr>
<td>注册用户名：</td>
<td><input type="text" name="u_name" /></td>
</tr>
<tr>
<td>密码：</td>
<td><input type="password" name="u_pass1" /></td>
</tr>
<tr>
<td>确认密码：</td>
<td><input type="password" name="u_pass2" /></td>
</tr>
<tr>
<td>邮箱：</td>
<td><input type="text" name="u_email" /></td>
</tr>
<tr>
<td>性别：</td>
<td><input type="radio" name="u_sex" value="1" checked />男
<input type="radio" name="u_sex" value="0" />女</td>
</tr>
<tr>
<td>生日：</td>
<td><input type="text" name="u_birth" /></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="注册" /></td>
</tr>
</table>
</form>
<form method="post" action="?">
<input type="hidden" name="act" value="search">
<table>
<tr>
<td>用户名：<input type="text" name="u_name" size="10" /></td>
<td>性别：<select name="u_sex">
<option value="" selected="selected">选择性别</option>
<option value="0">女</option>
<option value="1">男</option>
</select></td>
<td><input type="submit" value="查询" /></td>
</tr>
</table>
</form>
<%
If session("sql")="" Then Response.End
conn.open
rs.open session("sql"),conn,1,1
If Not rs.eof Then
%>
<table>
<tr>
<td>ID</td>
<td>用户名</td>
<td>密码</td>
<td>邮箱</td>
<td>性别</td>
<td>出生日期</td>
<td>修改</td>
<td>删除</td>
</tr>
<%
Dim iCnt
Do While Not rs.Eof
iCnt=iCnt+1
%>
<form method="post" action="?" name="f<%=iCnt%>">
<input type="hidden" name="act" value="">
<input type="hidden" name="u_id" value="<%=rs("u_id")%>">
<tr>
<td><%=rs("u_id")%></td>
<td><input type="text" name="u_name" value="<%=rs("u_name")%>" /></td>
<td><input type="text" name="u_pass" value="<%=rs("u_pass")%>" /></td>
<td><input type="text" name="u_email" value="<%=rs("u_email")%>" /></td>
<td><select name="u_sex">
<option value="0" <%If rs("u_sex")="0" Then Response.write " selected"%>>女</option>
<option value="1" <%If rs("u_sex")="1" Then Response.write " selected"%>>男</option>
</td>
<td><input type="text" name="u_birth" value="<%=rs("u_birth")%>" /></td>
<td><input type="button" value="修改" onclick="myDo('f<%=iCnt%>','mdf')" /></td>
<td><input type="button" value="删除" onclick="myDo('f<%=iCnt%>','del')" /></td>
</tr>
</form>
<%
rs.MoveNext
Loop
%>
</table>
<%
End If
rs.close : conn.close
%>
</body>
</html>
