Data | database | Database structure Author: Tonny
Reprint please show the Source: http://www.weiw.com
Database structure operations. Adapts to common databases such as Access,sql server.
1. Establish a connection.
Can be connected through ODBC or OLE DB.
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. Show All Tables
Set Gobjrs = Gobjdc.openschema (adSchemaTables)
Do as not gobjrs.eof
If gobjrs.fields ("table_type") = "TABLE" and Left (Gobjrs.fields ("table_name"), 4) <> "Msys" Then
' You don't have to show the system table.
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. Create a new table
<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>
Main source of 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%> >
<!-Please note that the field types are filled out here according to different databases->
<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>
Main source of 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 (+) & _
Myfieldname & Chr (a) & "" &_
Request.Form ("Fieldtype_" &i)
Mylength = Request.Form ("Fieldlength_" &i)
If IsNumeric (mylength) Then
mysqlquerystring = mysqlquerystring & "(& Mylength &)"
E
