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 "response.write" & vbcrfend ifgobjrs.movenextLoopGobjrs.closeX
3. New table
DefineTable.asp Main Source MYFIELDCOUNT = Request.form ("Field_count")
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, AdlockReadOnfor i = 0 to Gobjrs.fields.count - 1Response.write "& vbcrfresponse.write "" & gobjrs.fields (i) .Name & "& vbcrlfmytype = gettype (gobjrs.fields (i) .Type) response.write" & mytype & "" & vbcrlfmylength = "" IF MyTYPE <> "LONGTEXT" AND myType <> "LONGBINARY" ThenmyLength = gObjRS.Fields (i) .DefinedSize End IfResponse.Write "" & myLength & "" & vbCrlfResponse.Write "" & vbCrlfmyLink = "dropfield1.asp? dSN_Name =" & myDSN & "& Table_Name =" & meTable & "& Field_Name =" & gobjrs.fields (i) .nameresponse.write "Drop" & gobjrs.fields (i) .Name & "Field" response.write "" & vbcrfResponse.write " & vbcrlfnextgobjrs.close
Function GetType (pConstant) Select Case pConstantCase adBinary '128GetType = "BINARY" Case adBoolean' 11GetType = "BOOLEAN" Case adUnsignedTinyInt '17GetType = "BYTE" Case adInteger' 3GetType = "LONG" Case adCurrency '6GetType = "CURRENCY" Case adDBTimeStamp' 135GetType = "DATETIME" Case adSingle '4GetType = "SINGLE" Case adDouble' 5GetType = "DOUBLE" Case adSmallInt '2GetType = "SHORT" Case adLongVarChar' 201GetType = "LONGTEXT" Case adLongVarBinary '205GetType = "LONGBINARY" Case adVarChar' 200GetType = "Text" case elsegettype = "unknow (" & pconstant & ")" End Selectnd Function5. Add a field mySQLQueryString = "ALTER TABLE" & myTable & "ADD COLUMN" & Request.Form ( "FieldName") & "" mySQLQueryString = mySQLQueryString & Request.Form ( "FieldType") & "" myLength = Request.Form ( " FieldLength ") If isNumeric (myLength) ThenmySQLQueryString = mySQLQueryString &" ( "& myLength &") "End IfmySQLQueryString = mySQLQueryString & Request.Form (" FieldNull ") &" "If Request.Form (" FieldUnique ") <>" " 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
