Jeff Uchtman wrote:
Query in ASP against a MS SQL DB.
<%
Dim Sql
Dim Conn
Dim totalCount
Dim rsCount
Dim rsACount
Dim PetACount
Dim rsBCount
Dim PetBCount
Dim rsCCount
Dim PetCCount
Dim rsDCount
Dim PetDCount
Set rsCount = Server.CreateOb ject("ADODB.Rec ordset")
Sql = "SELECT ID from Verified"
rsCount.Open SQL, Conn, 1, 3
<snip>
Yes there is definitely a better way than making 5 trips to the database to
open very expensive cursors:
1. Normalize your database. You are having a hard time here because you have
4 PetX columns. Instead, you should break out the Pet information into
another table called Pets. The table will have 3 columns: ID, PetType,
PetData (I'm not sure what the 1's represent. You will probably want to
replace "PetData" with a more descriptive name). This will cause you to
rearrange this data:
ID PetA PetB PetC PetD
A 1 1
B 1 1 1
to look like this:
Verified
ID
A
B
Pets
ID PetType PetData
A A 1
A C 1
B B 1
B C 1
B D 1
If for some reason, you don't have control over this database design, then,
instead of creating a new table, you should create a view that normalizes
the table. This view will "fold" your table, normalizing it:
CREATE VIEW Pets AS
SELECT ID, 'A' PetType,PetA PetData
FROM Verified
WHERE PetA is not null
Union All
SELECT ID, 'B', PetB FROM Verified
WHERE PetB is not null
Union All
SELECT ID, 'C', PetC FROM Verified
WHERE PetC is not null
Union All
SELECT ID, 'D', PetD FROM Verified
WHERE PetD is not null
Either of these options (the new table, or the new view) will allow you to
get the total count from Verified by:
SELECT Count(*) as [Total] FROM Verified
and the pet counts in a single query by:
SELECT PetType, Count(*) As PetTotal
FROM Pets
WHERE PetData = '1'
GROUP BY PetType
2. Use a stored procedure. Use Query Analyzer to run this script in your
database to create the stored procedure.
CREATE PROCEDURE GetPetCounts
AS
SELECT PetType, Count(*) As PetTotal
FROM Pets
WHERE PetData = '1'
GROUP BY PetType
UNION ALL
SELECT 'Total', Count(*) FROM Verified
Then test the stored procedure, again using Query Analyzer, to make sure it
returns the results you want. Based on the above data, this procedure should
return the following resultset:
PetType Total
A 1
B 1
C 2
D 1
Total 2
In ASP, you can call this stored procedure very efficiently by this vbscript
code:
dim ardata, rs, conn, i, curCount
Dim totalCount,PetA Count,PetBCount ,PetCCount,PetD Count
'create and open the connection, then:
Set rs = Server.CreateOb ject("ADODB.Rec ordset")
conn.GetPetCoun ts rs
if not rs.eof then ardata=rs.GetRo ws
rs.close: set rs=nothing
conn.close: set conn=nothing
if isarray(ardata) then
for i = 0 to ubound(ardata,2 )
curCount = ardata(1,i)
select case ardata(0,i)
case "A": PetACount=curCo unt
case "B": PetBCount=curCo unt
case "C": PetCCount=curCo unt
case "D": PetDCount=curCo unt
case "Total": PetACount=total Count
end select
next
else
response.write "no counts were returned"
end
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
