using ASP and parametized query

Discussion in 'ASP General' started by shank, Aug 9, 2004.

  1. shank

    shank Guest

    Per a previous suggestion, I'm trying to use a parametized query in Access
    2002. The query functions properly in Access. Now I'm trying to call it from
    ASP. I'm using code I found at
    http://www.xefteri.com/articles/apr302002/default.aspx and trying to adjust
    for my needs. I'm getting this error. The query is there and functioning. It
    appears that I'm not connecting. Can I get some more insight?
    thanks!
    ------------------------------
    Microsoft VBScript runtime (0x800A01A8)
    Object required: 'qry_FindSongs 'White'

    ------------------------------
    My connection string is
    ------------------------------
    Dim conn, mdb, mdw, MM_GenKAccess_STRING

    set conn = CreateObject("ADODB.Connection")
    conn.Mode=adModeRead
    mdb = Server.MapPath("songs.mdb")
    mdw = Server.MapPath("system.mdw")

    MM_GenKAccess_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & mdb & ";" & _
    "Jet OLEDB:Database Password=xyz;" & _
    "Jet OLEDB:System database=" & mdw

    conn.open MM_GenKAccess_STRING
    ------------------------------
    <% OPTION EXPLICIT %>
    <%
    Dim T
    T = "%"
    If (Request("title") <> "") Then
    T = Request("title")
    End If
    %>
    <%
    Dim A
    A = "%"
    If (Request("artist") <> "") Then
    A = Request("artist")
    End If
    %>
    <%
    Dim C
    C = "%"
    If (Request("category") <> "") Then
    C = Request("category")
    End If
    %>
    <%
    Dim TY
    TY = "%"
    If (Request("type") <> "") Then
    TY = Request("type")
    End If
    %>
    <%
    Dim M
    M = "%"
    If (Request("manuf") <> "") Then
    M = Request("manuf")
    End If
    %>
    <%
    Dim SA
    SA = "%"
    If (Request("singleartist") <> "") Then
    SA = Request("singleartist")
    End If
    %>
    <%
    Dim rsResults
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    rsResults.ActiveConnection = MM_GenKAccess_STRING
    rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '" & TY &
    "', '" & M & "', '" & SA & "'"
    rsResults.Open rsResults, 0, 4
    %>
    shank, Aug 9, 2004
    #1
    1. Advertising

  2. shank wrote:
    > Set rsResults = Server.CreateObject("ADODB.Recordset")
    > rsResults.ActiveConnection = MM_GenKAccess_STRING
    > rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
    > & TY & "', '" & M & "', '" & SA & "'"
    > rsResults.Open rsResults, 0, 4
    > %>


    Do this instead:

    dim cn
    Set cn=server.createobject("adodb.connection")
    cn.open MM_GenKAccess_STRING
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    cn.qry_FindSongs T,A,C,TY,M,SA, rsResults

    See? No delimiters to worry about.

    Bob Barrows

    PS. You don't seem to have provided a user name for your connection. Are you
    sure you have workgroup security on your database?

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Aug 10, 2004
    #2
    1. Advertising

  3. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > shank wrote:
    > > Set rsResults = Server.CreateObject("ADODB.Recordset")
    > > rsResults.ActiveConnection = MM_GenKAccess_STRING
    > > rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
    > > & TY & "', '" & M & "', '" & SA & "'"
    > > rsResults.Open rsResults, 0, 4
    > > %>

    >
    > Do this instead:
    >
    > dim cn
    > Set cn=server.createobject("adodb.connection")
    > cn.open MM_GenKAccess_STRING
    > Set rsResults = Server.CreateObject("ADODB.Recordset")
    > cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    >
    > See? No delimiters to worry about.
    >
    > Bob Barrows
    >
    > PS. You don't seem to have provided a user name for your connection. Are

    you
    > sure you have workgroup security on your database?

    ----------------------------------------------
    I'm on a local station and the connection string works for all other pages.
    I connected with your string, but got no results, where I should be. To be
    clear, this string: "cn.qry_FindSongs T,A,C,TY,M,SA, rsResults" passes the
    values of the variables T,A,C,TY,M,SA into the query qry_FindSongs and
    returns a recordset rsResults... correct? I'm not getting a return on
    rsResults.

    I get this error:
    Response object, ASP 0185 (0x8002000E)
    A default property was not found for the object.

    The variables are being filled....
    T: water
    A: %
    C: %
    TY: %
    M: %
    SA: %

    <%
    dim cn, rsResults
    Set cn=Server.CreateObject("ADODB.connection")
    cn.open MM_GenKAccess_STRING
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    %>
    shank, Aug 10, 2004
    #3
  4. shank wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> shank wrote:
    >>> Set rsResults = Server.CreateObject("ADODB.Recordset")
    >>> rsResults.ActiveConnection = MM_GenKAccess_STRING
    >>> rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
    >>> & TY & "', '" & M & "', '" & SA & "'"
    >>> rsResults.Open rsResults, 0, 4
    >>> %>

    >>
    >> Do this instead:
    >>
    >> dim cn
    >> Set cn=server.createobject("adodb.connection")
    >> cn.open MM_GenKAccess_STRING
    >> Set rsResults = Server.CreateObject("ADODB.Recordset")
    >> cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    >>
    >> See? No delimiters to worry about.
    >>
    >> Bob Barrows
    >>
    >> PS. You don't seem to have provided a user name for your connection.
    >> Are you sure you have workgroup security on your database?

    > ----------------------------------------------
    > I'm on a local station


    I'm not sure what you mean by "local station"

    > and the connection string works for all other
    > pages. I connected with your string, but got no results, where I
    > should be. To be clear, this string: "


    It's not a string, it's a call to a method.

    cn.qry_FindSongs T,A,C,TY,M,SA, rsResults

    > " passes the values of the variables T,A,C,TY,M,SA into the
    > query qry_FindSongs and returns a recordset rsResults... correct?


    Correct. it always works for me.

    > I'm not getting a return on rsResults.
    >
    > I get this error:
    > Response object, ASP 0185 (0x8002000E)
    > A default property was not found for the object.


    Does the error message point to this line?

    Check to verify that your connection is open

    <%
    dim cn, rsResults
    Set cn=Server.CreateObject("ADODB.connection")
    cn.open MM_GenKAccess_STRING
    if cn.State = 1 then
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    else
    Response.Write "Connection is not open<BR>"
    end if
    %>

    Your connection string still looks wierd to me. When you open the database
    in Access, do you have to supply both a user name and a password? If not,
    you are not using workgroup security and you don't need that business about
    a system database in your connection string. If you don't have to enter a
    password, then your database is not password-protected and the only thing
    needed in your connection string is:

    MM_GenKAccess_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & mdb

    If none of these suggestions help, I will need to look at it first-hand.
    Would it be possible to send your database to my email address? Or, you can
    extract the relevant objects into another database and remove any sensitive
    data if that allows you to send it.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Aug 10, 2004
    #4
  5. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:Oxks7%...
    > shank wrote:
    > > "Bob Barrows [MVP]" <> wrote in message
    > > news:...
    > >> shank wrote:
    > >>> Set rsResults = Server.CreateObject("ADODB.Recordset")
    > >>> rsResults.ActiveConnection = MM_GenKAccess_STRING
    > >>> rsResults = "qry_FindSongs '" & T & "', '" & A & "', '" & C & "', '"
    > >>> & TY & "', '" & M & "', '" & SA & "'"
    > >>> rsResults.Open rsResults, 0, 4
    > >>> %>
    > >>
    > >> Do this instead:
    > >>
    > >> dim cn
    > >> Set cn=server.createobject("adodb.connection")
    > >> cn.open MM_GenKAccess_STRING
    > >> Set rsResults = Server.CreateObject("ADODB.Recordset")
    > >> cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    > >>
    > >> See? No delimiters to worry about.
    > >>
    > >> Bob Barrows
    > >>
    > >> PS. You don't seem to have provided a user name for your connection.
    > >> Are you sure you have workgroup security on your database?

    > > ----------------------------------------------
    > > I'm on a local station

    >
    > I'm not sure what you mean by "local station"
    >
    > > and the connection string works for all other
    > > pages. I connected with your string, but got no results, where I
    > > should be. To be clear, this string: "

    >
    > It's not a string, it's a call to a method.
    >
    > cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    >
    > > " passes the values of the variables T,A,C,TY,M,SA into the
    > > query qry_FindSongs and returns a recordset rsResults... correct?

    >
    > Correct. it always works for me.
    >
    > > I'm not getting a return on rsResults.
    > >
    > > I get this error:
    > > Response object, ASP 0185 (0x8002000E)
    > > A default property was not found for the object.

    >
    > Does the error message point to this line?
    >
    > Check to verify that your connection is open
    >
    > <%
    > dim cn, rsResults
    > Set cn=Server.CreateObject("ADODB.connection")
    > cn.open MM_GenKAccess_STRING
    > if cn.State = 1 then
    > Set rsResults = Server.CreateObject("ADODB.Recordset")
    > cn.qry_FindSongs T,A,C,TY,M,SA, rsResults
    > else
    > Response.Write "Connection is not open<BR>"
    > end if
    > %>
    >
    > Your connection string still looks wierd to me. When you open the database
    > in Access, do you have to supply both a user name and a password? If not,
    > you are not using workgroup security and you don't need that business

    about
    > a system database in your connection string. If you don't have to enter a
    > password, then your database is not password-protected and the only thing
    > needed in your connection string is:
    >
    > MM_GenKAccess_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    > "Data Source=" & mdb
    >
    > If none of these suggestions help, I will need to look at it first-hand.
    > Would it be possible to send your database to my email address? Or, you

    can
    > extract the relevant objects into another database and remove any

    sensitive
    > data if that allows you to send it.
    >
    > Bob Barrows

    ---------------------------------------------------
    I have narrowed the problem down. The connection is open. I made this small
    test code and it works. I created a query in the Access DB that does not
    require a variable. And it works....

    <%
    'this code works and there is no variables
    if conn.State = 1 then
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    conn.qry_SelectCat, rsResults
    Response.Write "Connection is open<BR>"
    else
    Response.Write "Connection is not open<BR>"
    end if
    %>

    .... Then I created a query that requires 1 simple variable and I get this
    error...
    "Either BOF or EOF is True, or the current record has been deleted.
    Requested operation requires a current record" which suggests to me that the
    variables are not getting passed to the query. How can I troubleshoot
    further? Thanks!

    <%
    'I introduce 1 variable and it appears the variable is not getting passed to
    the query
    C = "fav"
    if conn.State = 1 then
    Set rsResults = Server.CreateObject("ADODB.Recordset")
    conn.qry_SelectCategories C, rsResults
    Response.Write "Connection is open<BR>"
    else
    Response.Write "Connection is not open<BR>"
    end if
    %>
    shank, Aug 10, 2004
    #5
  6. shank wrote:

    > this error...
    > "Either BOF or EOF is True, or the current record has been deleted.
    > Requested operation requires a current record" which suggests to me
    > that the variables are not getting passed to the query. How can I
    > troubleshoot further? Thanks!
    >
    > <%
    > 'I introduce 1 variable and it appears the variable is not getting
    > passed to the query
    > C = "fav"
    > if conn.State = 1 then
    > Set rsResults = Server.CreateObject("ADODB.Recordset")
    > conn.qry_SelectCategories C, rsResults
    > Response.Write "Connection is open<BR>"
    > else
    > Response.Write "Connection is not open<BR>"
    > end if
    > %>

    Can you show the SQL for the query that accepts the parameter? I suspect
    that the query itself is the problem.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Aug 10, 2004
    #6
  7. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > shank wrote:
    >
    > > this error...
    > > "Either BOF or EOF is True, or the current record has been deleted.
    > > Requested operation requires a current record" which suggests to me
    > > that the variables are not getting passed to the query. How can I
    > > troubleshoot further? Thanks!
    > >
    > > <%
    > > 'I introduce 1 variable and it appears the variable is not getting
    > > passed to the query
    > > C = "fav"
    > > if conn.State = 1 then
    > > Set rsResults = Server.CreateObject("ADODB.Recordset")
    > > conn.qry_SelectCategories C, rsResults
    > > Response.Write "Connection is open<BR>"
    > > else
    > > Response.Write "Connection is not open<BR>"
    > > end if
    > > %>

    > Can you show the SQL for the query that accepts the parameter? I suspect
    > that the query itself is the problem.

    -------------------
    I cut-n-pasted these from SQL view in Access.
    This query requests 1 variable...
    SELECT DISTINCT GenKStock.Category
    FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
    GenKTitles.ItemNumber
    WHERE (((GenKStock.Category) Like "*" & [C] & "*"))
    ORDER BY GenKStock.Category;

    This query has no variables...
    SELECT DISTINCT GenKStock.Category
    FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
    GenKTitles.ItemNumber
    ORDER BY GenKStock.Category;

    This is the original query that I need to work...
    SELECT GenKStock.OrderNo, GenKTitles.Title, GenKTitles.Artist,
    GenKStock.Category, GenKStock.Type, GenKStock.Manuf, GenKStock.SingleArtist,
    GenKTitles.mp3Files
    FROM GenKStock INNER JOIN GenKTitles ON GenKStock.OrderNo =
    GenKTitles.ItemNumber
    WHERE (((GenKTitles.Title) Like "*" & [T] & "*") AND ((GenKTitles.Artist)
    Like "*" & [A] & "*") AND ((GenKStock.Category) Like "*" & [C] & "*") AND
    ((GenKStock.Type) Like "*" & [TY] & "*") AND ((GenKStock.Manuf) Like "*" &
    [M] & "*") AND ((GenKStock.SingleArtist) Like "*" & [SA] & "*"));
    shank, Aug 10, 2004
    #7
  8. shank wrote:
    > WHERE (((GenKStock.Category) Like "*" & [C] & "*"))
    > ORDER BY GenKStock.Category;


    You have to change the * to % in order to call this query from ADO. It
    didn't make sense to me the first time I encountered this issue, either.

    Bob barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Aug 10, 2004
    #8
  9. shank

    shank Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > shank wrote:
    > > WHERE (((GenKStock.Category) Like "*" & [C] & "*"))
    > > ORDER BY GenKStock.Category;

    >
    > You have to change the * to % in order to call this query from ADO. It
    > didn't make sense to me the first time I encountered this issue, either.
    >


    Shazam! Very strange. The ASP gets a recordset, but when using the Access
    interface, '%' does not yield any results. Oh well I guess. Thanks very very
    much!!!!
    shank, Aug 11, 2004
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Eric Nelson
    Replies:
    5
    Views:
    1,504
    Alexey Smirnov
    Feb 4, 2009
  2. John
    Replies:
    13
    Views:
    534
  3. Guest
    Replies:
    10
    Views:
    280
    Guest
    Jul 8, 2005
  4. Roland Hall

    ASP LIKE query using parameterized query

    Roland Hall, Jan 12, 2007, in forum: ASP General
    Replies:
    8
    Views:
    206
    Roland Hall
    Jan 13, 2007
  5. CraftyTech

    parametized unittest

    CraftyTech, Jan 12, 2014, in forum: Python
    Replies:
    3
    Views:
    63
    CraftyTech
    Jan 12, 2014
Loading...

Share This Page