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:
Post a Comment