IDC资讯中心
作者：tonny
转载请显示出处：http://www.weiw.com
数据库结构操作。适应于access，sql server等常见的数据库。
1。建立连接。
可以通过odbc或oledb连接。
set gobjdc = server.createobject("adodb.connection")
dim strconn,mydsn
mydsn="test"
strconn="dsn="&mydsn&";uid=sa;pwd="
strconn ="provider=sqloledb.1;persist security info=true;user id=sa;initial catalog=meisha;data source=tonny"
gobjdc.connectionstring=strconn
gobjdc.open
2。显示所有表
set gobjrs = gobjdc.openschema(adschematables)
do while not gobjrs.eof
if gobjrs.fields("table_type") = "table" and left(gobjrs.fields("table_name"), 4) <> "msys" then
不必把系统表显示出来
response.write "<tr>"
response.write "<td>" & gobjrs.fields("table_name") & "</td>"
myplink = "?dsn_name=" & mydsn & "&table_name=" & gobjrs.fields("table_name")
response.write "<td> <a href=tablestruct.asp" & myplink & ">structure</a> </td>"
response.write "<td> <a href=tablecontent.asp" & myplink & ">content</a> </td>"
response.write "</tr>" & vbcrlf
end if
gobjrs.movenext
loop
gobjrs.close
3。新建表
<form method=post action="definetable.asp?dsn_name=<% =mydsn %>">
table name :<br>
<input type="text" name="table_name"><br>
field count :<br>
<input type="text" name="field_count"><br>
<br>
<input type="submit" value="create">
</form>
definetable.asp中主要源码
myfieldcount = request.form("field_count")
<form method=post action="createtable.asp?dsn_name=<% =request.form("dsn_name") %>&table_name=<% =request.form("table_name") %>&field_count=<%=myfieldcount %>" id="form1">
<table id="table2">
<tr>
<td>name</td>
<td>type</td>
<td>length</td>
<td>null</td>
<td>primary key</td>
<td>unique index</td>
</tr>
<% for i=1 to myfieldcount%>
<tr>
<td><input type="text" name=<% ="fieldname_" & i %> ></td>
<td><select size=1 name=<% ="fieldtype_" & i %> >
<!-请注意：此处根据不同数据库填写字段类型->
<option>binary
<option>bit
<option>byte
<option>counter
<option>currency
<option>datetime
<option>single
<option>double
<option>short
<option>long
<option>longtext
<option>longbinary
<option>text
</select>
</td>
<td><input type="text" size=5 name=<% ="fieldlength_" & i %> id="text2"></td>
<td>
<select size=1 name=<% ="fieldnull_" & i %> id="select2">
<option>not null
<option>null
</select>
</td>
<td><input type="checkbox" name=<% ="fieldprimary_" & i %> id="checkbox1"></td>
<td><input type="checkbox" name=<% ="fieldunique_" & i %> id="checkbox2"></td>
</tr>
<%next%>
</table>
<input type="reset" value="clear" id="reset1" name="reset1"> <input type="submit" value="create" id="submit1" name="submit1">
</form>
createtable.asp中主要源码
myprimary = ""
mysqlquerystring = "create table " & mytable &" ("
myfieldcount = cint(request.querystring("field_count"))
for i = 1 to myfieldcount
myfieldname = request.form("fieldname_"&i)
mysqlquerystring = mysqlquerystring & chr(34) & _
myfieldname & chr(34) & " " &_
request.form("fieldtype_"&i)
mylength = request.form("fieldlength_"&i)
if isnumeric(mylength) then
mysqlquerystring = mysqlquerystring & " (" & mylength & ") "
end if
mysqlquerystring = mysqlquerystring & " " & request.form("fieldnull_"&i)
if request.form("fieldunique_"&i) <> "" then
mysqlquerystring = mysqlquerystring & " constraint pk" & myfieldname & " unique"
end if
mysqlquerystring = mysqlquerystring & ", "
if request.form("fieldprimary_"&i) <> "" then
myprimary = myprimary & chr(34) & myfieldname & chr(34) & ", "
end if
next
mysqlquerystring = left(mysqlquerystring, len(mysqlquerystring)-2)
if myprimary <> "" then
myprimary = left(myprimary, len(myprimary)-2)
mysqlquerystring = mysqlquerystring & ", " & "constraint contraint primary key(" & myprimary & ")"
end if
mysqlquerystring = mysqlquerystring & ");"
response.write mysqlquerystring
gobjdc.execute mysqlquerystring
4。显示表结构
set gobjrs = server.createobject("adodb.recordset")
gobjrs.open "[" & mytable & "]", gobjdc, adopenforwardonly, adlockreadonly
for i = 0 to gobjrs.fields.count – 1
response.write "<tr>" & vbcrlf
response.write "<td>" & gobjrs.fields(i).name & "</td>" & vbcrlf
mytype = gettype(gobjrs.fields(i).type)
response.write "<td>" & mytype & "</td>"& vbcrlf
mylength = " "
if mytype <> "longtext" and mytype <> "longbinary" then
mylength = gobjrs.fields(i).definedsize
end if
response.write "<td>" & mylength & "</td>"& vbcrlf
response.write "<td>"& vbcrlf
mylink = "dropfield1.asp?dsn_name=" & mydsn & "&table_name=" & mytable & "&field_name=" & gobjrs.fields(i).name
response.write "<a href=" & mylink & "><b> drop " & gobjrs.fields(i).name & " field</b></a>"
response.write "</td>"& vbcrlf
response.write "</tr>"& vbcrlf
next
gobjrs.close
function gettype(pconstant)
select case pconstant
case adbinary ’128
gettype = "binary"
case adboolean ‘11
gettype = "boolean"
case adunsignedtinyint ’17
gettype = "byte"
case adinteger ‘3
gettype = "long"
case adcurrency ’6
gettype = "currency"
case addbtimestamp ‘135
gettype = "datetime"
case adsingle ’4
gettype = "single"
case addouble ‘5
gettype = "double"
case adsmallint ’2
gettype = "short"
case adlongvarchar ‘201
gettype = "longtext"
case adlongvarbinary ’205
gettype = "longbinary"
case advarchar ‘200
gettype = "text"
case else
gettype = "unknow(" & pconstant & ")"
end select
end function
5。添加一字段
mysqlquerystring = "alter table " & mytable & " add column " & request.form("fieldname") & " "
mysqlquerystring = mysqlquerystring & request.form("fieldtype") & " "
mylength = request.form("fieldlength")
if isnumeric(mylength) then
mysqlquerystring = mysqlquerystring & "(" & mylength & ") "
end if
mysqlquerystring = mysqlquerystring & request.form("fieldnull") & " "
if request.form("fieldunique") <> "" then
mysqlquerystring = mysqlquerystring & " constraint pk" & myfieldname & " unique"
end if
gobjdc.execute mysqlquerystring
6。删除一字段
mysqlquerystring = "alter table " & mytable & " drop column " & request.querystring("field_name") & ";"
gobjdc.execute mysqlquerystring
7。删除一表
mysqlquerystring = "drop table " & mytable
gobjdc.execute mysqlquerystring
附：—- 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
const adchapter = 136
const adfiletime = 64
const addbfiletime = 137
const adpropvariant = 138
const advarnumeric = 139
