Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Mutiple Recordset with MS SQL

Posted on 2006-11-20
3
Medium Priority
?
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

610 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