|
View and Sort any SQL table from a single ASP page
|
Email
|
| Submitted on: |
6/23/2000 8:38:37 AM |
| By: |
Tim Hancock
|
| Level: |
Advanced |
| User Rating: |
By 4 Users |
| Compatibility: |
ASP (Active Server Pages) |
| Views: |
33492 |
|
(About the author) |
|
|
|
This page allows you to view and sort all of your tables in an SQL database
|
| |
|
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...
- 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.
- 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.
- You may link to this code from another website, but ONLY if it is not wrapped in a frame.
- 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 view 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 connection string to adapt it to work on your 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://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=6238&lngWId=4'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; Data Source=<Your Server Name>; Initial Catalog=<Your Database Name>; User Id=sa; Password=;"
' Create the Recordset Object
Set adoRS = Server.CreateObject("ADODB.Recordset")
' Retrieve the table name from the submitting form, When the form first loads, there is no table name passed
TableName = Request.Form("Tables")
If TableName <> "" Then
' Store the value for later use if sorting 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
|
| |
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?
(If this comment was disrespectful, please report it.)
|
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:
(If this comment was disrespectful, please report it.)
|
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.
(If this comment was disrespectful, please report it.)
|
6/16/2004 2:04:08 PM:
So where is the tables.asp or updatetables.asp ?
(If this comment was disrespectful, please report it.)
|
9/27/2004 8:56:09 PM:
I tried this script on a virgin install of MS SQL, because Access is just awful, and it just gives me endless errors.
I am trying to find examples of code to learn from, but I am getting tired of poorly documented, proprietary pieces posted as the "do-all, end-all" of scripts. Only to find out by the time I learn all the necessary tweaking involved to make it work on a STANDARD system, I could just as well have rewritten it myself.
It only frustrates those of us who would REALLY like to write good code, because of the numerous examples to the contrary out there.
If you aren't going to support it:
Test it, document it, THEN post it.
(If this comment was disrespectful, please report it.)
|
9/28/2004 3:34:47 AM: Tim Hancock
Perhaps you'd like to read the instructions at the top before posting.
I just copied the code into a web page called Tables.asp myself, added the database connection (as per the instructions) and bingo! It works.
If you want some help, post the error message.
(If this comment was disrespectful, please report it.)
|
Add Your Feedback
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
To post feedback, first please login.
|
