Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version
Go to top

Online DB Administration

, 17 Mar 2003
Online administration of your database. Create or modify tables, keys, and indexes. Edit or insert data.
<%@language=javascript%>
<!--#include file=common.asp-->
<!--#include file=b.grid.asp-->
<%
	function Content(Conn) {
		var oCat = Server.CreateObject("ADOX.Catalog");
		oCat.ActiveConnection = Conn;

		if(Request.Form("createkey").Count==1) {
			var sql = "";
			var sSource = "", sDest = "";
			if(IsJet(Conn))
				sql = 'alter table [' + Request.QueryString("table").Item + ']';
			else
				sql = 'alter table "' + Request.QueryString("table").Item + '"';
			
			var nCol = 0;
			do {
				var sTmp = GetFormValue("column"+nCol,"");
				if(sTmp=="") break;
				if(sSource!="") sSource += ",";
				if(IsJet(Conn))
					sSource += '[' + sTmp + ']';
				else
					sSource += '"' + sTmp + '"';
				if(sDest!="") sDest += ",";
				if(IsJet(Conn))
					sDest += '[' + GetFormValue("relatedcolumn"+nCol,"") + ']';
				else 
					sDest += '"' + GetFormValue("relatedcolumn"+nCol,"") + '"'; 
				nCol++;
			} while(true);
			
			
			if(GetFormValue("type",-1)==adKeyForeign) {
				if(IsJet(Conn))
					sql += ' add constraint [' + GetFormValue("name","") + ']';
				else
					sql += ' add constraint "' + GetFormValue("name","") + '"';
				sql += " foreign key(" + sSource + ")";
				if(IsJet(Conn))
					sql += ' references [' + GetFormValue("relatedtable",null) + ']';
				else
					sql += ' references "' + GetFormValue("relatedtable",null) + '"';
				sql += "(" + sDest + ")";
			} else if(GetFormValue("type",-1)==adKeyPrimary) {
				if(IsJet(Conn))
					sql += ' add constraint [' + GetFormValue("name","") + ']';
				else
					sql += ' add constraint "' + GetFormValue("name","") + '"';
				sql += " primary key(" + sSource + ")";
			} else if(GetFormValue("type",-1)==adKeyUnique) {
				if(IsJet(Conn))
					sql += ' add constraint [' + GetFormValue("name","") + ']';
				else
					sql += ' add constraint "' + GetFormValue("name","") + '"';
				sql += " unique(" + sSource + ")";
			}

			switch(GetFormValue("deleterule",adRINone)-0) {
			case adRICascade:
				sql += " on delete cascade";
				break;
			case adRISetNull:
				sql += " on delete set null";
				break;
			case adRISetDefault:
				sql += " on delete set default";
				break;
			}
			switch(GetFormValue("updaterule",adRINone)-0) {
			case adRICascade:
				sql += " on update cascade";
				break;
			case adRISetNull:
				sql += " on update set null";
				break;
			case adRISetDefault:
				sql += " on update set default";
				break;
			}

			try {
				Conn.Execute(sql);
				EndPage("keys.asp?table="+Request.QueryString("table").Item);
			}
			catch(e) {
				Out('\n<s'+'cript type="text/javascript">\nSetErrorMessage("' + e.description + '");\n</scr'+'ipt>\n');
			}
		}

		Out('<table class="list" cellspacing="0" cellpadding="0">');

		var sName = GetFormValue("name","");
		Out('<tr><th colspan="3">' + Request.QueryString('table').Item + '</th></tr>');
		Out("<tr class=gridrow><td>Name:</td>");
		Out("<td><input type=edit value=\"" + sName + "\" name=\"name\"></td><td>&nbsp;</td></tr>");

		var nTmp = GetFormValue("type",-1);
		Out("<tr class=gridrow><td>Type:</td>");
		Out("<td><select name=\"type\" onchange=\"this.form.submit()\">");
		Out("<option "+(nTmp==adKeyPrimary?"selected ":"")+"value=1>Primary</option>");
		Out("<option "+(nTmp==adKeyForeign?"selected ":"")+"value=2>Foreign</option>");
		Out("<option "+(nTmp==adKeyUnique?"selected ":"")+"value=3>Unique</option>");
		Out("</select></td><td>&nbsp;</td></tr>");

		var sRelatedTable = "";
		if(nTmp==adKeyForeign) {	
			Out("<tr class=gridrow><td>Related Table:</td>");
			Out("<td><select name=\"relatedtable\" onchange=\"this.form.submit()\">");
			sRelatedTable = GetFormValue("relatedtable","");
			for(var e=new Enumerator(oCat.Tables);!e.atEnd();e.moveNext()) {
				if(e.item().Type!="TABLE") continue;
				Out("<option ");
				if(e.item().Name==sRelatedTable) Out("selected ");
				Out("value=\"" + e.item().Name + "\">" + e.item().Name + "</option>");
			}
			Out("</select></td><td>&nbsp;</td></tr>");
		} else {
			Out("<tr class=gridrow><td>Related Table:</td>");
			Out("<td><select disabled name=\"relatedtable\">");
			Out("<option selected>&lt;Not Needed&gt;</option>");
			Out("</select></td><td>&nbsp;</td></tr>");
		}
		
		var nTmp = GetFormValue("deleterule",-1);
		if(GetFormValue("type",-1)==adKeyForeign) {	
			Out("<tr class=gridrow><td>Delete Rule:</td>");
			Out("<td><select name=\"deleterule\">");
			Out("<option "+(nTmp==adRINone?"selected ":"")+"value=0>None</option>");
			Out("<option "+(nTmp==adRICascade?"selected ":"")+"value=1>Cascade</option>");
			Out("<option "+(nTmp==adRISetNull?"selected ":"")+"value=2>SetNull</option>");
			Out("<option "+(nTmp==adRISetDefault?"selected ":"")+"value=3>SetDefault</option>");
			Out("</select></td><td>&nbsp;</td></tr>");
		} else {
			Out("<tr class=gridrow><td>Delete Rule:</td>");
			Out("<td><select disabled name=\"deleterule\">");
			Out("<option selected>&lt;Not Needed&gt;</option>");
			Out("</select></td><td>&nbsp;</td></tr>");
		}

		if(GetFormValue("type",-1)==adKeyForeign) {	
			var nTmp = GetFormValue("updaterule",-1);
			Out("<tr class=gridrow><td>Update Rule:</td>");
			Out("<td><select name=\"updaterule\">");
			Out("<option "+(nTmp==adRINone?"selected ":"")+"value=0>None</option>");
			Out("<option "+(nTmp==adRICascade?"selected ":"")+"value=1>Cascade</option>");
			Out("<option "+(nTmp==adRISetNull?"selected ":"")+"value=2>SetNull</option>");
			Out("<option "+(nTmp==adRISetDefault?"selected ":"")+"value=3>SetDefault</option>");
			Out("</select></td><td>&nbsp;</td></tr>");
		} else {
			Out("<tr class=gridrow><td>Update Rule:</td>");
			Out("<td><select disabled name=\"updaterule\">");
			Out("<option selected>&lt;Not Needed&gt;</option>");
			Out("</select></td><td>&nbsp;</td></tr>");
		}

		var bTitle = false;
		var nColumn = 0;
		do {
			Out("<tr class=gridrow><td>");
			if(!bTitle) {
				bTitle = true;
				Out("Columns:");
			} else {
				Out("&nbsp;");
			}
			Out("</td><td>");
			sTmp = GetFormValue("column"+nColumn,"");
			Out("<select name=\"column"+nColumn+"\" onchange=\"this.form.submit()\">");
			Out("<option value=\"\">&lt;select column&gt;</option>");
			for(var e=new Enumerator(oCat.Tables(Request.QueryString("table").Item).Columns);!e.atEnd();e.moveNext()) {
				Out("<option ");
				if(e.item().Name==sTmp) Out("selected ");
				Out("value=\"" + e.item().Name + "\">" + e.item().Name + "</option>");
			}
			Out("</select>");
			if(sRelatedTable!="" && sTmp!="") {
				Out("</td><td>");
				var sTmp2 = GetFormValue("relatedcolumn"+nColumn,"");
				Out("<select name=\"relatedcolumn"+nColumn+"\" onchange=\"this.form.submit()\">");
				Out("<option value=\"\">&lt;select column&gt;</option>");
				for(var e=new Enumerator(oCat.Tables(sRelatedTable).Columns);!e.atEnd();e.moveNext()) {
					Out("<option ");
					if(e.item().Name==sTmp2) Out("selected ");
					Out("value=\"" + e.item().Name + "\">" + e.item().Name + "</option>");
				}
				Out("</select>");
			} else Out("</td><td>&nbsp;");
			Out("</td></tr>");
			nColumn++;
			if(sTmp=="") break;
		} while(true);

		Out("<tr><td colspan=3>");
		Out("<input type=submit name=\"createkey\" value=\"Create a new key\"/>");
		Out("</td></tr>");

		Out("</table>");

		oCat = null;	

		Out('<br>');
		Out('<table class="list" cellspacing="0" cellpadding="0">\n');
		Out('<tr>\n');
  		Out('<td>&nbsp;<a href="columns.asp?table='+Request.QueryString('table').Item+'">Back</a>&nbsp;</td>\n');
  		Out('</table>\n');
	}

	PageSetup();
	NewPageHeader();
	Content(Object.Conn);
	NewPageFooter();
	PageCleanup();
%>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Bjornar Henden
Web Developer
Norway Norway
No Biography provided

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 18 Mar 2003
Article Copyright 2002 by Bjornar Henden
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid