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