?? sql&access edit.asp
字號:
dim rs
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql,conn,3
if not rs.eof then
rs.pageSize = pageSize
if cint(page) < 1 then page = 1
if cint(page) > rs.PageCount then page = rs.PageCount
rs.absolutePage = page
end if
'顯示分頁導(dǎo)航
showNavBar rs,page,pageUrl,pageSize
'-------------------------------
echo "<div 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><td bgcolor=#ffffff>刪</td>"
end if
for i = 0 to rs.fields.count - 1 '循環(huán)字段名
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 '循環(huán)數(shù)據(jù)
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>"
echo "<td><a href='?"&Request.QueryString&"&keylog="&rs(primarykey)&"' onClick='return table_delete();'><font color=#FF000>×</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
'==================================================================執(zhí)行sql函數(shù)
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", "") '替換注釋
sql = RegExpReplace(sql, "\n[\s| ]*\r", "") '替換空行
sql = RegExpReplace(sql, "\n", "") '替換換行符
sql = RegExpReplace(sql, "\r", "") '替換回車符
if (LCase(left(sql,len("select"))) = "select") and instr(sql,"into") = 0 then
Call showSelect (sql)
if err <> 0 then echo "<br><font color=red>" & err.description & "</font>"
response.end
else
'如果非select語句,允許執(zhí)行多條以分號分隔的語句
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 & "<br> <b>"
echo iLoop + 1 & "、</b></font><font color=#CC6600>" & aSql(iLoop) & "</font><br>"
'err.clear() '忽略錯誤
exit sub '中止執(zhí)行
else
echo "<div style='padding:3px 0px;border-bottom:1px solid #069;'><b>" & iLoop + 1 & "、</b>" & aSql(iLoop) & "</div>"
end if
end if
next
echo "<font color=red><h4>命令執(zhí)行成功</h4></font>"
end if
end sub
'顯示數(shù)據(jù)庫信息
'QueryType有以下三個主要參數(shù)
'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)
echo "<div style='overflow-x:auto;overflow-y:auto; width:800;height:380;'><table border=0 border=0 cellpadding=3 cellspacing=1 bgcolor=#CCCCCC><tr>"
for i = 0 to rs.fields.count - 1 '循環(huán)字段名
set field = rs.fields.item(i)
echo "<td bgcolor='#FFFFFF'>" & field.name & " </td>"
next
echo "</tr>"
dim i,field
do while not rs.eof '循環(huán)數(shù)據(jù)
echo "<tr>"
for i = 0 to rs.fields.count - 1
set field = rs.fields.item(i)
echo "<td bgcolor='#FFFFFF'>" & field.value & " </td>"
next
echo "</tr>"
rs.MoveNext
loop
echo "</table></div>"
end sub
%>
<%
'==================================================================導(dǎo)出SQL
sub tosql(strt)
'strt = 0 導(dǎo)出結(jié)構(gòu)
'strt = 1 導(dǎo)出內(nèi)容
dim strsql
if strt = "0" then
table = request("table_name")
echo "以下是表 <font color=red>" & request("table_name") & "</font> 的結(jié)構(gòu): "
echo "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>"
strsql = getsql(table)
end if
if strt = "2" then
echo "以下是 <font color=red> 數(shù)據(jù)庫 </font> 的結(jié)構(gòu): "
echo "<input type='button' name='ok' value=' 返 回 ' onClick='javascript:history.go(-1)'>"
set objSchema = Conn.OpenSchema(adSchemaTables)
Do While Not objSchema.EOF
if objSchema("TABLE_TYPE") = "TABLE" then
table = objSchema("TABLE_NAME")
strsql = strsql & getsql(table)'table & "|"'getsql(table)
end if
objSchema.MoveNext
Loop
objSchema.close
end if
echo "<textarea cols=110 rows=38>" & strsql & "</textarea>"
conn.close
end sub
'================================================================== 輸出表結(jié)構(gòu)
function getsql(table)
on error resume next
getsql = "-- 表結(jié)構(gòu) " & table & " 的SQL語句。" & chr(10)
dim primary,primarykey
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
tbl_struct = "CREATE TABLE [" & table & "] ( " & chr(10)
sql = "SELECT * FROM " & table
Set rs = Conn.Execute(sql)
if err = 0 then
for i = 0 to rs.fields.count-1
tbl_struct = tbl_struct & "[" & rs(i).name & "] "
typs = typ(rs(i).type)
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-2)
tbl_struct = tbl_struct & ");"
end if
else
tbl_struct = "CREATE TABLE [" & table & "];"
end if
getsql = getsql & tbl_struct & chr(10) & chr(10)
end function
sub help()
echo "SQL 常用語句：<br><br>"
echo "創(chuàng)建表：<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 "設(shè)置主鍵：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 數(shù)據(jù)表名稱<br><br>"
echo "添加字段：ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL<br><br>"
echo "刪除字段：alter table [tablename] drop [fieldname]<br><br>"
echo "修改字段：ALTER TABLE [表名] ALTER COLUMN [字段名] 類型(大小) NULL<br><br>"
echo "新建約束：ALTER TABLE [表名] ADD CONSTRAINT 約束名 CHECK ([約束字段] <= '2000-1-1')<br><br>"
echo "刪除約束：ALTER TABLE [表名] DROP CONSTRAINT 約束名<br><br>"
echo "新建默認(rèn)值：ALTER TABLE [表名] ADD CONSTRAINT 默認(rèn)值名 DEFAULT '51WINDOWS.NET' FOR [字段名]<br><br>"
echo "刪除默認(rèn)值：ALTER TABLE [表名] DROP CONSTRAINT 默認(rèn)值名<br><br>"
end sub
%>
<!--程序界面主表格開始-->
<table width="100%" height="100%" border="0" cellpadding="5" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td width="18%" valign="top">
<div id="Layer1" style="overflow-x:auto;overflow-y:auto; width:100%;height:100%;">
<div style="width:140px;height:0px;overflow:hidden;"></div>
表： <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%" 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" '顯示創(chuàng)建表界面
call add_table(request("table_name"),request("field_num"))
case "createtable" '執(zhí)行創(chuàng)建表
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 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()
case "reobj"
call reobj()
end select
%>
</div>
</td>
</tr>
</table>
<!--程序界面主表格結(jié)束-->
</body>
</html>?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -