■ 站内搜索
■ 推荐
■ 热点
■ 相关
- 无相关代码
在线批量修改Access数据库
作者：不详 来源：网络 时间：2009-09-10 点击：944
■ 简介说明：
总需要批量修改数据库中的一些内容，所以这个代码很有用。
<%这里加入你网站管理员的验证信息%>
<HTML><HEAD><TITLE>数据库批量修改</TITLE>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<style>
* {
font-family: "宋体";
}
Body {
font-size: 9pt;
line-height: 12pt;
scrollbar-face-color: #DEE3E7;
scrollbar-highlight-color: #FFFFFF;
scrollbar-shadow-color: #DEE3E7;
scrollbar-3dlight-color: #D1D7DC;
scrollbar-arrow-color: #006699;
scrollbar-track-color: #EFEFEF;
scrollbar-darkshadow-color: #98AAB1;
}
Table,Td,form {
font-size: 9pt;
line-height: 12pt;
}
input,select,option,textarea {
font-size: 9pt;
/**border: 1px solid #CCCCCC;**/
color: #000033;
}
.b1 {
border: 1px solid #CCCCCC;
}
.tdbg1 {
background-color: #D7D7D7;
}
.tdbg2 {
background-color: #F6F6F6;
}
.tdbgleft {
background-color: #F9F9F9;
}
.max1{
height:300px;
width:500px;
}
.max{
Z-INDEX: 1; LEFT: 0px; POSITION: absolute; TOP: 0px;height:expression(body.offsetHeight);width:expression(body.offsetWidth)
}
a:link {
color: #000066;
text-decoration: none;
}
a:visited {
text-decoration: none;
color: #000066;
}
a:hover {
color: #FF0000;
text-decoration: underline;
}
a:active {
text-decoration: underline;
}
</style>
<Script Language=Javascript>
function check(){
var f=document.myform
if(f.oSoftLink.value.length==0){alert("要查找的字符不能为空");f.oSoftLink.focus();return false}
}
</script>
<Script Language=Javascript>
function check1(){
var f=document.myform1
if(f.mdbname.value.length==0){alert("数据库地址不能为空");f.mdbname.focus();return false}
}
</script>
</HEAD>
<BODY leftMargin=0 topMargin=0>
<table width="100%" border="0" cellspacing="2">
<tr>
<td class="b1" valign="top">
<% sub Sysmsg(msgtitle,msginfo) %>
<table width="100%" border="0" cellpadding="5">
<tr>
<td class="tdbg1"><strong><%= msgtitle %></strong></td>
</tr>
<tr>
<td class="tdbg2"><%= msginfo %></td>
</tr>
<tr>
<td class="tdbg2"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
</table>
<% end sub %>
<%
function checkStr(str)
if isnull(str) then
checkStr = ""
exit function
end if
checkStr=replace(str,"'","''")
end function
select case Trim(Request.Form("action"))
case "replace"
call replacelink()
case "search"
call searchlink()
case "biao"
call biao()
case "ziduan"
call ziduan()
case else
call main()
end select
sub replacelink()
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
oSoftLink=checkStr(Trim(Session("oSoftLink")))
nSoftLink=checkStr(Trim(Request.Form("nSoftLink")))
biaoname=checkStr(Trim(Session("biaoname")))
ziduanname=checkStr(Trim(Session("ziduanname")))
set rs=server.createobject("adodb.recordset")
set uprs=server.createobject("adodb.recordset")
haveid=Session("haveid")
if haveid=1 then
'response.write "快"
sql="select id," & Session("ziduanname") & " from " & Session("biaoname") & " where " & Session("ziduanname") & " like '%"&Trim(Session("oSoftLink"))"%'"
rs.open sql,conn,1,1
if not(rs.eof and rs.bof) then
i=rs.recordcount
do while not rs.eof
newAddress=replace(rs(1),""&oSoftLink"",""&nSoftLink"")
sql="select * from " & biaoname & " where ID="&rs(0)
uprs.open sql,conn,1,3
uprs(""&ziduanname"")=newAddress
uprs.update
uprs.close
rs.MoveNext
Loop
end if
else
'response.write "不快"
sql="select " & Session("ziduanname") & " from " & Session("biaoname") & " where " & Session("ziduanname") & " like '%"&Trim(Session("oSoftLink"))"%'"
rs.open sql,conn,1,1
if not(rs.eof and rs.bof) then
i=rs.recordcount
do while not rs.eof
newAddress=replace(rs(0),""&oSoftLink"",""&nSoftLink"")
sql="select * from " & biaoname & " where " & ziduanname & "='"&rs(0)"'"
uprs.open sql,conn,1,3
uprs(""&ziduanname"")=newAddress
uprs.update
uprs.close
rs.MoveNext
Loop
end if
end if
rs.close
set uprs=nothing
set rs=nothing
msgtitle="批量管理"
msginfo="<li>操作成功，共更新了 "&i" 条信息！</li>"
call Sysmsg(msgtitle,msginfo)
Conn.close
Set conn = Nothing
end sub
sub searchlink()
%>
<form name="myform" action="" method="post">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">替换
<input name="action" type="hidden" value="replace"></th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">
<%
Session("ziduanname")=checkStr(Trim(Request.Form("ziduanname")))
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
set rs=server.createobject("adodb.recordset")
sql="select count(" & Session("ziduanname") & ") from " & Session("biaoname") & " where " & Session("ziduanname") & " like '%"&checkStr(Trim(Request.Form("oSoftLink")))"%'"
rs.open sql,conn,1,1
if not(rs.eof and rs.bof) then
Session("oSoftLink")=checkStr(Trim(Request.Form("oSoftLink")))
Response.Write("本次搜索找到了 <b>"&rs(0)"</b> 个相关条目。")
else
Response.Write("没有找到相关信息")
end if
rs.close
set rs=nothing
Conn.close
Set conn = Nothing
%>
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">将字符
<input disabled name="oSoftLink" type="text" value="<%= Trim(Request.Form("oSoftLink")) %>" size="45" maxlength="50">
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">替换成
<input name="nSoftLink" type="text" value="" size="45" maxlength="50">
</tr>
<tr>
<td height="25" align="center" class="tdbg2">
<input type="submit" name="Submit2" value="马上替换 "></td>
</tr>
<tr>
<td class="tdbg2" align="center"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
</table>
</form>
<%
end sub
sub biao()
Session("mdbname")=checkStr(Trim(Request.Form("mdbname")))
Set fso = CreateObject("Scripting.FileSystemObject")
filename=server.mappath(""&Session("mdbname")"")
if Not fso.FileExists(filename) then
msgtitle="批量管理"
msginfo="<li>你输入的数据库 "&filename" 不存在！</li><br><li>请输入<font color=#FF0000>正确</font>的数据库地址！</li>"
call Sysmsg(msgtitle,msginfo)
response.end
end if
Set fso = nothing
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
%>
<form name="myform" action="" method="post">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">选择表名</th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">数据库地址（相对地址）：
<input disabled name="mdbname" type="text" size="45" maxlength="50" value="<%= Trim(Request.Form("mdbname")) %>">
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">要操作的表名：
<input name="action" type="hidden" value="ziduan">
<select name="biaoname">
<%Set rs=Conn.OpenSchema(20)
While not rs.EOF
Response.Write("<option value='" & rs(2) & "'>" & rs(2) & "</option>")
rs.MoveNext
Wend%>
</select>
<input type="submit" name="Submit" value="下一步">
</td>
</tr>
<tr>
<td class="tdbg2" align="center"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
</table>
</form>
<% end sub
sub ziduan()
'Session("mdbname")=checkStr(Trim(Request.Form("mdbname")))
Session("biaoname")=checkStr(Trim(Request.Form("biaoname")))
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
%>
<form name="myform" action="" method="post" onsubmit="return check()">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">选择字段名输入查找内容</th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">数据库地址（相对地址）：
<input disabled name="mdbname" type="text" size="45" maxlength="50" value="<%= Session("mdbname") %>">
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">要操作的表名：
<select name="biaoname">
<option value="<%= Session("biaoname") %>" selected><%=Session("biaoname")%></option>
</select>
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">要替换的字段名：
<select name="ziduanname">
<%
haveid=0
set rs=server.createobject("adodb.recordset")
sql="select * from [" & Session("biaoname") & "] where 1<>1"
rs.open sql,Conn,1,1
j=rs.Fields.count
For i=0 to (j-1)
'Response.Write("第" & i+1 & "个字段名：" & rs.Fields(i).Name & "<br>")
Response.Write("<option value='" & rs.Fields(i).Name & "'>" & rs.Fields(i).Name & "</option>")
if LCase(rs.Fields(i).Name)="id" then
Session("haveid")=1
end if
Next
rs.close%>
</select>
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">内容中包含的字符：
<input name="action" type="hidden" value="search">
<input name="oSoftLink" type="text" size="45" maxlength="50">
<input type="submit" name="Submit" value="查找">
</td>
</tr>
<tr>
<td class="tdbg2" align="center"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">注意：单引号“'”将被自动过滤掉</td>
</tr>
</table>
</form>
<% end sub
sub main()
%>
<form name="myform1" action="" method="post" onsubmit="return check1()">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">输入数据库名</th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">请输入数据库地址（相对地址）：
<input name="action" type="hidden" value="biao">
<input name="mdbname" type="text" size="45" maxlength="50">
<input type="submit" name="Submit" value="下一步"> </td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">注意：单引号“'”将被自动过滤掉</td>
</tr>
</table>
</form>
<center><script>var tc_user="addision";var tc_class="2";</script>
</center>
<% end sub %>
</td>
</tr>
</table>
</BODY>
</HTML>
<HTML><HEAD><TITLE>数据库批量修改</TITLE>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<style>
* {
font-family: "宋体";
}
Body {
font-size: 9pt;
line-height: 12pt;
scrollbar-face-color: #DEE3E7;
scrollbar-highlight-color: #FFFFFF;
scrollbar-shadow-color: #DEE3E7;
scrollbar-3dlight-color: #D1D7DC;
scrollbar-arrow-color: #006699;
scrollbar-track-color: #EFEFEF;
scrollbar-darkshadow-color: #98AAB1;
}
Table,Td,form {
font-size: 9pt;
line-height: 12pt;
}
input,select,option,textarea {
font-size: 9pt;
/**border: 1px solid #CCCCCC;**/
color: #000033;
}
.b1 {
border: 1px solid #CCCCCC;
}
.tdbg1 {
background-color: #D7D7D7;
}
.tdbg2 {
background-color: #F6F6F6;
}
.tdbgleft {
background-color: #F9F9F9;
}
.max1{
height:300px;
width:500px;
}
.max{
Z-INDEX: 1; LEFT: 0px; POSITION: absolute; TOP: 0px;height:expression(body.offsetHeight);width:expression(body.offsetWidth)
}
a:link {
color: #000066;
text-decoration: none;
}
a:visited {
text-decoration: none;
color: #000066;
}
a:hover {
color: #FF0000;
text-decoration: underline;
}
a:active {
text-decoration: underline;
}
</style>
<Script Language=Javascript>
function check(){
var f=document.myform
if(f.oSoftLink.value.length==0){alert("要查找的字符不能为空");f.oSoftLink.focus();return false}
}
</script>
<Script Language=Javascript>
function check1(){
var f=document.myform1
if(f.mdbname.value.length==0){alert("数据库地址不能为空");f.mdbname.focus();return false}
}
</script>
</HEAD>
<BODY leftMargin=0 topMargin=0>
<table width="100%" border="0" cellspacing="2">
<tr>
<td class="b1" valign="top">
<% sub Sysmsg(msgtitle,msginfo) %>
<table width="100%" border="0" cellpadding="5">
<tr>
<td class="tdbg1"><strong><%= msgtitle %></strong></td>
</tr>
<tr>
<td class="tdbg2"><%= msginfo %></td>
</tr>
<tr>
<td class="tdbg2"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
</table>
<% end sub %>
<%
function checkStr(str)
if isnull(str) then
checkStr = ""
exit function
end if
checkStr=replace(str,"'","''")
end function
select case Trim(Request.Form("action"))
case "replace"
call replacelink()
case "search"
call searchlink()
case "biao"
call biao()
case "ziduan"
call ziduan()
case else
call main()
end select
sub replacelink()
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
oSoftLink=checkStr(Trim(Session("oSoftLink")))
nSoftLink=checkStr(Trim(Request.Form("nSoftLink")))
biaoname=checkStr(Trim(Session("biaoname")))
ziduanname=checkStr(Trim(Session("ziduanname")))
set rs=server.createobject("adodb.recordset")
set uprs=server.createobject("adodb.recordset")
haveid=Session("haveid")
if haveid=1 then
'response.write "快"
sql="select id," & Session("ziduanname") & " from " & Session("biaoname") & " where " & Session("ziduanname") & " like '%"&Trim(Session("oSoftLink"))"%'"
rs.open sql,conn,1,1
if not(rs.eof and rs.bof) then
i=rs.recordcount
do while not rs.eof
newAddress=replace(rs(1),""&oSoftLink"",""&nSoftLink"")
sql="select * from " & biaoname & " where ID="&rs(0)
uprs.open sql,conn,1,3
uprs(""&ziduanname"")=newAddress
uprs.update
uprs.close
rs.MoveNext
Loop
end if
else
'response.write "不快"
sql="select " & Session("ziduanname") & " from " & Session("biaoname") & " where " & Session("ziduanname") & " like '%"&Trim(Session("oSoftLink"))"%'"
rs.open sql,conn,1,1
if not(rs.eof and rs.bof) then
i=rs.recordcount
do while not rs.eof
newAddress=replace(rs(0),""&oSoftLink"",""&nSoftLink"")
sql="select * from " & biaoname & " where " & ziduanname & "='"&rs(0)"'"
uprs.open sql,conn,1,3
uprs(""&ziduanname"")=newAddress
uprs.update
uprs.close
rs.MoveNext
Loop
end if
end if
rs.close
set uprs=nothing
set rs=nothing
msgtitle="批量管理"
msginfo="<li>操作成功，共更新了 "&i" 条信息！</li>"
call Sysmsg(msgtitle,msginfo)
Conn.close
Set conn = Nothing
end sub
sub searchlink()
%>
<form name="myform" action="" method="post">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">替换
<input name="action" type="hidden" value="replace"></th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">
<%
Session("ziduanname")=checkStr(Trim(Request.Form("ziduanname")))
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
set rs=server.createobject("adodb.recordset")
sql="select count(" & Session("ziduanname") & ") from " & Session("biaoname") & " where " & Session("ziduanname") & " like '%"&checkStr(Trim(Request.Form("oSoftLink")))"%'"
rs.open sql,conn,1,1
if not(rs.eof and rs.bof) then
Session("oSoftLink")=checkStr(Trim(Request.Form("oSoftLink")))
Response.Write("本次搜索找到了 <b>"&rs(0)"</b> 个相关条目。")
else
Response.Write("没有找到相关信息")
end if
rs.close
set rs=nothing
Conn.close
Set conn = Nothing
%>
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">将字符
<input disabled name="oSoftLink" type="text" value="<%= Trim(Request.Form("oSoftLink")) %>" size="45" maxlength="50">
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">替换成
<input name="nSoftLink" type="text" value="" size="45" maxlength="50">
</tr>
<tr>
<td height="25" align="center" class="tdbg2">
<input type="submit" name="Submit2" value="马上替换 "></td>
</tr>
<tr>
<td class="tdbg2" align="center"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
</table>
</form>
<%
end sub
sub biao()
Session("mdbname")=checkStr(Trim(Request.Form("mdbname")))
Set fso = CreateObject("Scripting.FileSystemObject")
filename=server.mappath(""&Session("mdbname")"")
if Not fso.FileExists(filename) then
msgtitle="批量管理"
msginfo="<li>你输入的数据库 "&filename" 不存在！</li><br><li>请输入<font color=#FF0000>正确</font>的数据库地址！</li>"
call Sysmsg(msgtitle,msginfo)
response.end
end if
Set fso = nothing
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
%>
<form name="myform" action="" method="post">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">选择表名</th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">数据库地址（相对地址）：
<input disabled name="mdbname" type="text" size="45" maxlength="50" value="<%= Trim(Request.Form("mdbname")) %>">
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">要操作的表名：
<input name="action" type="hidden" value="ziduan">
<select name="biaoname">
<%Set rs=Conn.OpenSchema(20)
While not rs.EOF
Response.Write("<option value='" & rs(2) & "'>" & rs(2) & "</option>")
rs.MoveNext
Wend%>
</select>
<input type="submit" name="Submit" value="下一步">
</td>
</tr>
<tr>
<td class="tdbg2" align="center"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
</table>
</form>
<% end sub
sub ziduan()
'Session("mdbname")=checkStr(Trim(Request.Form("mdbname")))
Session("biaoname")=checkStr(Trim(Request.Form("biaoname")))
set conn=server.createobject("ADODB.CONNECTION")
connstr="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath(""&Session("mdbname")"")
conn.open connstr
%>
<form name="myform" action="" method="post" onsubmit="return check()">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">选择字段名输入查找内容</th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">数据库地址（相对地址）：
<input disabled name="mdbname" type="text" size="45" maxlength="50" value="<%= Session("mdbname") %>">
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">要操作的表名：
<select name="biaoname">
<option value="<%= Session("biaoname") %>" selected><%=Session("biaoname")%></option>
</select>
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">要替换的字段名：
<select name="ziduanname">
<%
haveid=0
set rs=server.createobject("adodb.recordset")
sql="select * from [" & Session("biaoname") & "] where 1<>1"
rs.open sql,Conn,1,1
j=rs.Fields.count
For i=0 to (j-1)
'Response.Write("第" & i+1 & "个字段名：" & rs.Fields(i).Name & "<br>")
Response.Write("<option value='" & rs.Fields(i).Name & "'>" & rs.Fields(i).Name & "</option>")
if LCase(rs.Fields(i).Name)="id" then
Session("haveid")=1
end if
Next
rs.close%>
</select>
</td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">内容中包含的字符：
<input name="action" type="hidden" value="search">
<input name="oSoftLink" type="text" size="45" maxlength="50">
<input type="submit" name="Submit" value="查找">
</td>
</tr>
<tr>
<td class="tdbg2" align="center"><a href="javascript:history.go(-1)" ><< 返回上一页</a></td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">注意：单引号“'”将被自动过滤掉</td>
</tr>
</table>
</form>
<% end sub
sub main()
%>
<form name="myform1" action="" method="post" onsubmit="return check1()">
<table cellpadding="3" cellspacing="1" border="0" width="100%" class="tableBorder" align=center>
<tr>
<th height="22" class="tdbg1">输入数据库名</th>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">请输入数据库地址（相对地址）：
<input name="action" type="hidden" value="biao">
<input name="mdbname" type="text" size="45" maxlength="50">
<input type="submit" name="Submit" value="下一步"> </td>
</tr>
<tr>
<td height="25" align="center" class="tdbg2">注意：单引号“'”将被自动过滤掉</td>
</tr>
</table>
</form>
<center><script>var tc_user="addision";var tc_class="2";</script>
</center>
<% end sub %>
</td>
</tr>
</table>
</BODY>
</HTML>
■ 发表评论
■ 最新评论【显示所有评论】
