onlineaccess.asp

学校成绩查询系统是一个非常好的软件请大家试试

   
   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
 
 ‘显示分页导航
   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         ‘循环字段名
      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>"
   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
 ‘==================================================================执行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", "")   ‘替换注释
    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语句,允许执行多条以分号分隔的语句
     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>&nbsp;&nbsp;<b>"
     echo iLoop + 1 & "、</b></font><font color=#CC6600>" & aSql(iLoop) & "</font><br>"
     ‘err.clear()     ‘忽略错误
     exit sub          ‘中止执行
    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>命令执行成功</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)

   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         ‘循环字段名
      set field = rs.fields.item(i)
      echo "<td bgcolor=‘#FFFFFF‘>" & field.name & " </td>"
   next
   echo "</tr>"
   
   dim i,field
   do while not rs.eof                      ‘循环数据
      echo "<tr>"
      for i = 0 to rs.fields.count - 1
         set field = rs.fields.item(i)
         echo "<td bgcolor=‘#FFFFFF‘>" & field.value & " &nbsp;</td>"
      next
      echo "</tr>"
      rs.MoveNext
   loop
   
   echo "</table></div>"
end sub   

%>

 

<%
‘==================================================================导出SQL
sub tosql(strt)
 ‘strt = 0 导出结构
 ‘strt = 1 导出内容
 dim strsql
 if strt = "0"  then
  table = request("table_name")
  echo "以下是表 <font color=red>" & request("table_name") & "</font> 的结构: "
  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> 数据库 </font> 的结构: "
  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

‘================================================================== 输出表结构
function getsql(table)
 on error resume next
 getsql = "-- 表结构 " & 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 "创建表：<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 "设置主键：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 [表名] 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 "新建默认值：ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT ‘51WINDOWS.NET‘ FOR [字段名]<br><br>"
 echo "删除默认值：ALTER TABLE [表名] DROP CONSTRAINT 默认值名<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>
表：&nbsp;<a href="">主界面</a>&nbsp;<a href="">退出</a>&nbsp;<a href="">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" ‘显示创建表界面
  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 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>
<!--程序界面主表格结束-->
</body>
</html>