简介
类似sql server 2000 的sql 查询分析器界面，可以对网站上的access数据库直接执行sql查询语句
特点
全部功能用一个asp文件实现，使用方便
使用方法
用文本编辑器等打开sqltools.asp,
Const USER_NAME="admin" ' 将 admin 修改为你的用户名
Const PASS_WORD="welcome" ' 将 welcome 修改为你的密码
Const DB = "data/#%2051e0ed0a938e9b4cc781.mdb" ' 这里设置access数据库所在的相对路径
源代码：
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<% Option Explicit %>
<%Response.CodePage=65001%>
<% Response.Charset="UTF-8" %>
<% Response.Buffer=true
Server.ScriptTimeOut=1
'------设置常量---------------------
Const USER_NAME="admin"
Const PASS_WORD="welcome"
Const DB = "data/#%2051e0ed0a938e9b4cc781.mdb"
'----------------------------------
Const adSchemaTables = 20
dim sqlstr,connstr,conn,adSchemaColumns
dim i
adSchemaColumns = 4
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DB)
select case request.Form("act")
case "登录"
response.Cookies("username") = request.Form("username")
response.Cookies("password") = request.Form("password")
end select
if request.Form("sqlstr") <> "" then
sqlstr = request.Form("sqlstr")
sqlstr = trim(sqlstr)
end if
%>?<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="zh-CN" lang="zh-CN">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Language" content="zh-CN" />
<title>SQL 查询分析器</title>
<style type="text/css">
body{ font-size:12px; font-family:Arial, Helvetica, sans-serif;}
h2{font-size:14px;}
#userlogin label{width:400px; display:block; text-align:right;}
#header{ background-color:#eeeeff;}
#left{ width:200px; height:450px; overflow:scroll; float:left;}
#foot{ clear:both; background-color:#CCFFCC}
#sqlstr{ overflow-y:visible;}
#showdetail{ width:700px; height:380px; overflow:scroll; border:solid #999999 1px;}
#showdetail table{border:#999999 solid 1px;}
#showdetail table tr td{border:#999999 solid 1px;}
ul{list-style:none; margin-left:10px;}
ul li{font-weight:bold;}
ul li ul li{font-weight:normal;}
.key{ font-weight:bold; color:#FF2222;}
</style>
<script language="javascript" type="text/javascript">
</script>
</head>
<body>
<%
if (request.Cookies("username") <> USER_NAME) or (request.Cookies("password") <> PASS_WORD) then
%>
<div id="userlogin">
<h1>用户登录</h1>
<form name="form1" method="post" action="sqltools.asp">
<label>用户名
<input name="username" type="text" id="username">
</label>
<label>密码
<input name="password" type="password" id="password">
</label>
<label>
<input name="act" type="submit" id="act" value="登录">
</label>
</form>
<p>用户名密码，请修改文件中 常量Const USER_NAME 和 Const PASS_WORD</p>
</div>
<%
response.Write("</body></html>")
response.End()
end if
%>
<%call openconn%>
<div id="header">
<h1>Asp Access 查询分析器</h1>
<p>数据库路径:<%=connstr%></p>
</div>
<div id="left">
<%
Dim rstSchema,rscSchema,rskSchema,tablename
Set rstSchema = Conn.OpenSchema(adSchemaTables)
%>
<h2>表</h2>
<ul>
<%
Do while not rstSchema.EOF
if rstSchema("TABLE_TYPE") = "TABLE" then
tablename = trim(rstSchema("TABLE_NAME"))
response.write("<li>"& tablename)
set rscSchema = conn.OpenSchema(4,Array(Empty, Empty, tablename,Empty))
set rskSchema = conn.OpenSchema(28,Array(Empty,Empty,tablename))
if not rscSchema.eof or rscSchema.bof then
response.Write("<ul>")
while not rscSchema.eof
if not rskSchema.eof then
if (rskSchema("COLUMN_NAME") = rscSchema("COLUMN_NAME")) then
response.Write("<li class=""key"">")
else
response.Write("<li>")
end if
else
response.Write("<li>")
end if
response.Write(rscSchema("COLUMN_NAME"))
response.Write(" [" & tablewordtype(rscSchema("DATA_TYPE")) & "] ")
response.Write("</li>")
rscSchema.movenext
wend
response.Write("</ul>")
rscSchema.close
end if
response.Write("</li>")
end if
I = I + 1
rstSchema.MoveNext
Loop
%>
</ul>
<h2>查询</h2>
<ul>
<%
rstSchema.movefirst
Do while not rstSchema.EOF
if rstSchema("TABLE_TYPE") = "VIEW" then
response.write("<li>"& rstSchema("TABLE_NAME") & "</li>")
end if
I = I + 1
rstSchema.MoveNext
Loop
%>
</ul>
</div>
<div>
<form action="sqltools.asp" method="post" name="formsqlstr" id="formsqlstr">
<div><textarea name="sqlstr" cols="80" rows="1" id="sqlstr"><%=sqlstr%></textarea>
</div>
<div>
<input type="submit" name="Submit" value="提交">
</div>
</form>
</div>
<div id="showdetail">
<%
select case trim(lcase(left(sqlstr,6)))
case "select"
dim rs
On Error Resume Next
set rs=conn.execute(sqlstr)
If Err Then
Response.Write "发生错误 : <br /><br />"& Err.Source &" ("&Err.Number&")"
response.Write("<br /> " & errorname(Err.Number))
response.Write("</div></body></html>")
response.End()
end if
response.Write("<table><tr>")
for i = 0 to rs.Fields.Count-1
response.Write("<td>" & rs.Fields(i).Name & "</td>")
next
response.Write("</tr>")
if not rs.eof then
while not rs.eof
response.Write("<tr>")
for i = 0 to rs.Fields.Count-1
response.Write("<td>" & rs(i) & "</td>")
next
response.Write("</tr>")
rs.movenext
wend
end if
response.Write("</table>")
rs.close
set rs=nothing
conn.close
set conn=nothing
case "update"
conn.execute(sqlstr)
case "delete"
conn.execute(sqlstr)
case "create"
conn.execute(sqlstr)
case "drop"
conn.execute(sqlstr)
case else
response.Write("无法解释的SQL语句。")
end select
%>
</div>
<div id="foot"> <a href="http://www.farsuncomputer.com/" target="_blank"> 上海遥日电脑有限公司 </a> </div>
</body>
</html><%
function openconn()
set conn=Server.CreateObject("ADODB.Connection")
On Error Resume Next
Conn.open ConnStr
If Err Then
Response.Write ""&IsSqlVer&"Database error.<br><br>"&Err.Source&" ("&Err.Number&")"
response.Write("<br />请修改代码中数据库地址 ""Const DB =""")
Set Conn = Nothing
err.Clear
Response.End()
End If
end function
Function tablewordtype(thetype)
select case thetype
case "202"
tablewordtype = "Text"
case "2"
tablewordtype = "Integer"
case "3"
tablewordtype = "long"
case "4"
tablewordtype = "Single"
case "5"
tablewordtype = "Double"
case "6"
tablewordtype = "Currency"
case "7"
tablewordtype = "Byte"
case "10"
tablewordtype = "auto"
case "201"
tablewordtype = "Memo"
case "203"
tablewordtype = "Memo"
case "204"
tablewordtype = "bit"
case "11"
tablewordtype = "YesNo"
case "130"
tablewordtype = "NChar"
case "131"
tablewordtype = "Decimal"
case "135"
tablewordtype = "DateTime"
case "72"
tablewordtype = "ReplicationID"
case else
tablewordtype = thetype
end select
end Function
function errorname(errnumber)
select case errnumber
case -2147217865
errorname = " 找不到数据表或查.询 "
case -2147217900
errorname = " 内存溢出,请检查查询字段名是否正确. "
case else
errorname = " 未知错误,请到网上查询该错误代码 : " & errnumber
end select
end function
%>