Using recordset value in where caluse of select statement

Lakki · Oct 24, 2002

Hi,

Please find below my code. I am trying to achieve the following :
I am getting a set of records in a recordset object (oRSp).
I am using each of the recordset object value in "where clause of another select statement" to get one more recordset object (oRSv) . If I hard code the value in the where clause of second select statement, code works fine.

<HTML>
<HEAD>
<TITLE>Report</TITLE>
</HEAD><BODY>
<H1>Persons in Organization</h1>
<%
dim oRSv,conn,per,co
Set conn = Server.CreateObject("ADODB.Connection&quot

conn.open "DSN=sd4;userid=servicedesk;pwd=sd;"
set oRSv=server.CreateObject("ADODB.Recordset&quot

query = "select name_1 from v_organization;"
oRSv.open query, conn
oRSv.MoveFirst
Response.Write "<TABLE BORDER='1'>"
Do while NOT oRSv.EOF
Response.Write "<TR><TD>" & oRSv("name_1&quot

& "</TD></TR>"
dim temp
temp = "'" &oRSv("name_1&quot

&"';"
per = "select name from v_person where organization_name_1=" &temp
set oRSp=server.CreateObject("ADODB.Recordset&quot

oRSp.open per,conn
oRSp.MoveFirst
Do while NOT oRSp.EOF
Response.Write "<TR><TD>" & oRSp("name&quot

& "</TD></TR>"
oRSp.moveNext
Loop
oRSv.MoveNext
Loop
Response.Write "</TABLE>"
oRSv.close
set oRSv=nothing
%></BODY></HTML>

When I run this code, it displays the following error
Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/test.asp, line 22

Please suggest me where I am going wrong.

Any help on this would be really great..

Thanks in advance
Lakki

GabeC · Oct 24, 2002

After

Code:

oRSp.open per,conn[\code] you need to check and see that the recordset actually found data.

[code]
do while not oRSp.EOF
... code you have in the second Do While Loop
Loop
[\code]


It appears that no records are being returned for the value in the WHERE clause of the second SQL statement.

Hope this helps,

Thanks,

Gabe

Lakki · Oct 24, 2002

Yes I have checked it out. If I use Response.write with the temp variable, I am able to observe the expected value of it in the browser.

So it is not a problem with the empty recordset from the 1st select statement. u are right that oRSp recordset does not have any records. This is what surprising to me since the temp variable has a value in it. If I execute the same in sqlplus, I am finding records returned by it.

Thanks
Lakki

GabeC · Oct 25, 2002

After the line of code

Code:

per = "select name from v_person where organization_name_1=" &temp

perform a

Code:

response.write per & "<BR>"

This will print out your sql statement that is returning no records.

Post this sql statement along with the hard coded sql statement that returns data.

Maybe I can see something by looking at the working and non-working sql statements.

Between these two lines of code

Code:

Loop oRSv.MoveNext

You need to close the oRSp recordset and set it to nothing just like you do the oRSv recordset at the end of your code.

One more note. Move your

Code:

dim temp

line of code outside your loop. You should only declare this variable once instead of everytime you run through the loop.

Thanks,

Gabe

Lakki · Oct 25, 2002

Thank You.

You are right. One of the record in the recordset had null value. Hence I was getting this error.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Using recordset value in where caluse of select statement

Lakki

Programmer

GabeC

Programmer

Lakki

Programmer

GabeC

Programmer

Lakki

Programmer

Similar threads

Part and Inventory Search

Sponsor