Retrieving multiple result rows from foreign key tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Retrieving multiple result rows from foreign key tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 08:58:40

A. Mentzer writes "ASP and Query Analyzer window gives different results, and I can't get all rows of data from foreign key tables.
In a relational SQL Server database, a guy is described in tblPERSONEL. His six children are described in tblCHILDREN. TblCHILDREN has as a foreign key the value of the identity key in tblPERSONEL.
Using code like this, I get six result rows if the guy has six children. But only in Query Analyzer. The same type of code on my asp page returns only one row with the first child. I tried different joins all with the same result. No help there.
declare @parm_ssn SSN
select @parm_ssn = ('360-48-2337 ')
select p.persid, ssn, firstname, lastname,
d.dependentname
from tblPersonal p
RIGHT OUTER join tbldependents d
on p.persid = d.persid
where p.ssn = @parm_ssn
Seems like I need to force all six children into a one row result or get all three rows to return to my asp page. Do you have any ideas?"

Nazim
A custom title

1408 Posts

Posted - 2002-02-19 : 09:03:12

This query should do it. i would suggest you to create a stored procedure and call it using Asp. Apart from solving your problem it will also help in performance benefits

select p.persid, ssn, firstname, lastname,
d.dependentname
from tblPersonal p
left join tbldependents d
on p.persid = d.persid
where p.ssn = @parm_ssn

--------------------------------------------------------------

aalex
Starting Member

2 Posts

Posted - 2002-02-19 : 09:37:26

Still need some very savvy help. Thanks for the suggestion, but it’s not an answer for this problem. The problem is that I cannot get all the results in the ASP page. The SQL is in a stored procedure. THE PROBLEM IS: In the asp page, I only get the first row, no matter how many rows the sql returns in Query Analyzer.
(Your code returns multiple matching rows, the same as mine did. I do not think the problem is with the SQL code itself. )

Seems like I need to force all six children into a one row result or get all three rows to return to my asp page. Do you have any ideas?"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-19 : 09:41:11

Maybe you should post your ASP code. It doesn't sound like the problem is in SQL Server. Although I would make sure you have

SET NOCOUNT ON

as the first line in your stored procedure.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

izaltsman
A custom title

1139 Posts

Posted - 2002-02-19 : 10:45:04

Sounds to me like you didn't set up a loop to step through the records in your recordset (which means you only display the first record). Like graz said -- post your ASP code and we may be able to help...
A loop I am talking about would look something like:


Do Until rs.eof   'loop until the end of recordset
    Response.Write (rs.fields(1).value) & "<BR>"
    rs.MoveNext    'This is how you go to the next record
Loop

Edited by - izaltsman on 02/19/2002 10:47:48

aalex
Starting Member

2 Posts

Posted - 2002-02-20 : 06:16:31

THANKS! Savvy I asked for. Savvy I got. The solution was, as you said, to loop through all the rows of the result set . The test code is below. Now, I have another problem. This code seems to "consume" the recordset. I tried to do ors.movefirst to reset the counter but it would not allow. Here is my code for defining the recordset. Have I created a forward only resultset?

= = = = = code to create resultset and open the database etc.= = =
sp2call = "{call sp_test_mullitrow_sponly(?)}"
Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")
'-- Open ADO Connection
oConn.Open PER_CONNECT_SQL
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = sp2call
'--parameters for stored procedure
oCmd.Parameters.Append oCmd.CreateParameter("@PARM_SSN", adVARCHAR, adParamInput, 12 )
'-- Assign value to input parameter
oCmd("@parm_SSN") = parm_ssn
'-- Fire the Stored Proc and assign resulting recordset
Set oRs = oCmd.Execute

= = = =Code for looping through the recordset= = = = = = = = =
I had previously looped through fields using the following code and thought it got everything. But NOOOO. I got only all the fields for one row.

Response.Write "<BR><BR><B> RESULT SET FIELD/TYPE/VALUE </B><BR>"
n = 0 'want to start first row at index zero
For Each fld In ors.Fields
Response.Write n & "- - - - - - - - - " & "Name: " & fld.Name & " - - - - - - Type: " & fld.Type & "- - - - - - Value: " & fld.Value & "<BR>"
n = n + 1
Next

= = = solution to loop through all rows and fields in each = = =
I used your code as follows to see everything:

do until ors.eof
%> <table><tr><tr><%
for y = 0 to ors.fields.count -1 %>
<td><%=ors(y).value%></td>
<%next %>
</tr>
<% ors.movenext
loop
set ors = ors.nextrecordset
%></table><%

karuna
Aged Yak Warrior

582 Posts

Posted - 2002-02-20 : 08:42:06

quote:

'-- Fire the Stored Proc and assign resulting recordset
Set oRs = oCmd.Execute

The ADO creates the Recordset by itself,by default the cursor is forward only.

Hope this should work

sp2call = "{call sp_test_mullitrow_sponly(?)}"
Set oConn = Server.CreateObject("ADODB.Connection")
Set oCmd = Server.CreateObject("ADODB.Command")

Set oRs = Server.CreateObject("ADODB.Recordset")' Create Recordset Explicitly

'-- Open ADO Connection
oConn.Open PER_CONNECT_SQL
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = sp2call
'--parameters for stored procedure
oCmd.Parameters.Append oCmd.CreateParameter("@PARM_SSN", adVARCHAR, adParamInput, 12 )
'-- Assign value to input parameter
oCmd("@parm_SSN") = parm_ssn
'-- Fire the Stored Proc and assign resulting recordset

oRs.Open(oCmd,oConn,adOpenStatic)'Either define Static or Dynamic
based on your requirement

do until ors.eof
%> <table><tr><tr><%
for y = 0 to ors.fields.count -1 %>
<td><%=ors(y).value%></td>
<%next %>
</tr>
<% ors.movenext
loop

ors.movefirst

%></table>

I didnt tried the code,But I hope it should work.

Karunakaran

Don't wait for things to happen,Make them to happen...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources