ASP: A template that will show all data from a table (including header rows)
I talked about stored procedures here the other day. Today, I needed to create a page that would call one of those stored procedures (the selecting one) and display all data from a table. This code is very plug-and-play-ish - very little needs to be modified (that is, as long as you're using MS SQL Server). If all you need to do is a "screen dump" of everything in the table, this ASP takes care of everything for you… (it even uses an ASP version of the alternating color rows PHP script snippet I had posted awhile back here.
<%@ Language=VBScript %>
<%
option explicit
response.Buffer = True
dim cn
dim cmd
dim rs
%>
<!– #include virtual="/adovbs.inc" –>
<HTML>
<HEAD>
<TITLE>View All Data From Table EnterTableNameHere</TITLE>
<link href="/styles.css" rel="stylesheet" type="text/css">
<!–
in your css file - you need to define three classes:
pagetitle, header, data, and evenrow
see their usage below
–>
</HEAD>
<BODY>
<span class="pagetitle">View All Data From Table ENTERTABLENAMEHERE</span>
<%
set cn=server.CreateObject("ADODB.connection")
set cmd=server.CreateObject("ADODB.command")
cn.open "Provider=SQLOLEDB.1;Password=EnterPasswordHere;User ID=enterDBUserNameHere; initial Catalog=EnterDatabaseNameHere;Data Source=EnterSQLServerBoxName"
set cmd.ActiveConnection=cn
cmd.CommandText="enterSelectingStoredProcedureNameHere"
'this references my stored proecdure post here
set rs=cmd.Execute (,,adCmdStoredProc)
%>
<TABLE cellspacing="3" cellpadding="3">
<TR>
<%
'Display The Field Names
dim i
For i = 0 to rs.Fields.Count - 1 %>
<TD class="header" nowrap><%= rs(i).Name %></TD>
<% Next %>
</TR>
<%
'Display the data
'count variable declared below is used for the
'alternating color in the rows
dim count
count = 0
'php version of that alternating color row trick originally posted here
Do While Not rs.EOF
%>
<TR<% if count MOD 2 then %> class="evenrow"<% end if %>>
<%
dim y
For y = 0 to rs.Fields.Count - 1 %>
<TD VALIGN=TOP class="data" nowrap><%= rs(y) %></TD>
<% Next %>
</TR>
<%
rs.MoveNext
count = count + 1
Loop
%>
</TABLE>
<%
set rs=nothing
set cmd=nothing
set cn=nothing
%>
</BODY>
</HTML>
June 2nd, 2003 | Category: Script snippet
| Subscribe to comments | Both comments and pings are currently closed
June 3rd, 2003 at 3:25 am
The best ASP db editor around is here:
http://www.romankoch.ch/capslock/editor.htm
This is a universal table editor that, and has proven to be so usefull (at least to me). It's an excellent program (in asp) and totally free.
June 3rd, 2003 at 9:36 am
To be on the safe side, it's best to wrap your call to rs(y) with a Trim() like so: Trim(rs(y)).
I also recommend being explicit when you can …
Trim(rs.Fields(y).Value) is even better (for readability's sake at least).
You can make your rows sortable, too! A quick change is all you need to your recordset object:
Dim rs, obj, strSQL, strASCDESC, db_cnx, i
db_cnx = "Provider=SQLOLEDB.1;Password=EnterPasswordHere;User ID=enterDBUserNameHere; initial Catalog=EnterDatabaseNameHere;Data Source=EnterSQLServerBoxName"
Set obj = Server.CreateObject("adodb.connection")
Set rs = Server.CreateObject("adodb.recordset")
obj.Open db_cnx
strSQL = "SELECT state_id, state, state_abbr AS abbreviation FROM Coastal.dbo.State ORDER BY state"
rs.CursorLocation = 3
rs.Open strSQL, obj
If Trim(Request.QueryString("ascdesc")) = "ASC" Then
strASCDESC = "DESC"
Else
strASCDESC = "ASC"
End If
If Trim(Request.QueryString("sort")) > "" Then
rs.Sort = Trim(Request.QueryString("sort")) & " " & strASCDESC
End If
If Not rs.EOF Then
Response.Write "<tr>"
For i = 0 to rs.Fields.Count - 1
Response.Write "<a href=""?ascdesc=" & strASCDESC & "&sort=" & rs.Fields(i).Name & """>"
Response.Write rs.Fields(i).Name
Response.Write "</a></td>"
Next
Response.Write "</tr>"
End If
Then continue with the rest of the code as it is in the example Jennifer gives.
June 3rd, 2003 at 10:18 am
If you have access to your global.asa you should use Type Libraries instead of ADOVBS.inc
See http://www.asp101.com/articles/john/typelibs/default.asp for details.
June 3rd, 2003 at 4:17 pm
dvg - You wouldn't happen to know how to make that SQL statement so that it's called as a stored procedure? (I'm required to make all database calls from stored procedures)
June 4th, 2003 at 2:54 am
Yes, I do. I know everything. It's quite amazing, actually.
Sorry … I would have replied sooner, but I work all day. heh.
Do you have access to SQL Server's Enterprise Mangler? What platform are you using?
Send me an email.