?
Solved

Mutiple Recordset with MS SQL

Posted on 2006-11-20
3
Medium Priority
?
296 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

777 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