Solved

Mutiple Recordset with MS SQL

Posted on 2006-11-20
3
276 Views
Last Modified: 2008-02-01
Greetings:

You can see the page I am creating at:
http://www.klinegroup.com/reports/reports_agpesticides_dynamic.asp

I have a header with sort categories for the records below. I only want to show the category if the recordset has records that match.

I do that by querying for records that match that category, but set ROWCOUNT to 1 so only 1 record is returned. I then test if the query returned a recordset with rows or not.

What I'd like to do is consolidate from repeating the process for each category into a single stored procedure, that my code than iterates through. Can anyone help provide a working solution for this?

Alternatives to my approach are welcome.

Here's the Stored Procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[rp_ind_region]
@ind_id integer
AS
SET ROWCOUNT 1;
SELECT rp_n_america
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_n_america = 1);

SET ROWCOUNT 1;
SELECT rp_latin
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_latin = 1);

SET ROWCOUNT 1;
SELECT rp_w_europe
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_w_europe = 1);

SET ROWCOUNT 1;
SELECT rp_e_europe
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_e_europe = 1);

SET ROWCOUNT 1;
SELECT rp_middle_east
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_middle_east = 1);

SET ROWCOUNT 1;
SELECT rp_asia
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_asia = 1);

Here's the Code:

Set cmd = Server.CreateObject("ADODB.Command")
                                    Set cmd.ActiveConnection = cn
                                    cmd.CommandText = "rp_ind_n_america"
                                  cmd.CommandType = adCmdStoredProc
                                    
                                    set p = cmd.CreateParameter("ind_id", adInteger, adParamInput)
                                    cmd.Parameters.Append p
                                    p.value = ind_id

'****************************
' The code below will be used for n_america, latin, e_europe, w_europe, middle_east, and asia once I can cycle through the recordsets created
'****************************
                                    set RS_n_america = cmd.execute
                                    if not RS_n_america.EOF Then
                                    %>
                                                <li class="vtxt darkblue underline linkblack"><a href="<%=currentpage%>?region=n_america">N. America</a>&nbsp;<font color="#BDBDBD">|</font>&nbsp;</li>
                                    <%
                                    end if
                                    Set RS_n_america = Nothing
                                    Set p = Nothing

What I'd like to do is call the above code for each recordset in the stored procedure. How can I do that?

Thanks!
0
Comment
Question by:bsowards
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17981327
you have 2 options:

§1: generate 1 single recordset, having 2 columns for example:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[rp_ind_region]
@ind_id integer
AS
SELECT TOP 1 'N_AMERICA' as item, rp_n_america  as value
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_n_america = 1)
UNION ALL
SELECT TOP 1 'RP_LATIN', rp_latin
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_latin = 1)
UNION ALL
SELECT TOP 1 'W_EUROPE', rp_w_europe
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_w_europe = 1)
UNION ALL
SELECT TOP 1 'E_EUROPE', rp_e_europe
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_e_europe = 1)
UNION ALL
SELECT TOP 1 'M_EAST', rp_middle_east
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_middle_east = 1)
UNION ALL
SELECT TOP 1 'ASIA', rp_asia
from report INNER JOIN category ON report.rp_category = category.ct_id
WHERE (category.ct_ind = @ind_id) AND (rp_asia = 1)

set RS = cmd.execute
while not RS.EOF Then
%>
 <li class="vtxt darkblue underline linkblack"><a href="<%=currentpage%>?region=<%=rs.fields("item").value%>"><%=rs.fields("item").value%></a>&nbsp;<font color="#BDBDBD">|</font>&nbsp;</li>
<%
  rs.movenext
wend
Set RS = Nothing
Set p = Nothing



§2: loop the generated recordsets:


set RS = cmd.execute
while not (RS is nothing )Then
  if  not (rs.eof and rs.bof ) then
%>
 <li class="vtxt darkblue underline linkblack"><a href="<%=currentpage%>?region=n_america">N. America</a>&nbsp;<font color="#BDBDBD">|</font>&nbsp;</li>
<%
end if
 set rs = rs.nextrecordset
end if
Set RS= Nothing
Set p = Nothing
0
 

Author Comment

by:bsowards
ID: 17981389
Hi,

Very close!

The second section isn't necessary.

For some reason it isn't showing the final category that applies. In this case it is E. Europe. Any explaination?
0
 

Author Comment

by:bsowards
ID: 17981409
Nevermind. Looks like it was just a missing record. The points are yours thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now