<% '============================================================== ' TableEditoR 0.81 Beta ' http://www.2enetworx.com/dev/projects/tableeditor.asp '-------------------------------------------------------------- ' File: te_fieldedit.asp ' Description: Adds or edits a field in a table ' Initiated By Hakan Eskici on Nov 15, 2000 '-------------------------------------------------------------- ' Copyright (c) 2002, 2eNetWorX/dev. ' ' TableEditoR is distributed with General Public License. ' Any derivatives of this software must remain OpenSource and ' must be distributed at no charge. ' (See license.txt for additional information) ' ' See Credits.txt for the list of contributors. ' ' Change Log: '------------------------------------------------------------- ' # Nov 21, 2000 by Hakan Eskici ' Fixed a bug regarding the index to be in the specified table ' Added the check to see if a field is indexed (not being a PK) ' Added sSQL2 to create an index not being a constraint ' # Nov 27, 2000 by Hakan Eskici ' Changed sql data type mappings for memo and boolean ' # May 11, 2002 by Hakan Eskici ' Small fixes for autoincrement, unique and primary key issues ' # May 30, 2002 by Rami Kattan ' Security check if user can add fields ' Security fix, fieldedit require that user have the TableEdit permission too ' Fix bug: DSN and SQL cannot show autoincrement with ADOX '============================================================== %> <% sub ShowFieldForm if sName = "" then sName = sNewName %>
Home » Connections » "><%=arrDesc(request("cid"))%> » &tablename=<%=server.urlencode(request("tablename"))%>">Edit Table [<%=request("tablename")%>] » Edit Field <% if bProtected then response.write session("teFullName") response.write " (logout)" end if %>

<% if (bFldEdit or bFldAdd) and bTableEdit then %>

<%=sErr%>

<% if sNote <> "" then response.write "" %>
Name
Type
Size (if text)
Attributes >Accept Nulls?
>Indexed?
>Unique?
>Primary Key?
>Auto Increment? (if numeric)<% if sNote <> "" then response.write " *"%>

" & sNote & "

<% else Response.Write "

You have no permission to add/edit fields

" end if end sub dim aT(10,2) aT(1,1) = adVarWChar :aT(1,2) = "text" aT(2,1) = adInteger :aT(2,2) = "long" aT(3,1) = adSmallInt :aT(3,2) = "integer" aT(4,1) = adBoolean :aT(4,2) = "boolean" aT(5,1) = adDate :aT(5,2) = "date" aT(6,1) = adCurrency :aT(6,2) = "currency" aT(7,1) = adLongVarWChar :aT(7,2) = "memo" aT(8,1) = adLongVarBinary :aT(8,2) = "ole" aT(9,1) = adGUID :aT(9,2) = "guid" aT(10,1)= adUnsignedTinyInt :aT(10,2)= "byte" lConnID = request("cid") sTableName = request("tablename") sName = request("fldname") if sName = "" then bNewField = True 'on error resume next if request("cmdSave") <> "" and (bFldEdit or bFldAdd) then 'Submitted new field definition OpenRS arrConn(lConnID) sNewName = request("txtName") sBefore = request("txtBefore") lType = CLng(request("cboType")) if request("txtSize") <> "" then lSize = CLng(request("txtSize")) if request("chkAutoIncrement") <> "" then sAutoIncrement = " checked" if request("chkAcceptNulls") <> "" then sAcceptNulls = " checked" if request("chkPrimaryKey") <> "" then sPrimaryKey = " checked" if request("chkIndexed") <> "" then sIndexed = " checked" if request("chkIndexUnique") <> "" then sIndexUnique = " checked" sSQL = "ALTER TABLE [" & sTableName & "] " if bNewField then 'We are adding a new field sSQL = sSQL & " ADD COLUMN [" & sNewName & "] " else 'We are editing a field sSQL = sSQL & " ALTER COLUMN [" & sName & "] " end if select case lType case adSmallInt : sFieldType = "SMALLINT" case adInteger : sFieldType = "INTEGER" case adBoolean : sFieldType = "BIT" case adDate : sFieldType = "DATE" case adCurrency : sFieldType = "CURRENCY" case adVarWChar : sFieldType = "VARCHAR" case adLongVarWChar : sFieldType = "TEXT" case adLongVarBinary : sFieldType = "LONGVARBINARY" case adGUID : sFieldType = "GUID" case adUnsignedTinyInt : sFieldType = "TINYINT" end select sSQL = sSQL & sFieldType if lType = adVarWChar then 'Set field size if lSize = "" then lSize = 50 sSQL = sSQL & "(" & lSize & ") " end if if sAcceptNulls <> "" then sSQL = sSQL & " NULL " else sSQL = sSQL & " NOT NULL " end if if sAutoIncrement <> "" then sSQL = sSQL & " IDENTITY " end if if sPrimaryKey <> "" then 'If the field has a primary key index sSQL = sSQL & " CONSTRAINT [pk" & sNewName & "] PRIMARY KEY " else 'If the field is indexed (not PK) if sIndexed <> "" then if sIndexUnique <> "" then sSQL = sSQL & " CONSTRAINT [idx" & sNewName & "] UNIQUE" else 'Index is no constraint, so another sql stat required sSQL2 = "CREATE INDEX [idx" & sNewName & "] ON [" & sTableName & "] ([" & sNewName & "]) " end if end if end if if (sName <> "") and (sNewName <> sName) then 'Rename field 'sSQL = "ALTER TABLE [" & sTableName & "] ALTER COLUMN RENAME [" & sName & "] TO [" & sNewName & "]" sErr = "Cannot rename field." bErr = True end if if bErr = True then conn.close set rs = nothing set conn = nothing ShowFieldForm else ' response.write sSQL conn.execute sSQL if sSQL2 <> "" then conn.execute sSQL2 end if conn.close set rs = nothing set conn = nothing if err <> 0 then %><% response.write err.description & "
" & sSQL else response.redirect "te_tableedit.asp?cid=" & lConnID & "&tablename=" & server.urlencode(request("tablename")) end if end if else if sName <> "" then 'We are editing a field 'Get the attributes for the field OpenRS arrConn(lConnID) set AdoX = server.createobject("adox.catalog") ' set fld = server.createobject("adox.column") AdoX.ActiveConnection = conn set fld = AdoX.Tables(sTableName).Columns(sName) lType = fld.Type lSize = fld.DefinedSize if (fld.attributes AND adFldIsNullable) = adFldIsNullable then sAcceptNulls = " checked" end if 'Enumerate the indices to check that if this field has a primary key'd index set rs = conn.openSchema(adSchemaIndexes) do while not rs.eof if rs("table_name") = sTableName then if (rs("column_name") = fld.Name) then sIndexed = " checked" if (rs("unIque") = True) then sIndexUnique = " checked" end if if (rs("prImary_key") = True) then sPrimaryKey = " checked" end if end if end if rs.movenext loop if arrType(lConnID) <> tedbAccess then if request.querystring("autoinc") = "1" then sAutoIncrement = " checked disabled" else sAutoIncrement = " disabled" end if sNote = "* Cannot change Auto Increment fields." else if fld.properties("Autoincrement") = True then sAutoIncrement = " checked" end if end if end if 'Show form for a new field definition if bNewField then sErr = "Add new field." else sErr = "Edit field definition." end if ShowFieldForm end if %>