Recordset Problem / asp, vbscript, Ms Access

[Dilruba]

I want to create a Recordset and after that start a loop and within that loop i am creating more other recordset.

But Recordset within the loop is not working properly i.e. they are not getting expected value.
.

For example

<%

Dim Recordset1a 

dateRa = date()

Set Recordset1a = Server.CreateObject("ADODB.Recordset")
Recordset1a.ActiveConnection = srtring_conneting

Recordset1a.Source = "SELECT *  FROM book  WHERE name "

Recordset1a.CursorType = 0
Recordset1a.CursorLocation = 2
Recordset1a.LockType = 1
Recordset1a.Open()

%>

<% 
  
       While ( NOT Recordset1a.EOF ) 
%>

<%

Dim RecordsetSubj, daySubj,subj

daySubj = date()

RecordsetSubj = ""

subj = ""


subj = Recordset1a.Fields.Item("subject1").Value

Set RecordsetSubj = Server.CreateObject("ADODB.Recordset")
RecordsetSubj.ActiveConnection = srtring_conneting

RecordsetSubj.Source = "SELECT * FROM table WHERE ( (( type = 200 )  AND (subject LIKE '%" + Replace(subj, 

"'", "''") + "%') AND (put_date LIKE '%" + Replace(daySubj, "'", "''") + "%')) ORDER BY put_date desc"


RecordsetSubj.CursorType = 0
RecordsetSubj.CursorLocation = 2
RecordsetSubj.LockType = 1
RecordsetSubj.Open()

%>

<%
Dim RecordsetSubj1, daySubj1,subj1

RecordsetSubj1 = ""
subj1 = ""

daySubj1 = date()


subj1 = Recordset1a.Fields.Item("subject2").Value

Set RecordsetSubj1 = Server.CreateObject("ADODB.Recordset")
RecordsetSubj1.ActiveConnection = srtring_conneting

RecordsetSubj1.Source = "SELECT * FROM table WHERE ( (( type = 100 ) AND (put_date LIKE '%" + Replace

(daySubj1, "'", "''") + "%'))  ) ORDER BY put_date desc"

RecordsetSubj1.CursorType = 0
RecordsetSubj1.CursorLocation = 2
RecordsetSubj1.LockType = 1
RecordsetSubj1.Open()

'Recordset1_numRows = 0
%>

<% 
  Repeat1__numRowsa = Repeat1__numRowsa-1
  Recordset1a.MoveNext()
Wend
%>

Here RecordsetSubj, RecordsetSubj1 is not getting the expected value.

Is it wrong to create recordset with a loop as well as taking the dynamic value i.e. from database.

Please help me

[ilearneditonline]

I would split out the inner queries and write a couple of functions. Then I would call the functions from the loop.

[Dilruba]

Thanks for ur guideline and suggestion.

But my question was "Is it wrong to create recordset with a loop as well as taking the dynamic value i.e. from database?"

[jhardman]

Thanks for ur guideline and suggestion.

But my question was "Is it wrong to create recordset with a loop as well as taking the dynamic value i.e. from database?"

Besides personal preferences, yes it is wrong to create a recordset inside the loop like you did. At least the "set recordset..." line should be outside of the loop, whether you re-fill the rs every time through the loop or not. After that, you could open and close the recordset with a new query every time you go through the loop and that would be valid.

Jared