|
|
| |
|
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
1) You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this code from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the code or code's description. |
'**************************************
' Name: View and Sort any SQL table from
' a single ASP page
' Description:This page allows you to vi
' ew and sort all of your tables in an SQL
' database
' By: Tim Hancock
'
' Assumes:You will only need to add the
' server name and database name to the con
' nection string to adapt it to work on yo
' ur server.
The value of 133575514 entered In the sysobjects SQL String is the id For the table dtproperties. This table is a system table and you will need To substitute the value for the relating id found in the sysobjects table in your database.
'
'This code is copyrighted and has ' limited warranties.Please see http://w
' ww.Planet-Source-Code.com/xq/ASP/txtCode
' Id.6238/lngWId.4/qx/vb/scripts/ShowCode.
' htm 'for details. '**************************************
' ADO variables and SQL strings
Dim adoConn, adoRS, strSQL, strTableSQL
' Create the Connection Object
Set adoConn = Server.CreateObject("ADODB.Connection")
' Open the Connection using the SQL OLE
' ODBC adoConn.Open "Provider=SQLOLEDB; Da
' ta Source=<Your Server Name>; Init
' ial Catalog=<Your Database Name>;
' User Id=sa; Password=;"
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.Recordset")
' Retrieve the table name from the submi
' tting form, When the form first loads, t
' here is no table name passed
TableName = Request.Form("Tables")
if TableName <> "" Then
' Store the value for later use if sorti
' ng the table
Response.Cookies("TableSort")("TableName") = TableName
else
' The form has been refreshed, grab the
' table name from the cookie
TableName = Request.Cookies("TableSort")("TableName")
End if
' Retrieve the sort preference
SortBy = Request.Form("SortBy")
' If this is not the first time the page
' has loaded
if TableName <> "" Then
' Retrieve the id from the sysobjects table
strSQL = "SELECT id FROM sysobjects WHERE name = '" & TableName & "'"
Set adoRS = adoConn.Execute(strSQL)
TableID = adoRS("id")
adoRS.Close
' Create the new SQL String To retrieve the columns
strTableSQL = "SELECT * FROM " & TableName
' if the form has refreshed With a sort by preference
if SortBy <> "" Then
strTableSQL = strTableSQL & " ORDER BY " & SortBy
End if
End if
%>
<HTML>
<BODY>
<!-- Create the Information Row and Sort By row of the table -->
<FORM name="SortTable" action="Tables.asp" target="_self" method="post">
<TABLE border="0">
<TR>
<TD><FONT face="verdana,arial,helvetica" size="1"><B>Table Name: </B></FONT>
<Select name="Tables">
<% strSQL = "SELECT name FROM sysobjects WHERE xtype = 'U' AND (Not (id) = 133575514) ORDER BY name"
Set adoRS = adoConn.Execute(strSQL)
Do While Not adoRS.EOF
if adoRS("name") = TableName Then %>
<OPTION selected value="<%= adoRS("name") %>"><%= adoRS("name") %>
<% Else %>
<OPTION value="<%= adoRS("name") %>"><%= adoRS("name") %>
<% End if %>
<% adoRS.MoveNext
Loop
adoRS.Close %>
</Select>
</TD>
<TD><FONT face="verdana,arial,helvetica" size="1"><B>Sort By:</B></FONT>
<Select name="SortBy"><OPTION value="">
<% if TableID <> "" Then
strSQL = "SELECT name FROM syscolumns WHERE id = " & TableID
Set adoRS = adoConn.Execute(strSQL)
Do While Not adoRS.EOF %>
<OPTION value="<%= adoRS("name") %>"><%= adoRS("name") %>
<% adoRS.MoveNext
Loop
adoRS.Close
End if %>
</Select>
</TD>
<TD><INPUT type="submit"></TD>
</TR>
</TABLE>
</FORM>
<% if TableName <> "" Then %>
<BR>
<FONT face="verdana,arial,helvetica" size="2"><B>Table Name: <%= TableName %></B>
<BR>
<BR>
<!-- Create the table itself -->
<TABLE>
<!-- The header row -->
<TR>
<% strSQL = "SELECT name FROM syscolumns WHERE id = " & TableID & " ORDER BY colid"
Set adoRS = adoConn.Execute(strSQL)
Do While Not adoRS.EOF %>
<TD><FONT face="verdana,arial,helvetica" size="1"><B><%= adoRS("name") %></B></TD>
<% adoRS.MoveNext
Loop
adoRS.Close %>
</TR>
<!-- The data -->
<% Set adoRS.ActiveConnection = adoConn
adoRS.Source = strTableSQL
adoRS.LockType = 3
adoRS.Open
Do While Not adoRS.EOF %>
<TR>
<TD>
<FORM name="TableInfo" method="post" action="UpdateTable.asp?TableName=<%= TableName %>" target="_self">
<TR>
<% For Each oField In adoRS.Fields %>
<TD><INPUT type="text" name="<%= oField.name %>" value="<%= oField.Value %>"></TD>
<% Next %>
</TR>
</FORM>
</TD>
</TR>
<% adoRS.MoveNext
Loop
adoRS.Close %>
</TABLE>
<% End if %>
</BODY>
</HTML> | |
Other 2 submission(s) by this author
|
|
| |
| Report Bad Submission |
|
| |
| Your Vote! |
|
See Voting Log |
| |
| Other User Comments |
6/27/2000 3:48:00 PM:Daz B
Not bad :)
No criticism whatsoever
but your code is a little redundant.
Nice commenting...
p.s whats up with
the form submitting to another page?
Shouldn't you include that page for ppl
when they get the code?
p.p.s what
program did you use to generate this
code?
|
6/28/2000 7:00:31 AM:Tim Hancock
Apologies for the lack of information
but I had to rush away into a meeting.
In response to your questions:
|
6/28/2000 7:02:01 AM:Tim Hancock
It looks like the rest of my response
was cut off! Here it is
again...
Apologies for the lack of
information but I had to rush away into
a meeting. In response to your
questions:
The form you see that
submits the information to the
UpdateTable.asp page is there as a
method of changing and updating the
information in that record. You can use
similar methods to determine which
table you are updating and thus only
require one single page to update all
tables as well!
I live in the dark age
when it comes to programming, I use
CuteHTML from Globalscape to create and
edit pages. It is only a text type
editor but it is a lot better than
notepad. I feel I learn a lot more by
doing the coding manually than by
inserting objects and controls and
never understanding how they
work!
Thanks for the comments. There
always welcome.
Tim Hancock.
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this code has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular code, please click here.
|
|
