Database structure operation

zhaozj2021-02-11 171

Author: tonny reprint please show the source: http: //www.weiw.com

Database structure operation. Adapt to common databases such as Access, SQL Server.

1. establish connection. Can be connected via ODBC or OLEDB. Set gObjDC = Server.CreateObject ( "ADODB.Connection") dim strconn, myDSNmyDSN = "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 = strconngobjdc.open

2. Display all tables set gobjrs = gobjdc.openschema (adschematables) do while not gobjrs.eofif gobjrs.fields ("Table_Type") = "Table" and left ("Table_Name"), 4) <> "msys" THEN 'Don't have to display system tables to response.write "" response.write "" & gobjrs.fields ("Table_Name") & "" myplink = "? DSN_NAME =" & MYDSN & " & Table_name = "& gobjrs.fields (" Table_name ") Response.write" " response.write < A href = TableContent.asp "& myplink &"> Content "response.write" "& vbcrlfend ifgobjrs.movenextLoopgobjrs.closeXTloopgobjrs.close

3. New Table > Table Name:

field count: < BR>

DefineTable.asp Main Source MYFIELDCOUNT = Request.form ("Field_count")

& Table_name = <% = Request. Form ("Table_name")%> & Field_count = <% = myfieldcount%> "ID =" form1 "> "& vbrlfresponse.write"

Name

TYPE

Length

NULL

PRIMARY Key

Unique Index <% for i = 1 to myfieldcount%>

ID = "text2">

ID = "checkbox1">

ID ="

Checkbox2 "> <% next%>

createtable.asp main source myPrimary = "" mySQLQueryString = "CREATE TABLE" & myTable & "(" myFieldCount = CInt (Request.QueryString ( "Field_Count")) For i = 1 to myFieldCountmyFieldName = Request.Form ( "FieldName _" & i ) mySQLQueryString = mySQLQueryString & Chr (34) & _myFieldName & Chr (34) & "" & _Request.Form ( "FieldType _" & i) myLength = Request.Form ( "FieldLength _" & i) If isNumeric (myLength) ThenmySQLQueryString = mySQLQueryString & "( "& myLength &") "End IfmySQLQueryString = mySQLQueryString &" "& Request.Form (" FieldNull _ "& i) If Request.Form (" FieldUnique _ "& i) <>" "ThenmySQLQueryString = mySQLQueryString &" CONSTRAINT pk "& myFieldName &" UNIQUE "End IfmySQLQueryString = mySQLQueryString &", "If Request.Form (" FieldPrimary _ "& i) <>" "ThenmyPrimary = myPrimary & Chr (34) & myFieldName & Chr (34) &", "End IfNextmySQLQueryString = Left (mySQLQueryString, Len (mysqlQueryString) -2) if myprimary <> "" ThenmyPrimary = left (myprimary, len (myprimary) -2) mysqlQueryString = mysqlQueryString & "," & "Constraint C OnTRAINT Primary Key ("& myprimary &") "end ifmysqlQueryString = mysqlQueryString &"); "'response.write mysqlQueryStringGObjdc.execute mysqlQueryString

4. Display table structure set gobjrs = server.createObject ("AdoDb.Recordset") gobjrs.open "[" & myTable & "]", GOBJDC, Adopenforwardonly, AdlockReadonlyfor I = 0 to gobjrs.fields.count - 1Response.write

"& gobjrs.fields (i) .name &" "& vbcrlfmytype = gettype (gobjrs.fields (i) .type) response.write"

"& Mytype & "" & vbcrlfmylength = "" IF mytype <> "longtext" and mytype <> "longbinary" TenmyLength = gobjrs.fields (i) .definedsize end ifresponse.write "

" & Mylength & " "& vbCrlfResponse.Write"

"& vbCrlfmyLink =" dropfield1.asp? dSN_Name = "& myDSN &" & Table_Name = "& myTable &" & Field_Name = "& gObjRS.Fields (i) .NameResponse.Write "" " ThenmysqlQueryString = mysqlQueryString & "Constraint PK" & myfieldname & "unique" end ifgobjdc.execute mysqlQueryString

6. Delete a field mysqlQueryString = "ALTER TABLE" & MyTable & "Drop Column" & Request.QueryString ("Field_Name") & ";" gobjdc.execute mysqlQueryString

7. Deletes an mySQLQueryString = "DROP TABLE" & myTablegObjDC.execute mySQLQueryString attached: '---- DataTypeEnum Values ---- Const adEmpty = 0Const adTinyInt = 16Const adSmallInt = 2Const adInteger = 3Const adBigInt = 20Const adUnsignedTinyInt = 17Const adUnsignedSmallInt = 18Const adUnsignedInt = 19Const adUnsignedBigInt = 21Const adSingle = 4Const adDouble = 5Const adCurrency = 6Const adDecimal = 14Const adNumeric = 131Const adBoolean = 11Const adError = 10Const adUserDefined = 132Const adVariant = 12Const adIDispatch = 9Const adIUnknown = 13Const adGUID = 72Const adDate = 7Const adDBDate = 133Const adDBTime = 134Const adDBTimeStamp = 135Const adBSTR = 8Const adChar = 129Const adVarChar = 200Const adLongVarChar = 201Const adWChar = 130Const adVarWChar = 202Const adLongVarWChar = 203Const adBinary = 128Const adVarBinary = 204Const adLongVarBinary = 205Const adChapter = 136Const adFileTime = 64Const adDBFileTime = 137Const adPropVariant = 138Const adVarNumeric = 139

转载请注明原文地址:https://www.9cbs.com/read-5613.html

9cbs

New Post(0)