Solved

Mutiple Recordset with MS SQL

Posted on 2006-11-20
3
275 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
Comment Utility
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
Comment Utility
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
Comment Utility
Nevermind. Looks like it was just a missing record. The points are yours thanks!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

17 Experts available now in Live!

Get 1:1 Help Now