Professional Web Applications Themes

Access Query to SQL - ASP Database

The text below is the SQL statement used in Access to generate a valid query SQL = "SELECT tSNOBsTeamSelections.TeamName, tSNOBsTeamSelections.Player, PlayerPerformances.Total FROM tSNOBsTeamSelections LEFT JOIN PlayerPerformances ON tSNOBsTeamSelections.Player = PlayerPerformances.Player GROUP BY tSNOBsTeamSelections.TeamName, tSNOBsTeamSelections.Player, PlayerPerformances.Total HAVING tSNOBsTeamSelections.TeamName=" & Choice & " ORDER BY PlayerPerformances.Total DESC" However when I use this as the SQLstring to open a ASP recordset I get an error: Microsoft OLE DB Provider for ODBC Drivers (0x80040E10) [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. /ASPSNOBs/TeamListing.asp, line 10 If I remove the 'Having' clause the ASP works, but obviously the ASP page does not display the ...

  1. #1

    Default Access Query to SQL

    The text below is the SQL statement used in Access to generate a valid query

    SQL = "SELECT tSNOBsTeamSelections.TeamName, tSNOBsTeamSelections.Player,
    PlayerPerformances.Total FROM tSNOBsTeamSelections LEFT JOIN
    PlayerPerformances ON tSNOBsTeamSelections.Player =
    PlayerPerformances.Player GROUP BY tSNOBsTeamSelections.TeamName,
    tSNOBsTeamSelections.Player, PlayerPerformances.Total HAVING
    tSNOBsTeamSelections.TeamName=" & Choice & " ORDER BY
    PlayerPerformances.Total DESC"

    However when I use this as the SQLstring to open a ASP recordset I get an
    error:

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    /ASPSNOBs/TeamListing.asp, line 10

    If I remove the 'Having' clause the ASP works, but obviously the ASP page
    does not display the correct data.

    Help anyone.


    Paul Guest

  2. #2

    Default Re: Access Query to SQL

    Chances are that your "Choice" variable has no value. To find out, do:

    SQL = "SELECT tSNOBsTeamSelections.TeamName, tSNOBsTeamSelections.Player,
    PlayerPerformances.Total FROM tSNOBsTeamSelections LEFT JOIN
    PlayerPerformances ON tSNOBsTeamSelections.Player =
    PlayerPerformances.Player GROUP BY tSNOBsTeamSelections.TeamName,
    tSNOBsTeamSelections.Player, PlayerPerformances.Total HAVING
    tSNOBsTeamSelections.TeamName=" & Choice & " ORDER BY
    PlayerPerformances.Total DESC"

    RESPONSE.WRITE SQL
    RESPONSE.END

    Also, you should start using an OLEDB connection instead of ODBC.
    http://www.aspfaq.com/show.asp?id=2126

    Ray at work

    "Paul Smith" <me.uk> wrote in message
    news:4149998f$0$52653$plus.net... 


    Ray Guest

  3. #3

    Default Re: Access Query to SQL

    Please show us the Response.Write SQL version of this statement (which will
    actually be the sql being sent to the database. It may show you what the
    problem is.

    You aren't aggregating TeamName, so it would probably be a better performing
    query if you put the TeamName filter into a WHERE clause so the filtering
    would occur before the grouping.

    Bob Barrows
    Paul Smith wrote: 

    --
    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 Guest

  4. #4

    Default Re: Access Query to SQL

    The page produced by your Response.Write SQL :

    SELECT tSNOBsTeamSelections.TeamName, tSNOBsTeamSelections.Player,
    PlayerPerformances.Total FROM tSNOBsTeamSelections LEFT JOIN
    PlayerPerformances ON tSNOBsTeamSelections.Player =
    PlayerPerformances.Player GROUP BY tSNOBsTeamSelections.TeamName,
    tSNOBsTeamSelections.Player, PlayerPerformances.Total HAVING
    tSNOBsTeamSelections.TeamName=Cowboys ORDER BY PlayerPerformances.Total DESC

    The Cowboys is the parameter which is being passed to the ASP page when it
    is called.

    I am not sure whether it should in in quotes or not, but either way I still
    get the same error as originally quoted.

    I hope the additional info abouve helps you understand my issue. I am not
    good at SQL but have tracked the problem to the 'having' statement.



    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    message news:O46c6T$phx.gbl... 
    >
    >[/ref]


    Paul Guest

  5. #5

    Default Re: Access Query to SQL

    Bob,

    My SQL is not that good, I use the graphical Access interface to produce my
    SQL strings, so any help you can give me rewriting the SQL as you suggest
    would I think solve my issue. SQL seems not to like 'Having' but works with
    'Where'.

    Many thanks.



    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:O1hL7U$phx.gbl... 
    >
    > --
    > 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.
    >
    >[/ref]


    Paul Guest

  6. #6

    Default Re: Access Query to SQL


    "Paul Smith" <me.uk> wrote in message
    news:4149a0d8$0$71686$plus.net... 

    Try it. 'Cowboy' instead of Cowboy.

    Ray at work


    Ray Guest

  7. #7

    Default Re: Access Query to SQL

    Ray,

    Thank you very much - I have been trying all sorts of things for the past
    two hours trying to understand this.

    Many thanks.


    "Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
    message news:e5ypHl$phx.gbl... 
    >
    > Try it. 'Cowboy' instead of Cowboy.
    >
    > Ray at work
    >[/ref]


    Paul Guest

  8. #8

    Default Re: Access Query to SQL

    As Ray mentioned, the problem with your query was the lack of quotes around
    the value.

    Using the query builder, if you switch to SQL View after creating a query
    that works, you will see what the sql needs to look like when you build the
    statement in vbscript.

    A better technique is to use saved parameter queries instead of dynamic sql
    (building a string containing a sql statement and executing it). If you use
    Google to search for posts by me containing the words "saved parameter
    query" you will get a lot of information on this, especially if you sort by
    date in descending order.

    Bob Barrows

    Paul Smith wrote: 
    >>
    >> --
    >> 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.[/ref][/ref]

    --
    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 Guest

  9. #9

    Default Re: Access Query to SQL


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:O7$8Au$phx.gbl... 

    [:


    Ray Guest

  10. #10

    Default Re: Access Query to SQL

    Think of it along the same lines as:

    Dim a, b, c, d

    a = cat
    b = "cat"

    Response.Write "The value of a is " & a & "<br>"
    Response.WRite "The value of b is " & b

    Ray at work



    "Paul Smith" <me.uk> wrote in message
    news:4149a4fb$0$95739$plus.net... 
    >>
    >> Try it. 'Cowboy' instead of Cowboy.
    >>
    >> Ray at work
    >>[/ref]
    >
    >[/ref]


    Ray Guest

  11. #11

    Default Re: Access Query to SQL

    Bob,

    Do you know how many times the search you suggest brings up a reply from you
    suggesting a Goggle search on "saved parameter query"?

    Lots.... and despite looking I could not actually find any information on
    what this actually means.

    Could you either post a decent reference to some material to read how to use
    Access parameter queries using ASP or write some that you could reference,
    rather than constantly quoting this Goggle search - which I saw one comment
    from you admitting you had never actually done it yourself.

    I feel that I would like to use "saved parameter queries", but do not know
    how and cannot find out how to do so.




    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:O7$8Au$phx.gbl... [/ref]
    >
    > --
    > 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.
    >
    >[/ref]


    Paul Guest

  12. #12

    Default Re: Access Query to SQL

    Paul Smith wrote: 

    That's why I said to sort by date in descending order. 

    It's not very well doented anywhere that I could see. Let me see if I can
    dig up some of my posts about it...
    I think this one is the best:
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.ph x.gbl

    But, here's a couple more:
    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP1 1.phx.gbl

    http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gb l&oe=UTF-8&output=gplain

    There's really not a lot to it.

     
    That's certainly not true. I use them all the time. You must have misread
    what I was talking about.

    --
    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 Guest

  13. #13

    Default Re: Access Query to SQL

    > That's why I said to sort by date in descending order.

    The problem is that most of the people here are here precisely because they
    don't know how to use Google. :-)

    I would do an advanced search with whatever parameters you need to make your
    relevant posts appear in the top few, and then paste that URL rather than
    suggest the type of search. We know how well people follow instructions,
    and how willing they are to learn to fish, but clicking on a link is rarely
    a problem...

    An idea Steve Kass had is to place a GUID into specific posts he makes. If
    you do that, you can make the post, then search for the GUID on Google, and
    save the URL to the thread associated with it. Then he can just post the
    URL or tell them to search for that GUID (though this can lead to the same
    issue).

    A


    Aaron Guest

Similar Threads

  1. Access Query
    By dave in forum Coldfusion Database Access
    Replies: 5
    Last Post: February 21st, 08:20 PM
  2. MS Access query in MX 7
    By Eric Hung in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 19th, 03:31 AM
  3. Access query help
    By Brandon Taylor in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 12th, 09:52 PM
  4. Access Query in ASP
    By Ambassador in forum ASP Database
    Replies: 2
    Last Post: March 2nd, 06:08 AM
  5. MS access SQL query help!
    By Ralph Sharett in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 28th, 12:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139