recordset filters

Results 1 to 4 of 4

Thread: recordset filters

  1. #1
    Join Date
    Dec 1969
    Posts
    50

    Default recordset filters

    I need to set a new recordset equal to a filter on another recordset and then loop through this new recordset. This is happening while I&#039m looping through the first recordset so I have nested loops. I haven&#039t been able to get it to work. Help...!

  2. #2
    Stephan Zutterman Guest

    Default RE: recordset filters

    Perhaps ...<BR><BR>&#060;%<BR>Set conn = &#039... connection to your database ...<BR>Set rs = server.createobject("ADODB.Recordset")<BR>Set ps = server.createobject("ADODB.Recordset")<BR>sql = "Select * from table1"<BR>rs.open sql, conn, 3, 3<BR>do while not rs.eof<BR> sql = "Select * from table2 where field2 = &#039" & rs("MatchField") & "&#039"<BR> ps.open sql, conn, 3, 3<BR> do while not ps.eof<BR> &#039statements on 2nd recordset<BR> ps.movenext<BR> loop<BR> ps.close<BR> rs.movenext<BR>loop<BR>rs.close<BR>set rs = nothing<BR>set ps = nothing<BR>set conn = nothing<BR>%&#062;

  3. #3
    Join Date
    Dec 1969
    Posts
    50

    Default RE: recordset filters

    Thanks for the quick response. I am trying to avoid multiple calls to the database so I was hoping to set the second recordset equal to a filter on the first recordset rather than opening another sql query. Can I do that? What would be the syntax?

  4. #4
    Stephan Zutterman Guest

    Default RE: recordset filters

    The SQL command you are looking for is INNER JOIN ... <BR><BR>&#060;%<BR>Set Conn = &#039 Connection string ..<BR>Set rs = server.createobject("ADODB.Recordset")<BR>sql = "Select * from Table1 inner join Table2 on Table1.MatchField = Table2.Matchfield order by Table1.Matchfield"<BR>rs.open sql, conn, 3, 3<BR>Matcher = ""<BR>Do while not rs.eof<BR> If Matcher &#060;&#062; rs("MatchField") then<BR> Matcher = rs("MatchField")<BR> &#039 Statements where new section of Matchfields begin<BR> End if<BR> &#039Other statements on recordset<BR> rs.movenext<BR>Loop<BR>rs.close<BR>conn.close<BR>s et rs = nothing<BR>set conn = nothing<BR>%&#062;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •