Search Tools Links Login

View and Sort any SQL table from a single ASP page


This page allows you to view and sort all of your tables in an SQL database

Original Author: Tim Hancock

Assumptions

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.

Code

' 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=; Initial Catalog=; 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
%>










Table Name:

Sort By:



<% If TableName <> "" Then %>


Table Name: <%= TableName %>








<% strSQL = "SELECT name FROM syscolumns WHERE id = " & TableID & " ORDER BY colid"
Set adoRS = adoConn.Execute(strSQL)
Do While Not adoRS.EOF %>

<% adoRS.MoveNext
Loop
adoRS.Close %>


<% Set adoRS.ActiveConnection = adoConn
adoRS.Source = strTableSQL
adoRS.LockType = 3
adoRS.Open
Do While Not adoRS.EOF %>


<% For Each oField in adoRS.Fields %>

<% Next %>




<% adoRS.MoveNext
Loop
adoRS.Close %>

<%= adoRS("name") %>



<% End If %>

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 108 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.