📄 ado_recordset.asp
字号:
<!--#include file="CONST.ASP"-->
<!--#include file="inc/chkuser.asp"-->
<!--#include file="inc/coo_footer.asp"-->
<%'@ LANGUAGE="VBSCRIPT"%>
<%'option explicit
'dim startime
'startime=timer()
'dim membername
'membername=request.cookies("CooSel")("username")
helpID=9%>
<html>
<head>
<title>[数据库查询]</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<!--\\forGlobal.asa include\\ METADATA TYPE="typelib" FILE="C:\Program Files\Common Files\SYSTEM\ADO\msado15.dll" NAME="ADODB Type Library" -->
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4" NAME="ADODB Type Library" -->
<!--#include file="inc/coosel.css" -->
<style type="text/css">
body { font-size: 9pt}
table { font-size: 9pt}
td { font-size: 9pt}
.tx { font-size: 9pt;border: 1px groove ;padding-top: 2px;}
'A:LINK {Text-Decoration: none; color:#3366ee;}
'A:VISITED {Text-Decoration: none; color:#888888;}
'A:Active {Text-Decoration: none; color:#ffffff;}
'A:HOVER {Text-Decoration: underline overline; color:#00aa00;}
</style>
<%
' if not isMaster or SetPower(2,7)=false then
if SetPower(2,7)=false then
Response.write "</head><body bgcolor='#e8e8e8' text='#000000' leftmargin='0' rightmargin='0' topmargin='0' bottommargin='20'>"
Server.Execute("TopNav1.asp")
call GetError ("<br><li>没有进入数据库管理面板权限，或你尚未登陆！")
end if
call CloseDataBase()
'if not isMaster then Response.redirect "login.asp?action=exit"
dim BASEtype,ErrOrINFO,SUEssOrINFO
dim myTABLE,BASE_NAME,BaseOwner,PasswordB
BASEtype=trim(request.form("BASEtype"))
BASE_NAME=trim(request.form("BASE_NAME"))
DATA_Source=trim(request.form("DATA_Source"))
BaseOwner=trim(request.form("BaseOwner"))
if BASEtype="" then BASEtype=trim(request.querystring("BASEtype"))
if BASE_NAME="" then BASE_NAME=trim(request.querystring("BASE_NAME"))
if DATA_Source="" then DATA_Source=trim(request.querystring("DATA_Source"))
if BaseOwner="" then BaseOwner=trim(request.querystring("BaseOwner"))
PasswordB=trim(request.form("PasswordB"))
myTABLE=cstr(request.querystring("table"))
dim ASCII
Response.buffer=true
Response.isClientconnected
REM ===========RecordeSet分页参数=============
dim colCount,RowCount,MaxPageSize
dim CurrentPage,Page_Count,PageCountS
dim k,jk,ek,TTT
jk=0
MaxPageSize=20
CurrentPage=request.QueryString("page")
if not isNumeric(CurrentPage) or CurrentPage="" then
CurrentPage=1
else
CurrentPage=clng(CurrentPage)
if err then
CurrentPage=1
err.clear
end if
end if %>
</head>
<body leftmargin="0" rightmargin="0" topmargin="0" bottommargin="20">
<!--#include file="TopNav.asp" -->
<table width="100%" border="1" align="center" cellpadding="1" cellspacing="0" bordercolorlight=#999999 bordercolordark=#eeeeee background="pic/back.gif">
<tr align="center">
<td height=6 align="right" colspan='8'><img src="" width=0 height=0></td>
</tr>
<form name="SQLform" method="post" action="">
<tr align="center" height="28">
<td width="9%" align="right" nowrap ><font color="#75A6F2"> \\数据库类型：</font></td>
<td width="15%" align="left" nowrap ><font color="#75A6F2">
<select name="BASEtype" style="width:120" onchange="if(this.options[this.selectedIndex].value==''){SQLinput();}" >
<option >选择数据库类型</option>
<option value='SQL2000' <% if BASEtype="SQL2000" then %>selected<% End If %>>SQLserver2000</option>
<option value='ACCESS' <% if BASEtype="ACCESS" then %>selected<% End If %>>MS ACCESS</option>
<option value='Oracle' <% if BASEtype="Oracle" then %>selected<% End If %>>Oracle DATABASE</option>
<!-- ?BASEtype=SQL2000&BASE_NAME=< %'=BASE_NAME%>&table=< %'=myTABLE% >&page=1 -->
</select>
</font></td>
<td width="6%" align="right" nowrap ><font color="#75A6F2"> \\库名：</font></td>
<td width="17%" align="left" nowrap ><font color="#75A6F2"> <input name="BASE_NAME" type="text" value="<%=BASE_NAME%>" class='tx'> </font></td>
<td width="6%" align="right" nowrap ><font color="#75A6F2"> \\ＩＰ：</font></td>
<td width="16%" align="left" nowrap ><font color="#75A6F2"> <input name="DATA_Source" type="text" value="<%=DATA_Source%>" class='tx'> </font></td>
<td colspan=2 align="left"><span id=SQL_Inner><font color="#75A6F2"> <input type="submit" name="SubmitBASE" value="提 交"></font></span></td>
</tr>
<tr align="center">
<td align="right"><font color="#75A6F2"> \\查询方式：</font></td>
<td align="left"> <font color="#75A6F2">
<select name="select" style="width:120" onChange="{SQLform.SQL.value=this.value}">
<option selected>请选择查询命令</option>
<option value="SELECT TOP 20 * FROM [<%=myTABLE%>]">Select（选择）</option>
<option value="UPDATE [<%=myTABLE%>] SET ">Update（更新）</option>
<option value="INSERT INTO [<%=myTABLE%>] () VALUES ('')">Insert（插入）</option>
<option value="DELETE FROM [<%=myTABLE%>] WHERE 0>1">Delete（删除）</option>
</select>
</font></td>
<td align="right"><font color="#75A6F2"> \\帐号：</font></td>
<span id=SQL_Inner>
<td align="left"><font color="#75A6F2"> <input name="BaseOwner" type="text" value="<%=BaseOwner%>" class='tx'> </font></td>
<td align="right"><font color="#75A6F2"> \\密码：</font> </td>
<td align="left" ><font color="#75A6F2"> <input name="PasswordB" type="password" value="werasdfa234" class='tx'> </font></td>
<td colspan=2 align="left"><font color="#75A6F2"> </font></td>
</span>
</tr>
<tr align="center">
<td align="right"><font color="#75A6F2"> \\ＳＱＬ语句：</font></td>
<td colspan="6" align="left"> <font color="#75A6F2"> <% if request("sql")<>"" then %>
<input id='SQL' name='SQL' size="55" accept="image/gif" style='width:97%' value="<%=request("sql")%>" class='tx'>
<% Else %>
<input id='SQL' name='SQL' size="55" accept="image/gif" style='width:97%' value="<%="SELECT TOP 500 * from "&chr(91)&myTABLE&chr(93)&"" %>" class='tx'>
<% End If %></font></td>
<td width="16%" align="left" ><font color="#75A6F2"> <input type="submit" name="SubmitSQL" value="提交查询"></font></td>
</tr>
</form>
<tr align="center">
<td height=6 align="right" colspan='8'><img src="" width=0 height=0></td>
</tr>
</table>
<table width="100%" border="1" align="center" cellpadding="1" cellspacing="0" bgcolor="#D4D0C8" bordercolordark=#eeeeee bordercolorlight=#999999>
<tr>
<td width="8" bgcolor="#f3f3f3"><img src="" width=0 ></td>
<td>
<%
' Response.PICS
on error resume next
IF BASE_NAME="" then ' ==================== BASE_NAME
ErrOrINFO="<br><li>请选择数据库类型和输入数据库名。</li>"
call errorX()
Response.end
ELSE '-------------------------------------BASE_NAME
' dim CONN
dim CONNErr
' dim connstr
dim db
' dim pass_word
' dim User_ID
' dim cntimeout
' dim rs,sql
db=BASE_NAME
if BASEtype="ACCESS" then
REM===========ACCESS 连接========
'db="data/TSbbs.asa" '数据库名'
Set CONN = Server.CreateObject("ADODB.Connection")
CONNstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(""&db&"")
CONN.Open CONNstr
CONNErr="<br><br><li>请仔细核对你输入的ＡＣＣＥＳＳ数据库名路径是否准确"
elseif BASEtype="SQL2000" then
REM===========SQLserver 连接========
if Session("PasswordB")="" then
if PasswordB<>"" then
Pass_word=PasswordB 'PasswordB=密码
end if
else
if PasswordB="" then
Pass_word=Session("PasswordB") 'PasswordB=密码
else
Pass_word=PasswordB
end if
end if
User_ID=BaseOwner 'User ID=帐号
cntimeout=15
Set CONN = Server.CreateObject("ADODB.CONNection")
CONNStr="Provider=SQLOLEDB.1;Password='"&Pass_word&"';Persist Security Info=true;User ID='"&User_ID&"';Initial Catalog='"&db&"';Data Source='"&Data_Source&"';CONNect Timeout="&cntimeout&""
''PasswordB=""
''Response.write PasswordB
''Response.write CONNStr
CONN.Open CONNstr
CONNErr="<br><br><li>请仔细核对你输入的ＳＱＬ数据库名称，ＩＰ源，帐号密码是否正确。"
elseif BASEtype="Oracle" then
ErrOrINFO="<br>尚未未建立ＯＲＡＣＬＥ连接。"
call errorX()
Response.end
end if
if err.number=0 and Session("PasswordB")="" then
Session("PasswordB")=PasswordB
Elseif err.number<>0 then
if Session("PasswordB")="" then CONNErr=CONNErr+"<br><br><li>或Session 密码过期，请重登陆！"
ErrOrINFO="<br>"+"<li>"&Err.Description&""+CONNErr
err.clear
ClosedataBase
call errorX()
end if
'CreatTABLE2
if BASEType="SQL2000" then '-DB_NAME
REM =========DB NAME =======读去数据库的库名对象集合,仅仅SQL==
rem=========(一)用openSchema(1)===
set rs=CONN.openSchema(1) '更多查看ADOVBS.INC
'while not rs.eof
'Response.Write rs.fields("CATALOG_NAME").value&"<br>"
'rs.movenext
'wend
'Response.end()
rem=========(二)用系统表master.dbo.sysdatabases===
'dim SQL_dbNAME
'SQL_dbNAME="select name from master.dbo.sysdatabases"
'set rs=CONN.Execute(SQL_dbNAME )
if err.number<>0 then
ErrOrINFO="<br>"+"<li>"&Err.Description&"。<br><br>"
err.clear
ClosedataBase
call errorX()
end if
Response.write "<table width='100%' align=center bgcolor=#989898 border=0 bordercolor=#666666 cellspacing=1 cellpadding=3>"
Response.write "<tr bgcolor='#ffffef' >"&_
"<td colspan=6 height=22 bgcolor=#989898 ><span style='margin-left:200px;'><font color=#ffffff><b>SQL SERVER 数据库 列表</b></font></span></td>"&_
"</tr>"&_
"<tr bgcolor='#ffffef' align='center'>"
jk=0
dim iDB_NAME
while not rs.EOF
iDB_NAME=rs(0)
Response.write "<th bgcolor='eeffff' width='16%'><a href='?BASEtype="&BASEtype&"&BASE_NAME="&iDB_NAME&"&DATA_Source="&DATA_Source&"&BaseOwner="&BaseOwner&"&table="&myTABLE&"&page=1'>"&iDB_NAME&"</a></th>"
jk=jk+1
if jk mod 6 =0 then Response.write "</tr><tr align='center'>"
RS.MoveNext
wend
rs.close
ek=jk mod 6
For i=1 to 6-ek '用余数计算把剩下的未填充表格填满'
Response.write "<td bgcolor='eeffff'> </td>"
next
jk=0
Response.write "</tr></table>"
'Response.end
end if '---------DB_NAME
'=============//
REM==有两个方法取表名
REM 1=====用openSchema(20|4)函数来选择表名,字段名,对ACESS,SQLserver通用=====
set rs=CONN.openSchema(20) '取得表名/
'set rs=CONN.openSchema(4) '取的表的字段名数据类型等//20:TABLE_NAME,TABLE_TYPE// 4:TABLE_COLUMNS,DATA_TYPE
dim CATA_ScheMA,TABLE_NAME,ServerIP,iTABLE_NAME,showidsSQL
showidsSQL=trim(request("SQL"))
serverIP=Request.ServerVariables("SERVER_NAME")
if BASEtype="SQL2000" then
CATA_ScheMA="<font color=#FFFFaa>数据库：<b>"&rs("TABLE_CATALOG")&"</b> 所有者：<b>"&rs("TABLE_ScheMA")&"</b>（共 <b><span id=tablecount></span></b> 表） //"&serverIP&"</font>"
if showidsSQL="" then
TABLE_NAME="<font color=#FFFFaa>当前表：<b>"&rs("TABLE_CATALOG")&"."&rs("TABLE_ScheMA")&"."&myTABLE&"</b>（共<span id=COLScount></span>字段）</font>"
else
TABLE_NAME="<font color=#FFFFaa>当前结果集：<b>"&rs("TABLE_CATALOG")&"."&rs("TABLE_ScheMA")&"."&myTABLE&"</b>（共<span id=COLScount></span>列）</font>"
end if
elseif BASEtype="ACCESS" then
CATA_ScheMA="<font color=#FFFFaa>数据库：<b>"&BASE_NAME&" （共 <b><span id=tablecount></span></b> 表）</b></font>"
if showidsSQL="" then
TABLE_NAME="<font color=#FFFFaa>当前表：<b>"&myTABLE&"</b> （共<span id=COLScount></span>字段）</font>"
else
TABLE_NAME="<font color=#FFFFaa>当前查询结果：<b>"&myTABLE&"</b> （共<span id=COLScount></span>列）</font>"
end if
elseif BASEtype="Oracle" then
CATA_ScheMA="<font color=#FFFFaa>数据库：<b>"&rs("TABLE_CATALOG")&"</b> 所有者：<b>"&rs("TABLE_ScheMA")&" （共 <b><span id=tablecount></span></b> 表）</b></font>"
TABLE_NAME="<font color=#FFFFaa>当前查询结果：<b>"&rs("TABLE_CATALOG")&"."&rs("TABLE_ScheMA")&"."&myTABLE&"</b></font>"
end if
if err.number<>0 then
ErrOrINFO="<br>"+"<li>"&Err.Description&"。<br><br><li>可能你改变了查询的数据库，请在上面点选新数据库的表"
err.clear
ClosedataBase
call errorX()
end if
REM================//
REM 2=====用系统表sysobject来选择表名,之后用fields.NAME来取的记录集的名字=====
'for Access: sql=SELECT * from MSysObjects;
'for SQL server: sql="SELECT name from sysobjects where type='U' order by crdate desc"
'type,xtype都是类型,U是用户表,S是系统表
'''''''''
rem 'set rs=server.createobject("adodb.recordset")
rem 'sql="SELECT name,type,xtype from sysobjects where xtype='U' order by name"
rem 'rs.open sql,CONN,0,1
REM =================//
Response.write "<table width='100%' align=center bgcolor=#989898 border=0 bordercolor=#666666 cellspacing=1 cellpadding=3>"
Response.write "<tr bgcolor='#ffffef' >"&_
"<td colspan=6 height=22 bgcolor=#989898 ><span style='margin-left:200px;'>"&CATA_ScheMA&"</span></td>"&_
"</tr>"&_
"<tr bgcolor='#ffffef' align='center'>"
while not rs.EOF
iTABLE_NAME=rs("TABLE_NAME")
if rs("TABLE_type")="TABLE" then '屏蔽掉系统表 DATA_Source,BaseOwner,PasswordB
'Response.write "<th bgcolor='eeffff'><a href='?"&server.urlencode("BASEtype="&BASEtype&"&BASE_NAME="&BASE_NAME&"&table="&iTABLE_NAME&"&page=1")&"'>"&iTABLE_NAME&"</a></th>"
Response.write "<th bgcolor='eeffff' width='16%'><a href='?BASEtype="&BASEtype&"&BASE_NAME="&BASE_NAME&"&DATA_Source="&DATA_Source&"&BaseOwner="&BaseOwner&"&table="&iTABLE_NAME&"&page=1'>"&iTABLE_NAME&"</a></th>"
'Response.write "<th bgcolor='eeffff'>"&rs("TABLE_catalog")&"</th>" '库名,ACCESS不支持
'Response.write "<th bgcolor='eeffff'>"&rs("TABLE_Schema")&"</th>" 所有者
'Response.write "<th bgcolor='eeffff'>"&rs("TABLE_type")&"</th>" 类型 表或系统表
'----用第二种方法读取表名
''Response.write "<th bgcolor='eeffff'><a href='?page=1&table="&rs(0)&"'>"&rs(0)&"</a></th>"
'===/
jk=jk+1
if jk mod 6 =0 then Response.write "</tr><tr align='center'>"
end if
RS.MoveNext
wend
rs.close
ek=jk mod 6
'---------填充剩余单元格---------
For i=1 to 6-ek '用余数计算把剩下的未填充表格填满'
Response.write "<td bgcolor='eeffff'> </td>"
next
%><script>window.tablecount.innerHTML='<%=jk%> ';</script><% '把统计的表数写当上端
rem===GetString=======行列分隔，第二个参数是指定提取纪录数,不指定则是全部提取,第一个分割列,第二个分割行
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -