Disconnected recordsets to a Session in ASP Classic

Posted on 2007-10-11
Last Modified: 2012-06-27
I have a search page, that when a search term is entered, it posts back to itself and makes the call to my database and returns the results of the search.

I would like to be able to display a list of the recent searches in a display table. I am trying to use a disconnected recordset, but since the page posts back to itself, everyime I run a search it rebuilds the disconnected recordset (drs)  and only displays the most recent search.

I tried passing the drs to a Session using the following:

Dim drs
Set drs = Session("drs")
drs("search") = Request.form("search")

Set Session("drs") = drs

NOW, the error I'm getting says: "Object required: 'session(...)'", pointing to the second line of code.
(Microsoft VBScript runtime (0x800A01A8)

Initially, when it was resetting itself I had to declare the drs by:
drs.Fields.APPEND "search"

If someone could help get this in and out of the Session I'd appreciate it.

The end result is when the searches are displayed, I'm going to uses a standard do while loop to make each search  in the drs appear on a seperate row so I can use href to make them links.
Question by:Alemus
    LVL 9

    Expert Comment

    Usually when setting session variables, you do:

    Session("MyVar") = XYZ

    I don't think you use SET.
    LVL 9

    Expert Comment

    However, it's not generally a good practice to set session variables that could get large, and a recordset is certainly a candidate for something that could get large.

    Why can't you use 2 subroutines:  one that goes and gets the search results and one that returns the recent search list?  If you are posting the page every time anyway, you might as well return the results, rather than carrying them around in a session.  Plus, every time the search is executed, the recent searches should change in some way to reflect the latest search, right?  So, that means your session variable would change every time someone searches, so it's not really helpful to even use the session variable.
    LVL 2

    Author Comment

    The problem I ran into was in how I originally built the recordset.

    I was using a disconnected recordset.

    SET drs = Server.CreateObject("ADODB.Recordset")
    drs.Fields.APPEND "Searches",129,64
    drs("Searches") = request.form("searches")

    every time a new search was entered, this was rebuilding the drs and only showing just the one search they just entered. It may be I was doing something wrong, but that method wasn't holding a history of past searches.
    LVL 9

    Expert Comment

    The way I do updates is as follows:

    set drs = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM Table WHERE 1=2" strSQL, objConnection, 1, 3, 1
    drs("field1") = field1
    drs("field2") = field2

    I would suggest using a separate block of code to update the recordset from that which you use to retrieve the search history.  The way you have it now, drs only ever has 1 record because you open an empty recordset and add a single record to it.
    LVL 2

    Author Comment

    I think I've done a bad job of explaining what I'm trying to accomplish.
    When a user performs a search say "cat".
    The page posts back to itself as follows mypage.asp?variable=cat.

    A connection to the database is opend all results of cat are displayed.

    second search "dog"  mypage.asp?variable=dog

    connection to DB opened and results displayed.

    What I'm trying to do is save just the search terms themselves as a list

    Recent searches

    The list itself needs to be linkable so the user could click and redo a search without having to type in again.
    It's possible, the solution you provided will do what I'm trying to accomplish and I'm just not seeing it because I still need coffee :)
    The problem is there isn't a table anywhere, that is storing just the search terms themselves, which is why I was trying to load them into a disconnected recordset and pass them to a session. Without the session, everytime the user enters a new search, the disconnected recordset gets rebuilt and shows only the most recent search
    LVL 9

    Accepted Solution

    ok, i think i see what's going on.  so, there are 2 ways i would suggest approaching this.

    first, if you have the access to create a new table in the database, just populate it with recent searches, and use that to build your search links.  i sort of assume you can't go this route, but just in case, i thought i would mention it.

    otherwise, what you could do is keep the search terms in a session variable, rather than trying to put an entire recordset object into the session.

    so if the user searches for "cat", you do this:

    session("recentSearches") = "cat"

    then, the user searches for "dog", and you do this:

    if len(trim(session("recentSearches"))) > 0 then
      session("recentSearches") = "dog," & session("recentSearches")
      session("recentSearches") = "dog"
    end if

    NOTE:  i'm assuming you will use a variable here, instead of "dog", but for consistency, i'm using the dog, cat example.

    then, to build your links, you do this:

    arrRecentSearches = Split(session("recentSearches"), ",")

    then, loop through your array, and write out the links.  you'd then need to build a new recordset and re-query the database, but i think this is much more feasible than trying to keep an object in the session.  actually, i'm not 100% sure you can even store an object in the session.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now