02 Jun, 2003
ASP: A template that will show all data from a table (including header rows)
Posted by: Jennifer In: Script snippet
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>
scriptygoddess
