ASP代码:在线批量修改Access数据库 |
时间:2006-03-31 00:00:00 来源:TIANZI.ORG 作者:天子 阅读:21997次 |
|
|
总需要批量修改数据库中的一些内容,所以这个代码很有用,今天用了一下效果不错 通过视图界面方式在线操作ACCESS , SQL数据库,浏览所有表、视图、存储过程;插入新表,修改表名,动态插入、修改、删除字段;自由执行SQL查询语句,支持多条SQl语句同时执行,支持标准SQl注释;插入、浏览、修改、删除表记录,方便快捷。将下面代码复制另存为mdb.asp即可
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=gb2312"> <title>在线数据库管理</title> <style type="text/css"> <!-- body,td,th {font-family: "宋体";font-size: 12px;} form {margin:0px;padding:0px;} body { margin-left: 10px; margin-top: 10px; SCROLLBAR-ARROW-COLOR:#666666; SCROLLBAR-FACE-COLOR:#DDDDDD; SCROLLBAR-DARKSHADOW-COLOR:#999999; SCROLLBAR-HIGHLIGHT-COLOR:#FFFFFF; SCROLLBAR-3DLIGHT-COLOR:#CCCCCC; SCROLLBAR-SHADOW-COLOR:#FFFFFF; SCROLLBAR-TRACK-COLOR:#EEEEEE; } input { border-width: 1px;border-style:solid;border-color: #CCCCCC #999999 #999999 #CCCCCC;height: 18px;} textarea {border-width: 1px;border-style: solid;border-color: #CCCCCC #999999 #999999 #CCCCCC;} a:link {text-decoration: none;} a:visited {text-decoration: none;} a:hover {text-decoration: underline;} a:active {text-decoration: none;} .fixSpan {width:150px;white-space:nowrap;word-break:keep-all;overflow:hidden;text-overflow:ellipsis;} --> </style> </head>
<body> <% if request("key") = "db" then session("dbtype") = request("dbtype") session("dbstr") = request("dbstr") response.redirect "?" end if
if request("key") = "createdatabase" then call createdatabase() end if
if session("dbtype") = "" or session("dbstr") = "" then %> <form action="?key=db" method="post" name="dbt"> <br> 连接类型: <input name="dbtype" type="radio" value="access" onClick="dbstr.value='Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=<%=server.mappath("/")%>;Password=;'" checked> ACCESS <input type="radio" name="dbtype" value="sql" onClick="dbstr.value='driver={SQL Server};database=;Server=;uid=;pwd='"> SQL<br><br> 连接字符:<input name="dbstr" type="text" id="dbstr" size="140" value="Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=<%=server.mappath("/")%>;Password=;"> <input type="submit" name="Submit" value="连接" /><br><br> 注:access请使用绝对路径,本文件路径:<%=server.MapPath("mdb.asp")%> </form> <form name="createdatabase" method="post" action="?key=createdatabase"> <font color=red>创建数据库:</font>路径 <input name="dataname" type="text" value="<%=server.MapPath("/")&"database.mdb"%>" size="100"> <input type="submit" name="Submit" value="创建"> </form> <% response.End() end if
'==================================================================建库 sub createdatabase() dim DBName,dbstr,myCat on error resume next DBName = request("dataname") dbstr = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & DBName Set myCat = Server.CreateObject( "ADOX.Catalog" ) myCat.Create dbstr 'Set Con = Server.CreateObject( "ADODB.Connection" ) 'Con.Open dbstr if err <> 0 then response.write err.description session("dbtype") = "" session("dbstr") = "" response.write "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>" response.end end if session("dbtype") = "access" session("dbstr") = dbstr response.redirect "?" end sub
'==================================================================调用链接函数 conn()
function conn() dim conn1,connstr on error resume next select case session("dbtype") case "access" '==================================================================连接ACCESS数据库 connstr = session("dbstr") Set Conn1 = Server.CreateObject("ADODB.Connection") conn1.Open connstr case "sql" '==================================================================连接SQL数据库 set conn1 = Server.CreateObject("ADODB.Connection") conn1.open session("dbstr") end select if err <> 0 then response.write err.description session("dbtype") = "" session("dbstr") = "" response.write "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>" response.end end if set conn = conn1 end function
Sub echo(str) Response.Write(str) End Sub
Function IIf(var, val1, val2) If var = True Then IIf = val1 Else IIf = val2 End If End Function
'正则表达式函数,用于删除注释 '------------------------------------- Function RegExpReplace(strng, patrn, replStr) Dim regEx,match,matches ' 建立变量。 Set regEx = New RegExp ' 建立正则表达式。 regEx.Pattern = patrn ' 设置模式。 regEx.IgnoreCase = True ' 设置是否区分大小写。 regEx.Global = True ' 设置全局可用性。
RegExpReplace = regEx.Replace(strng, replStr) ' 作替换。 End Function
'==================================================================ADOVBS 常量声明
'---- DataTypeEnum Values ---- Const adEmpty = 0 Const adTinyInt = 16 Const adSmallInt = 2 Const adInteger = 3 Const adBigInt = 20 Const adUnsignedTinyInt = 17 Const adUnsignedSmallInt = 18 Const adUnsignedInt = 19 Const adUnsignedBigInt = 21 Const adSingle = 4 Const adDouble = 5 Const adCurrency = 6 Const adDecimal = 14 Const adNumeric = 131 Const adBoolean = 11 Const adError = 10 Const adUserDefined = 132 Const adVariant = 12 Const adIDispatch = 9 Const adIUnknown = 13 Const adGUID = 72 Const adDate = 7 Const adDBDate = 133 Const adDBTime = 134 Const adDBTimeStamp = 135 Const adBSTR = 8 Const adChar = 129 Const adVarChar = 200 Const adLongVarChar = 201 Const adWChar = 130 Const adVarWChar = 202 Const adLongVarWChar = 203 Const adBinary = 128 Const adVarBinary = 204 Const adLongVarBinary = 205
'---- FieldAttributeEnum Values ---- Const adFldMayDefer = &H00000002 Const adFldUpdatable = &H00000004 Const adFldUnknownUpdatable = &H00000008 Const adFldFixed = &H00000010 Const adFldIsNullable = &H00000020 Const adFldMayBeNull = &H00000040 Const adFldLong = &H00000080 Const adFldRowID = &H00000100 Const adFldRowVersion = &H00000200 Const adFldCacheDeferred = &H00001000
'---- SchemaEnum Values ---- '---- SchemaEnum Values ---- Const adSchemaProviderSpecific = -1 Const adSchemaAsserts = 0 Const adSchemaCatalogs = 1 Const adSchemaCharacterSets = 2 Const adSchemaCollations = 3 Const adSchemaColumns = 4 Const adSchemaCheckConstraints = 5 Const adSchemaConstraintColumnUsage = 6 Const adSchemaConstraintTableUsage = 7 Const adSchemaKeyColumnUsage = 8 Const adSchemaReferentialConstraints = 9 Const adSchemaTableConstraints = 10 Const adSchemaColumnsDomainUsage = 11 Const adSchemaIndexes = 12 Const adSchemaColumnPrivileges = 13 Const adSchemaTablePrivileges = 14 Const adSchemaUsagePrivileges = 15 Const adSchemaProcedures = 16 Const adSchemaSchemata = 17 Const adSchemaSQLLanguages = 18 Const adSchemaStatistics = 19 Const adSchemaTables = 20 Const adSchemaTranslations = 21 Const adSchemaProviderTypes = 22 Const adSchemaViews = 23 Const adSchemaViewColumnUsage = 24 Const adSchemaViewTableUsage = 25 Const adSchemaProcedureParameters = 26 Const adSchemaForeignKeys = 27 Const adSchemaPrimaryKeys = 28 Const adSchemaProcedureColumns = 29 Const adSchemaDBInfoKeywords = 30 Const adSchemaDBInfoLiterals = 31 Const adSchemaCubes = 32 Const adSchemaDimensions = 33 Const adSchemaHierarchies = 34 Const adSchemaLevels = 35 Const adSchemaMeasures = 36 Const adSchemaProperties = 37 Const adSchemaMembers = 38 Const adSchemaTrustees = 39 Const adSchemaFunctions = 40 Const adSchemaActions = 41 Const adSchemaCommands = 42 Const adSchemaSets = 43
'==================================================================返回字段类型函数 Function typ(field_type) 'field_type = 字段类型值 Select Case field_type case adEmpty:typ = "Empty" case adTinyInt:typ = "TinyInt" case adSmallInt:typ = "SmallInt" case adInteger:typ = "Integer" case adBigInt:typ = "BigInt" case adUnsignedTinyInt:typ = "UnsignedTinyInt" case adUnsignedSmallInt:typ = "UnsignedSmallInt" case adUnsignedInt:typ = "UnsignedInt" case adUnsignedBigInt:typ = "UnsignedBigInt" case adSingle:typ = "Single" case adDouble:typ = "Double" case adCurrency:typ = "Currency" case adDecimal:typ = "Decimal" case adNumeric:typ = "Numeric" case adBoolean:typ = "Boolean" case adError:typ = "Error" case adUserDefined:typ = "UserDefined" case adVariant:typ = "Variant" case adIDispatch:typ = "IDispatch" case adIUnknown:typ = "IUnknown" case adGUID:typ = "GUID" case adDATE:typ = "DATE" case adDBDate:typ = "DBDate" case adDBTime:typ = "DBTime" case adDBTimeStamp:typ = "DBTimeStamp" case adBSTR:typ = "BSTR" case adChar:typ = "Char" case adVarChar:typ = "VarChar" case adLongVarChar:typ = "LongVarChar" case adWChar:typ = "WChar" case adVarWChar:typ = "VarWChar" case adLongVarWChar:typ = "LongVarWChar" case adBinary:typ = "Binary" case adVarBinary:typ = "VarBinary" case adLongVarBinary:typ = "LongVarBinary" case adChapter:typ = "Chapter" case adPropVariant:typ = "PropVariant" case else:typ = "Unknown" end select End Function
'==================================================================主界面 sub main(str) on error resume next %> <script language=javascript> ie = (document.all)? true:false if (ie){ function ctlent(eventobject){if(event.ctrlKey && window.event.keyCode==13){this.document.exesql.submit();}} } </script> <form action="?key=sql" method=post name="exesql"> <font color=red>执行sql语句:</font><font color=#999999>(每句语句以“;”结束,支持(--)SQL注释,Ctrl + Enter 快速提交)</font> <div style="float:left;width:600px;"> <textarea name="sql" cols="90" rows="8" ondblClick="this.select();" onKeyDown="ctlent()"><%=request("sql")%></textarea><br /> <input type="checkbox" name="SchemaTable" value="1" style="border:0px;">adSchemaTables <input type="checkbox" name="SchemaColumn" value="2" style="border:0px;">adSchemaColumns <input type="checkbox" name="SchemaProvider" value="3" style="border:0px;">adSchemaProviderTypes
</div> <div style="float:left;width:50px;padding:50px 0px 0px 5px;"> <input type="submit" name="Submit_confirm" value="提交"> <br /> <br /> <input type="button" name="Submit3" value="清空" onClick="sql.value=''"><br /><br /> <input type="button" name="ok" value="返回" onClick="javascript:history.go(-1)"> </div> </form> <div style="clear:both"></div> <% if str = "" then %> <form action="?key=addtable" method="post"> <div style="clear:both;text-align:left;"><br /> <font color=red>创建新表:</font><br> 表 名:<input type="text" name="table_name" size="20"><br> 字段数:<input type="text" name="field_num" size="20"> <input type="submit" name="Submit_create" value="提交"> <input type="reset" name="Submit32" value="重置"> </div> </form> <% end if end sub
'==================================================================创建表界面 sub add_table(table_name,field_num) 'table_name = 表名称 'field_num = 字段数 on error resume next %> <p class="hei"><span>创建表:</span><%=table_name%></p> <form action="?key=createtable" method="post"> <table width="600" border="0" cellpadding="2" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td width="75" height="20" align="center" bgcolor="#FFFFFF">字段名</td> <td width="99" height="20" align="center" bgcolor="#FFFFFF">类 型</td> <td width="73" height="20" align="center" bgcolor="#FFFFFF">大 小</td> <td width="96" height="20" align="center" bgcolor="#FFFFFF">空值</td> <td width="83" height="20" align="center" bgcolor="#FFFFFF">自动编号</td> <td width="143" height="20" align="center" bgcolor="#FFFFFF">主 键</td> </tr> <% for i = 0 to field_num - 1 %> <tr> <td width="75" height="20" align="center" bgcolor="#FFFFFF"> <input type="text" name="field_name" size="10"> </td> <td width="99" height="20" align="center" bgcolor="#FFFFFF"> <select name="field_type"> <option value=""></option> <option value="binary">BINARY</option> <option value="bit">BIT(Y/N)</option> <option value="char" selected>CHAR(文本)</option> <option value="datetime">DATETIME</option> <option value="float">FLOAT</option> <option value="image">DOUBLE</option> <option value="int">INT</option> <option value="money">MONEY</option> <option value="numeric">NUMERIC</option> <option value="real">REAL</option> <option value="smallint">SMALLINT</option> <option value="memo">MEMO</option> <option value="varchar">VARCHAR</option> </select> </td> <td width="73" height="20" align="center" bgcolor="#FFFFFF"> <input type="text" name="field_size" size="10"> </td> <td width="96" height="20" align="center" bgcolor="#FFFFFF"> <select name="null"> <option value="NOT_NULL">NOT_NULL</option> <option value="NULL">NULL</option> </select> </td> <td width="83" height="20" align="center" bgcolor="#FFFFFF"> <select size="1" name="autoincrement"> <option></option> <option>自动编号</option> </select> </td> <td width="143" height="20" align="left" bgcolor="#FFFFFF"> <select name="primarykey"> <option></option> <option value="primarykey">primarykey</option> </select> </td> </tr> <% next %> <tr> <td height="35" align="center" colspan="5" bgcolor="#FFFFFF"> <input type="hidden" name="i" value=<%=field_num%>> <input type="hidden" name="table_name" value="<%=table_name%>"> <input type="submit" name="Submit" value=" 提 交 "> <input type="reset" name="Submit2" value=" 重 置 "> <input type="button" name="ok" value=" 放 弃 " onClick="javascript:history.go(-1)"> </td> <td height="20" bgcolor="#FFFFFF"></td> </tr> </table> </form> <% end sub
'==================================================================构建创建表的SQL语句 sub create_table() dim sql,i,primarykey on error resume next sql = "Create TABLE ["&request("table_name")&"] (" for i = 1 to request("i") sql = sql & "[" & request("field_name")(i) & "] " & request("field_type")(i) if request("field_size")(i) <> "" then sql = sql & "(" & request("field_size")(i) & ")" end if if request("null")(i) = "NOT_NULL" then sql = sql & " not null" end if if request("autoincrement")(i) = "自动编号" then sql = sql & " identity" end if if request("primarykey")(i) = "primarykey" then primarykey = request("field_name")(i) end if if primarykey <> "" then sql = sql & "," end if next if primarykey<>"" then sql=sql&" primary key (["&primarykey&"]) " end if sql = sql & ")" response.redirect "?key=sql&sql=" & sql end sub
'==================================================================查看表结构函数 sub view(table_name) 'table_name = 表名称 dim rs,sql,table,primary,primarykey,i on error resume next table = table_name Set primary = Conn.OpenSchema(adSchemaPrimaryKeys,Array(empty, empty, table)) if primary("COLUMN_NAME") <> "" then primarykey = primary("COLUMN_NAME") end if primary.Close Set primary = Nothing set rs = Server.CreateObject("ADODB.RecordSet") sql = "Select * From [" & table_name & "]" rs.Open sql,Conn ,3,3 %> <font color=red>表:<%=table_name%></font><br><br> <table width="600" border="0" cellpadding="1" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td width="125" height="20" bgcolor="#FFFFFF" align="center">字 段 名</td> <td width="110" bgcolor="#FFFFFF" align="center">类 型</td> <td width="83" bgcolor="#FFFFFF" align="center"> 设定大小</td> <td width="48" bgcolor="#FFFFFF" align="center">允许空</td> <td width="76" bgcolor="#FFFFFF" align="center">自动编号</td> <td width="54" bgcolor="#FFFFFF" align="center">主键</td> <td width="82" bgcolor="#FFFFFF" align="center">执行操作</td> </tr> <% sql = "Select * FROM [" & table_name & "] " Set rs = Conn.Execute(sql) For i = 0 to rs.fields.count-1 %> <tr> <td height="20" bgcolor="#FFFFFF" align="left"><%=rs(i).name%></td> <td bgcolor="#FFFFFF" align="left"><%=typ(rs(i).type)%></td> <td bgcolor="#FFFFFF" align="center"><%=rs(i).definedsize%></td> <td bgcolor="#FFFFFF" align="center"><%=iif((rs(i).Attributes and adFldIsNullable)=0,"No","Yes")%></td> <td bgcolor="#FFFFFF" align="center"><%=iif(rs(i).Properties("ISAUTOINCREMENT") = True,"是","否")%></td> <td bgcolor="#FFFFFF" align="center"><%=iif(rs(i).name = primarykey,"是","否")%></td> <td bgcolor="#FFFFFF" align="center"> <a href="?key=editfidlevi&fidle=<%=rs(i).name%>&table_name=<%=table_name%>">修改</a> <a href="?key=sql&sql=alter table [<%=table_name%>] drop [<%=rs(i).name%>];" onClick="return table_delete();">删除</a> </td> </tr> <% next %> </table> <script language="javascript"> function table_delete() { if (confirm("确认删除该记录吗? 该操作将不可撤销!!!")) return true; else return false; } </script> <br> <a href="?key=tosql&strt=0&table_name=<%=table_name%>" target="_blank">导出表结构</a> <a href="?key=sql&sql=select * from <%=table_name%>&table_name=<%=table_name%>&primarykey=<%=primarykey%>">浏览表记录</a> <a href="?key=sql&sql=Drop TABLE <%=table_name%>" onClick="return table_delete();">删除表</a> <br><br> <% if primarykey = "" then echo "<font color=red>该表没有主键,执行操作可能会导致数据损坏或丢失。</font><br>" echo "你可以将:" echo "<select name='keyname'>" For i=0 to rs.fields.count-1 echo "<option value=" & rs(i).name & ">" & rs(i).name & "</option>" next echo "</select> " echo "<input type=button value=设为主键 onclick=""location.href='?key=sql&sql=Alter TABLE ["&table_name&"] ADD PRIMARY KEY (['+keyname.value+'])';"">" echo "<br><br>" end if rs.close set rs = nothing %> <font color=red>增加字段:</font><br><br> <form action="?key=addfield" method="post"> <table width="600" height="39" border="0" cellpadding="2" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td width="60" height="20" align="center" bgcolor="#FFFFFF">字段名</td> <td width="50" height="20" align="center" bgcolor="#FFFFFF">类型</td> <td width="58" height="20" align="center" bgcolor="#FFFFFF">设定大小</td> <td width="64" height="20" align="center" bgcolor="#FFFFFF">允许空值</td> <td width="66" height="20" align="center" bgcolor="#FFFFFF"> 自动编号</td> <td width="96" height="20" align="center" bgcolor="#FFFFFF"> </td> </tr> <tr> <td width="60" height="20" align="center" bgcolor="#FFFFFF"> <input type="text" name="fldname" size="10"> </td> <td width="50" height="20" align="center" bgcolor="#FFFFFF"> <select size="1" name="fldtype"> <option value=""></option> <option value="binary">BINARY</option> <option value="bit">BIT(是/否)</option> <option value="char">CHAR</option> <option value="datetime">DATETIME</option> <option value="float">FLOAT</option> <option value="image">DOUBLE</option> <option value="int">INT</option> <option value="money">MONEY</option> <option value="numeric">NUMERIC</option> <option value="real">REAL</option> <option value="smallint">SMALLINT</option> <option value="memo">MEMO</option> <option value="varchar">VARCHAR</option> </select> </td> <td width="58" height="20" align="center" bgcolor="#FFFFFF"> <input type="text" name="fldsize" size="10"> </td> <td width="64" height="20" align="center" bgcolor="#FFFFFF"> <input name="null" type="checkbox" value="ON" checked> </td> <td width="66" height="20" align="center" bgcolor="#FFFFFF"> <input type="checkbox" name="autoincrement" value="ON"> </td> <td width="96" height="20" align="center" bgcolor="#FFFFFF"> <input type="hidden" name="table_name" value="<%=table_name%>"> <input type="submit" value="提交"> </td> </tr> </table> </form> <% if err <> 0 then echo err.description echo "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>" response.end end if end sub
'==================================================================编辑数据 sub editdata() dim keys,names,values,action,rs,sql,tab on error resume next keys = request("primarykey") names = request("table_name") values = request("primarykeyvalue") action = request("action") Set rs = Server.CreateObject("Adodb.RecordSet") if action = "" or action = "save" or action = "new" then sql = "select * from " & names & " where " & keys & " = " & values end if if action = "pre" then sql = "select top 1 * from " & names & " where " & keys & " < " & values & " order by " & keys & " desc" end if if action = "next" then sql = "select top 1 * from " & names & " where " & keys & " > " & values & " order by " & keys & " asc" end if if action = "add" then sql = "Select * From [" & names & "]" end if rs.Open sql, conn, 1, 3 if rs.eof and action = "new" then sql = "Select * From [" & names & "]" rs.Open sql, conn, 1, 3 end if if action = "save" or action = "new" then If action = "new" Then rs.AddNew For Each tab In rs.Fields If Keys <> tab.Name Then rs(tab.Name) = Request.Form(tab.Name & "_Column") if err <> 0 then echo tab.name & err.description echo "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>" response.end end if End If Next rs.update end if echo "字段数据编辑<br>" echo "<table width=600 border=0 cellpadding=5 cellspacing=1 bgcolor=#CCCCCC>" echo "<form action='?key=edit&table_name=" & names & "&primarykey=" & keys & "&primarykeyvalue=" & iif(action<>"add",rs(keys),"") & "' method='post' name='editor'>" echo "<br>" echo "<input type=hidden name=action value=save>" echo iif(action="add","","<input type=submit value=保存 onclick=this.form.action.value='save';> ") echo "<input type=button value=添加 onclick=if(confirm('确实要添加当前为新记录吗?')){this.form.action.value='new';this.form.submit();};> " echo "<input type=button value=上一条 onclick=""this.form.action.value='pre';this.form.submit();""> " echo "<input type=button value=下一条 onclick=""this.form.action.value='next';this.form.submit();""> " echo "<a href='?key=view&table_name=" & names & "'>表结构</a> " echo "<a href='?key=sql&sql=select * from " & names & "&table_name="& names & "&primarykey="&keys&"'>表浏览</a> " echo "<a href='?'>主界面</a><br>" if not rs.eof or action = "add" then For Each tab In rs.Fields echo "" echo "<BR><font color=red>" & tab.Name & "</font> <font color=#999999>( " & typ(tab.Type) & " )</font><br>" if tab.Type = 201 or tab.Type = 203 then echo "<textarea style='width:600;' name=""" & tab.Name & "_Column"" rows=6" echo IIf(tab.Name = keys, " disabled title='主键约束,将无法被修改.'>", ">") if action <> "add" then echo trim(tab.value) echo "</textarea>" else echo "<input type='text' style='width:600;' name='" & tab.Name & "_Column'" echo IIf(tab.Name = keys, " disabled title='主键约束,将无法被修改.'", " ") & " value='" if action <> "add" then echo trim(tab.value) echo "'>" end if echo "<br>" Next else echo "<script>alert('已经没有了!');history.back();</script>" Response.End() end if echo "<br>" echo iif(action="add","","<input type=submit value=保存 onclick=this.form.action.value='save';> ") echo "<input type=button value=添加 onclick=if(confirm('确实要添加当前为新记录吗?')){this.form.action.value='new';this.form.submit();};> " echo "<input type=button value=上一条 onclick=""this.form.action.value='pre';this.form.submit();""> " echo "<input type=button value=下一条 onclick=""this.form.action.value='next';this.form.submit();""> " echo "<a href='?key=view&table_name=" & names & "'>表结构</a> " echo "<a href='?key=sql&sql=select * from " & names & "&table_name="& names & "&primarykey="&keys&"'>表浏览</a> " echo "<a href='?'>主界面</a> " echo "</form></table>" end sub
'==================================================================显示存储过程 sub showproc() dim sTableName,adox echo "存储过程:<font color=red>" & Request("table_name") & "<font><br>" sTableName = Request("table_name") Set adox = Server.CreateObject("ADOX.Catalog") adox.ActiveConnection = Conn echo "<textarea cols=70 rows=8>" & adox.Procedures(sTableName).Command.CommandText & "</textarea><br>" end sub
'==================================================================修改字段属性的界面 sub editfidlevi() dim sql,rs,i on error resume next sql = "Select * From [" & request("table_name") & "]" set rs = conn.execute(sql) for i = 0 to rs.fields.count - 1 if rs(i).name = request("fidle") then %> <script LANGUAGE="JavaScript"> function validate(theForm) { if (theForm.type.value == "") { alert("请输入数据类型"); theForm.type.focus(); return (false); } return (true); } </script> <font color=red>修改字段属性:</font> <form action="?key=editfidle&fidle=<%=request("fidle")%>&table_name=<%=request("table_name")%>" method="post" name=frm onSubmit="return validate(frm)"> <table width="600" border="0" cellpadding="2" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td width="60" height="20" bgcolor="#FFFFFF" align="center">字段名</td> <td width="50" height="20" bgcolor="#FFFFFF" align="center">类型</td> <td width="58" height="20" bgcolor="#FFFFFF" align="center">设定大小</td> <td width="64" height="20" bgcolor="#FFFFFF" align="center">允许空值</td> <td width="66" height="20" bgcolor="#FFFFFF" align="center">自动编号</td> <td width="96" height="20" bgcolor="#FFFFFF"></td> </tr> <tr> <td width="60" height="20" bgcolor="#FFFFFF" align="center"><%=rs(i).name%></td> <td width="50" height="20" bgcolor="#FFFFFF" align="center"> <select name="type"> <option value=""></option> <option value="binary">BINARY</option> <option value="bit">BIT(是/否)</option> <option value="char">CHAR</option> <option value="datetime">DATETIME</option> <option value="float">FLOAT</option> <option value="image">DOUBLE</option> <option value="int">INT</option> <option value="money">MONEY</option> <option value="numeric">NUMERIC</option> <option value="real">REAL</option> <option value="smallint">SMALLINT</option> <option value="memo">MEMO</option> <option value="varchar">VARCHAR</option> </select> </td> <td width="58" height="20" bgcolor="#FFFFFF"><input type="text" name="size" size="10"></td> <td width="64" height="20" bgcolor="#FFFFFF" align="center"> <input type="checkbox" name="null" value="checkbox"<%=iif((rs(i).Attributes and adFldIsNullable)=0,""," checked")%>> </td> <td width="66" height="20" bgcolor="#FFFFFF" align="center"> <input type="checkbox" name="autoincrement" value="checkbox"<%=iif(rs(i).Properties("ISAUTOINCREMENT") = True," checked","")%>> </td> <td width="96" height="20" bgcolor="#FFFFFF" align="center"> <input type="submit" name="Submit" value="提交"> </td> </tr> </table> </form> <% end if next end sub
'==================================================================执行修改字段属性 sub editfidle() on error resume next sql = "Alter TABLE [" & request("table_name") & "] " sql = sql&"Alter COLUMN [" & request("fidle") & "] " if request("type") <> "" then sql = sql & request("type") end if if request("size") <> "" and request("null") = "" then sql = sql & "(" & request("size") & ") " end if if request("size") <> "" and request("null") <> "" then sql = sql & "(" & request("size") & ")" end if if request("null") <> "ON" then sql = sql & " NOT NULL" end if sql = trim(sql) conn.execute(sql) response.redirect "?key=view&table_name="& request("table_name") end sub
'==================================================================分页导航 '分页导航 sub showNavBar (rs,page,pageUrl) page = cint(page) %> <table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#CCCCCC"> <tr> <% if request("primarykey") <> "" and request("table_name") <> "" then %> <td align="left" bgcolor="#FFFFFF">当前表:<font color=red><%=request("table_name")%></font> <a href="?key=edit&table_name=<%=request("table_name")%>&primarykey=<%=request("primarykey")%>&action=add">插入新记录</a> </td> <% end if %> <td align="right" bgcolor="#FFFFFF"> <% echo "共有" & rs.recordCount & "条纪录 当前" & page & "/" & rs.PageCount & "页" if page > 1 then echo "<a href='" & pageUrl & "&page=1'>首页</a> " echo "<a href='" & pageUrl & "&page=" & page - 1 & "'>上页</a> " end if if (rs.PageCount > 1 and page < rs.PageCount) then echo "<a href='" & pageUrl & "&page=" & page + 1 & "'>下页</a> " echo "<a href='" & pageUrl & "&page=" & rs.pageCount & "'>末页</a> " end if echo "转到:第" echo "<select name='select2' onChange='location.href=this.value;'>" dim i for i = 1 to rs.PageCount echo "<option value='"& pageUrl &"&page="& i & "' " if i = cint(page) then echo "selected" echo ">"& i &"</option>" next echo "</select>页" %> </td> </tr> </table> <% end sub
'==================================================================显示查询 sub showselect(sql) Const pageSize = 80 '设置每页显示的记录数 dim page,pageUrl '设置当前显示的页数 page = request("page") if page="" or not isNumeric(page) then page=1 pageUrl = "?key=sql&sql=" & sql if request("primarykey") <> "" and request("table_name") <> "" then pageUrl = pageUrl & "&table_name=" & request("table_name") & "&primarykey=" & request("primarykey") end if '-------------------------- dim rs set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql,conn,3 if not rs.eof then if cint(page) < 1 then page = 1 if cint(page) > rs.PageCount then page = rs.PageCount rs.pageSize = pageSize rs.absolutePage = page end if '显示分页导航 showNavBar rs,page,pageUrl '------------------------------- echo "<div id='Layer3' style='overflow-x:auto;overflow-y:auto; width:800;height:380;'>" echo "<table border=0 border=0 cellpadding=3 cellspacing=1 bgcolor=#CCCCCC><tr>" primarykey = request("primarykey") if primarykey <> "" and request("table_name") <> "" then echo "<td bgcolor=#ffffff>操作</td>" end if for i = 0 to rs.fields.count - 1 '循环字段名 set field = rs.fields.item(i) echo "<td bgcolor=#ffffff>" & field.name & " </td>" next echo "</tr>" dim i,field,j do while not rs.eof and j < rs.pageSize '循环数据 echo "<tr>" if primarykey <> "" and request("table_name") <> "" then echo "<td bgcolor=#ffffff nowrap><a href='?key=edit&table_name=" & request("table_name") & "&primarykey=" & primarykey & "&primarykeyvalue=" & rs(primarykey) & "'><font color=#666666>编辑</font></a></td>" end if for i = 0 to rs.fields.count - 1 set field = rs.fields.item(i) if len(field.value) < 12 then echo "<td bgcolor=#ffffff nowrap>" & field.value & " </td>" else echo "<td bgcolor='#ffffff'><span class='fixspan'>" & field.value & " </span></td>" end if next echo "</tr>" rs.MoveNext j = j + 1 loop 'response.ContentType ="application/vnd.ms-excel"'生成EXCEL表格 echo "</table></div>" end sub
sub exesql(sql) on error resume next '==================================================================执行sql函数 if trim(request.form("SchemaTable")) <> "" then Call showSchema (adSchemaTables) if trim (request.form("SchemaColumn")) <> "" then Call showSchema(adSchemaColumns) if trim (request.form("SchemaProvider")) <> "" then Call showSchema(adSchemaProviderTypes)
sql = trim(request("sql")) if sql = "" then exit sub sql = RegExpReplace(sql, "(--)(.)*n", "") if (LCase(left(sql,len("select"))) = "select") then Call showSelect (sql) response.end else '如果非select语句,允许执行多条以分号分隔的语句 dim aSql,iLoop aSql = split(sql,";") for iLoop = 0 to UBound(aSql) if trim(aSql(iLoop)) <> "" then conn.execute (aSql(iLoop)) if err <> 0 then echo "<br><font color=red>" & err.description & "</font>" exit sub else echo iLoop + 1 & "、" & aSql(iLoop) & "<br>" end if end if next echo "<font color=red><h4>命令执行成功</h4></font>" end if end sub
'显示数据库信息 'QueryType有以下三个主要参数 'adSchemaTables 'adSchemaColumns 'adSchemaProviderTypes 'Call showSchema (adSchemaTables) sub showSchema(QueryType) dim rs 'set rs = conn.OpenSchema() set rs = conn.OpenSchema (QueryType) 'set rs = conn.OpenSchema (adSchemaProviderTypes)
response.write "<br><table border=0 border=0 cellpadding=3 cellspacing=1 bgcolor=#CCCCCC><tr>" for i = 0 to rs.fields.count - 1 '循环字段名 set field = rs.fields.item(i) response.write "<td bgcolor='#FFFFFF'>" & field.name & " </td>" next response.write "</tr>" dim i,field do while not rs.eof '循环数据 response.write "<tr>" for i = 0 to rs.fields.count - 1 set field = rs.fields.item(i) response.write "<td bgcolor='#FFFFFF'>" & field.value & " </td>" next response.write "</tr>" rs.MoveNext loop response.write "</table>" end sub
'==================================================================添加字段函数 sub addfield() on error resume next fldname = request("fldname") fldtype = request("fldtype") fldsize = request("fldsize") fldnull = request("null") fldautoincrement = request("autoincrement") table_name = request("table_name") if fldname <> "" and fldtype <> "" then sql = "alter table [" & table_name & "] add ["&fldname&"] " & fldtype if fldsize <> "" then sql = sql & "(" & fldsize & ")" end if if fldnull <> "ON" then sql = sql & " not null" end if if fldautoincrement = "ON" then sql = sql & " indentity" end if conn.execute(sql) response.redirect "?key=view&table_name=" & table_name else echo "输入数据错误!<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>" end if if err <> 0 then echo err.description echo "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>" response.end end if end sub %>
<% '==================================================================导出SQL sub tosql(strt) 'strt = 0 导出结构 'strt = 1 导出内容 if strt = "0" then echo "以下是表 <font color=red>" & request("table_name") & "</font> 的结构: <A href='javascript:window.close();'>关闭窗口</A><br>" table = request("table_name") Set primary = Conn.OpenSchema(adSchemaPrimaryKeys, _ Array(empty, empty, table)) if primary("COLUMN_NAME") <> "" then primarykey = primary("COLUMN_NAME") end if primary.Close set primary = nothing table = "["&request("table_name")&"]" tbl_struct = "Create TABLE " & table & " (" & chr(10) sql = "Select * FROM " & table Set rs = Conn.Execute(sql) for i = 0 to rs.fields.count-1 tbl_struct = tbl_struct & "[" & rs(i).name & "] " field_type = rs(i).type Select Case field_type case 3 typs="INT" case 2 typs="SMALLINT" case 4 typs="REAL" case 6 typs="MONEY" case 5 typs="NUMERIC" case 7 typs="DATETIME" case 11 typs="BIT" case 130 typs="CHAR" case 202 typs="VARCHAR" case 203 typs="MEMO" case 205 typs="IMAGE" case 128 typs="BINARY" End Select if typs = "VARCHAR" or typs = "BINARY" or typs = "CHAR" then tbl_struct = tbl_struct & typs & "(" & rs(i).definedsize & ")" else tbl_struct = tbl_struct & typs & " " end if attrib = rs(i).attributes if (attrib and adFldIsNullable) = 0 then tbl_struct = tbl_struct&" NOT NULL" end if if rs(i).Properties("ISAUTOINCREMENT") = True then tbl_struct = tbl_struct & " IDENTITY" end if tbl_struct = tbl_struct & "," & chr(10) next if primarykey <> "" then tbl_struct = tbl_struct & "PRIMARY KEY ([" & primarykey & "]))" else len_of_sql = Len(tbl_struct) tbl_struct = Mid(tbl_struct,1,len_of_sql-1) tbl_struct = tbl_struct & ")" end if echo "<textarea cols=110 rows=30>" & tbl_struct & "</textarea>" conn.close response.end end if end sub
sub help() echo "SQL 常用语句:<br><br>" echo "创建表:<br>" echo "Create TABLE [表名] (<br>" echo "[test1] int not null identity,<br>" echo "[test2] binary not null,<br>" echo "primary key ([test1]))<br><br>" echo "设置主键:<br>" echo "Alter TABLE [tablename] ADD PRIMARY KEY ([fieldname])<br><br>" echo "查询:select * from tablename where fieldname *** order by id desc<br><br>" echo "更新:update tanlename set fieldname = values,cn_name='values' where ID = 1<br><br>" echo "添加:insert into tanlename (fieldnam,fieldnam2)values (1,'values')<br><br>" echo "删除:delete from tanlename where fieldname = values<br><br>" echo "删除表:Drop TABLE 数据表名称<br><br>" echo "删除字段:alter table [tablename] drop [fieldname]<br><br>"
end sub %>
<!--程序界面主表格开始--> <table width="100%" height="100%" border="0" cellpadding="5" cellspacing="1" bgcolor="#CCCCCC"> <tr> <td width="18%" bgcolor="#FFFFFF" valign="top">
<div id="Layer1" style="overflow-x:auto;overflow-y:auto; width:100%;height:100%;"> 表: <a href="?">主界面</a> <a href="?key=exit">退出</a> <a href="?key=help">Help</a><br>
<% set objSchema = Conn.OpenSchema(adSchemaTables) Do While Not objSchema.EOF if objSchema("TABLE_TYPE") = "TABLE" then '输出表名 echo "<a href='?key=view&table_name="& objSchema("TABLE_NAME") &"'>" & objSchema("TABLE_NAME") & "</a><br>" end if objSchema.MoveNext Loop
echo "所有视图:<br>" objSchema.MoveFirst Do While Not objSchema.EOF if objSchema("TABLE_TYPE") = "VIEW" then '输出表名 echo "<a href='?key=sql&sql=Select * FROM [" & objSchema("TABLE_NAME")& "]'>" & objSchema("TABLE_NAME") & "</a><br>" end if objSchema.MoveNext Loop objSchema.Close set objSchema = nothing
echo "存储过程:<br>" set objSchema = Conn.OpenSchema(adSchemaProcedures) Do While Not objSchema.EOF echo "<a href='?key=proc&table_name="& objSchema("PROCEDURE_NAME") &"'>" & objSchema("PROCEDURE_NAME") & "</a><br>" objSchema.MoveNext Loop objSchema.Close set objSchema = nothing %> </div> </td> <td width="82%" bgcolor="#FFFFFF" valign="top"> <div id="Layer2" style="overflow-x:anto;overflow-y:auto; width:100%;height:100%;"> <%
select case request("key") case "" '显示主界面 call main("") case "addtable" '显示创建表界面 call add_table(request("table_name"),request("field_num")) case "createtable" '执行创建表 call create_table() case "view" call view(request("table_name")) case "sql" call main("1") call exesql(trim(request("sql"))) case "addfield" call addfield() case "editfidlevi" call editfidlevi() call view(request("table_name")) case "editfidle" call editfidle() case "exit" session("dbtype") = "" session("dbstr") = "" session("db007pass") = "" response.redirect "?" case "tosql" call tosql(request("strt")) case "proc" call main("1") call showproc() case "help" call help() case "edit" call EditData() end select %> </div> </td> </tr> </table> <!--程序界面主表格结束--> </body> </html>
|
|
|
|
|
|