CODEWITHSUNDEEP
asp-classicrecordset
flavour404
flavour404

Reputation: 6312

classic asp query a recordset rather than a table

So, you have a recordset created like this:

Set rs = Server.CreateObject("ADODB.Recordset")

And you fill it like this:

rs.Open queryString, AuthConn, adOpenKeyset, adLockReadOnly

My question is, I want a second recordset that is a subset of the first (rs) recordset, can you do this in classic asp

Set rs2 = Server.CreateObject("ADODB.Recordset")

My immediate guess is that it would be something like this

rs2.Open queryString, rs, adOpenKeyset, adLockReadOnly

Why you ask? Well we have an older site that we are updating and adding new features too and rather than change a LOT of code I was wondering if I could be sneaky and use a setset of an already created (large) recordset, to save on another query to the db etc. Just wondering if it can be done.

Thanks,

Upvotes: 0

Views: 4781

Answers (3)

Cheran Shunmugavel
Cheran Shunmugavel

Reputation: 8459

You can use the Clone method to create a duplicate recordset, then use Filter to reduce the dataset to what you're interested in. For example:

Dim rs, rs2
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open queryString, AuthConn, adOpenKeyset, adLockReadOnly

Set rs2 = rs.Clone()
rs2.Filter = "Field1 = 'foo'"

The string form of Filter is basic; it's pretty much <Field> <op> <value>. You can combine multiple expressions using AND and OR, but even that has some limitations (see the documentation link for the full details).

For more complex filtering, you can pass the Filter property an array of Bookmark objects. In this case, you loop through the recordset (or a clone of the recordset), testing each record by whatever complex criteria you have. If the record passes the test, you save its Bookmark to an array or other collection. Then, you can set the Filter property to your array of Bookmarks and you have a custom-filtered recordset.

'Note that I haven't tested this code
Dim rs, rs2, bookmarks
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open queryString, AuthConn, adOpenKeyset, adLockReadOnly

Set rs2 = rs.Clone()
bookmarks = Array()
Do Until rs2.EOF
    If rs2("Field1") = 2 * rs2("Field2") Then
        ReDim Preserve bookmarks(UBound(bookmarks) + 1)
        bookmarks(UBound(bookmarks)) = rs2.Bookmark
    End If
    rs2.MoveNext
Loop

rs2.Filter = bookmarks
' Now rs2 contains only records where Field1 = 2*Field2

You can use this same technique to get unique values (aka DISTINCT) by using a Dictionary object to store the unique key values. Doing a DISTINCT on multiple fields is a bit trickier. What I've done is the past is to combine the multiple fields using a separator that won't be in the data (such as a pipe |). That's not always possible, though.

Upvotes: 3

John
John

Reputation: 4638

Obviously you can create a second query and recordset and run it over and over again inside "do while not rs.eof............loop" - I'm guessing that's what you want to avoid.

You might want to take a look at Datashaping. There's an article about it here. It's old, but then this is Classic ASP

https://web.archive.org/web/20210513001641/https://www.4guysfromrolla.com/webtech/092599-1.shtml

There also used to be a page on MSDN called "Using Data Shape to create hierarchical recordsets". You'll find loads of links to it on Google but they all take you to a 404 page on a resource for .net developers now :(

Upvotes: 0

skv
skv

Reputation: 1803

The recordset object can be opened only by a "connection" object, you cannot replace the connection object with another recordset object as shown above, however if you modify the querystring object and open the desired subset through your own query you can achieve this.

Please post querystring if you have difficulty making the subset.

Upvotes: 0

Related Questions