Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Aug 2012
    Location
    Los Angeles, CA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering a Recordset with a QueryString

    Hello, I'm creating a set of 2 pages, http://ican4kids.org/ncfr_State_Teams.asp and http://ican4kids.org/ncfr_State_Team_Details.asp which are displaying information from an Access database on my webserver.

    State_Teams has several links that add a Query String to the end of the State_Team_Details page equal to the database field "stateteamId".

    I'm calling the following function in the body of State_Team_Details

    Code:
    Function MakeCountyDetails
    
    Dim adoCon
    Dim strSQL
    Dim rsStateTeams
    
    'Create ado object, create recordset, run the query, assign query and ado to recordset
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("_db/ican.mdb")
    Set rsStateTeams = Server.CreateObject("ADODB.Recordset")	
    strSQL = "SELECT * FROM stateteamsWithMembers"
    rsStateTeams.Open strSQL, adoCon
    rsStateTeams.Filter = "stateteamId = "request.querystring(""stateteamID"") ""
    
    End Function
    with the intention of creating a new SQLQuery that is filtered by the QueryString in the URL, however i'm getting the following error

    Error Type:
    Microsoft VBScript compilation (0x800A0401)
    Expected end of statement
    /ncfr_State_Team_Details.asp, line 19, column 38
    Line 19 is

    rsStateTeams.Filter = "stateteamId = "request.querystring(""stateteamID"") ""

    and I assume I'm just effing up the syntax, but there may be more fundamental problems I'm missing. Help please?
    Last edited by DigitalSocrates; 08-17-2012 at 10:52 PM. Reason: added Query String Parameter

  • #2
    New to the CF scene
    Join Date
    Aug 2012
    Location
    Los Angeles, CA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured out the correct syntax:

    Code:
    rsStateTeams.Filter = "stateteamId = '" + request.querystring("stateteamID") + "'"
    but now I'm trying to figure out why
    Code:
    Response.Write rsStateTeams("stateteamID") + " " + rsStateTeams("stateteamState")
    comes back with the error

    Microsoft VBScript runtime (0x800A000D)
    Type mismatch: 'rsStateTeams(...)'


    Edit: realized i left the table out

    Code:
    Response.Write rsStateTeams("stateteams.stateteamID") + " " + rsStateTeams("stateteams.stateteamState")
    New error:

    Error Type:
    ADODB.Recordset (0x800A0CC1)
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    Last edited by DigitalSocrates; 08-17-2012 at 11:50 PM. Reason: different error

  • #3
    New to the CF scene
    Join Date
    Aug 2012
    Location
    Los Angeles, CA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tried to cleanup the code and combine the filter into the original Query:

    Code:
    Function MakeCountyDetails
    
    Dim adoCon
    Dim strSQL
    Dim rsStateTeams
    
    'Create ado object, create recordset, run the query, assign query and ado to recordset
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("_db/ican.mdb")
    Set rsStateTeams = Server.CreateObject("ADODB.Recordset")	
    strSQL = "SELECT * FROM stateteamsWithMembers WHERE stateteamId = '" + request.querystring("stateteamID") + "'"
    rsStateTeams.Open strSQL, adoCon
    
    Response.Write request.querystring("stateteamID")
    
    Response.Write rsStateTeams("stateteams.stateteamID") + " " + rsStateTeams("stateteams.stateteamState")
    
    End Function
    which came up with this error

    Microsoft JET Database Engine (0x80040E07)
    Data type mismatch in criteria expression.
    /ncfr_State_Team_Details.asp, line 16
    Line 16 is

    rsStateTeams.Open strSQL, adoCon

    which up till now had given me no problems. Maybe strSQL could handle the query fine, but the data can't be opened by rsStateTeams?

  • #4
    New to the CF scene
    Join Date
    Aug 2012
    Location
    Los Angeles, CA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    alright, I figured out that the text I changed it to was converting the querystring into a string, which mismatches with the autonumber I was trying to compare it to.

    After a few more tweaks i'm now looking at:

    Code:
    Function MakeCountyDetails
    
    Dim adoCon
    Dim strSQL
    Dim rsStateTeams
    
    'Create ado object, create recordset, run the query, assign query and ado to recordset
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("_db/ican.mdb")
    Set rsStateTeams = Server.CreateObject("ADODB.Recordset")	
    strSQL = "SELECT * FROM stateteamsWithMembers WHERE stateteams.stateteamId = " + request.querystring(""stateteamID"") + ""
    rsStateTeams.Open strSQL, adoCon
    
    Response.Write request.querystring("stateteamID")
    
    Response.Write rsStateTeams("stateteams.stateteamID") + " " + rsStateTeams("stateteams.stateteamState")
    
    End Function
    with the error

    Error Type:
    Microsoft VBScript compilation (0x800A03EE)
    Expected ')'
    /ncfr_State_Team_Details.asp, line 15, column 103
    I have no idea where a ')' is supposed to go.

    Line 15 is still the same line:

    Code:
    stateteams.stateteamId = " + request.querystring(""stateteamID"") + ""

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    In any case, you should "sanitize" the querystring. People can and do purposely pass bogus stuff in query stings trying to either break your site of steal info from your database.

    On top of that, you have improperly code this as a FUNCTION. A FUNCTION should always return a value; yours does not. So it should be a SUB instead.

    Your query is completely bogus: You are mentioning only ONE table in your FROM clause: stateteamsWithMembers. And yet, in your where clause, you attempt to specify a field from a *different* table: stateteams. Make up your mind. Which table are you going to use?

    So, a *GUESS* at the answer:
    Code:
    Sub MakeCountyDetails
        Dim adoCon
        Dim strSQL
        Dim rsStateTeams
        Dim id
    
        ' "sanitize" the query string value to avoid problems:
        id = Request.QueryString("stateteamid")
        If Not IsNumeric( id ) Then
            Response.Write "Invalid state team id"
            Response.End ' or use Exit Sub if you want the rest of the page to still show up
        End If
        id = CDBL( id ) ' just to be doubly sure
    
        'Create ado object, create recordset, run the query, assign query and ado to recordset
        Set adoCon = Server.CreateObject("ADODB.Connection")
        adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("_db/ican.mdb")
    
        ' I am *guessing* that you want to use the table name shown here after the FROM
        ' If not, use the correct table name
        strSQL = "SELECT * FROM stateteamsWithMembers WHERE stateteamId = " & id
        Set rsStateTeams.Open adoCon.Execute( strSQL ) ' more efficient than your cose
    
        Response.Write rsStateTeams("stateteamID") & " " & rsStateTeams("stateteamState")
       
        ' not strictly needed but a good idea:
        rsStateTeams.close
        adoCon.close
    
    End Sub
    *** HOWEVER ***

    If you will be doing any other DB queries on this page, then you really ought to create the connection to your DB *OUTSIDE* of any FUNCTION or SUB, probably as the first thing on the page, and then only close it as you exit the page.
    Last edited by Old Pedant; 08-18-2012 at 01:08 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    By the way, the reason for that last error in this line:
    Code:
        stateteams.stateteamId = " + request.querystring(""stateteamID"") + ""
    is that you are totall misusing the "" there.

    What is happening is that VBS sees that code as being okay up until here:
    Code:
        stateteams.stateteamId = " + request.querystring(""
    But then it has no idea what you mean by this:
    Code:
        stateteamID"") + ""
    It thinks you need a right parenthesis, thus:
    Code:
        stateteams.stateteamId = " + request.querystring("")
    before it can decide what to do with the rest of the (very wrong) code.

    Finally, for what it's worth: It is bad practice to use + to combine string in VBScript. It usually works, but there are some oddball circumstances where it really will try to do ADDITION instead of string concatenation. If you use the & operator (as you can see I did), then there is never any confusion, as & can only mean "concatenate these strings".
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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