Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Mutiple Recordset with MS SQL

Posted on 2006-11-20
3
Medium Priority
?
301 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 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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 have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
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 lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

564 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