Tuesday, June 13, 2006

Display Report from Access using ASP

Here is a post I made on webdeveloper.com asking for some help with ASP, and some guy named Russell posted an amazing instant working code:

Hey there, I am really new to ASP and SQL
My boss gave me the task to do the following:
We have a huge database with a ton of records that specify:
Location, Specialty, Number, and other things. He wants me to create a report that displays

Location1 SpecialtyA Average Number for this specialty
----------SpecialtyB Average Number for this specialty
----------SpecialtyC Average Number for this specialty
Location2 SpecialtyA Average Number for this specialty
----------SpecialtyB Average Number for this specialty
----------SpecialtyC Average Number for this specialty
and so on...

So far I created a page in dreamweaver filled with a huge table with a ton of cells. After connecting the database, what do I type to have it spit out the average number? Something like Response.Write ? Thanks!
Edit/Delete Message


Here is his amazing response code:

<%
main

Sub main()
Dim ar

ar = getData()

If isArray(ar) Then
showReport ar
Else
Response.Write "No Data"
End If
End Sub

Sub showReport(ar)
Dim i

With Response
.Write "<table>" & vbCrLf
.Write "<tr>" & vbCrLf
.Write "<td>Location</td>" & vbCrLf
.Write "<td>Specialty</td>" & vbCrLf
.Write "<td>Avg</td>" & vbCrLf
.Write "</tr>" & vbCrLf

For i = 0 To Ubound(ar, 2)
.Write "<tr>" & vbCrLf
.Write "<td>" & ar(0, i) & "</td>" '' location
.Write "<td>" & ar(1, i) & "</td>" '' specialty
.Write "<td>" & ar(2, i) & "</td>" '' avg
.Write "</tr>" & vbCrLf
Next
.Write "<table>" & vbCrLf
End With
End Sub

Function getData()
Dim cmd
Dim sql
Dim rs

sql = "SELECT location, specialty, AVG(number_for_this_specialty) " &_
"FROM myTable " &_
"GROUP BY location, specialty " &_
"ORDER BY location, specialty"

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = YOUR_CONNECTION_STRING_HERE
.CommandType = 1 '' adCmdText
.CommandText = sql

rs.Open .Execute
End With

If Not rs.EOF Then
getData = rs.GetRows
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
End Function
%>

No comments: