SELECT Query in ASP

Discussion in 'ASP General' started by Nano, Dec 3, 2007.

  1. Nano

    Nano Guest

    I have created ASP file from MS Access. It has the following Code. But
    it gives an error at:

    rs.Open sql, conn, 3, 3

    The Error is:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
    1.
    /MyWeb/ERP-IR Distribution.asp, line 23


    This error only comes when I write query that includes WHERE clause,
    simple SELECT query runs without any error.

    Kinldy help, below mention is the code.


    <%
    If IsObject(Session("ERP_IR_Distribution_rs")) Then
    Set rs = Session("ERP_IR_Distribution_rs")
    Else
    sql = "SELECT [ERP-IR].ID, [ERP-IR].Department, [ERP-IR].[Logged
    on], [ERP-IR].Issue, [ERP-IR].Code, [ERP-IR].Status, [ERP-IR].[Action
    Taken], [ERP-IR].[Recommendations/ Comments], [ERP-IR].[Action By],
    [ERP-IR].Due, [ERP-IR].[Closed on], [ERP-IR].[Key User], [ERP-IR].[KU
    Comments] FROM [ERP-IR] WHERE ((([ERP-
    IR].Department)=""Distribution"")) "
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    If rs.eof Then
    rs.AddNew
    End If
    Set Session("ERP_IR_Distribution_rs") = rs
    End If
    %>


    Regards,
    Hasnain Raja
     
    Nano, Dec 3, 2007
    #1
    1. Advertising

  2. Nano wrote:
    > I have created ASP file from MS Access. It has the following Code. But
    > it gives an error at:
    >
    > rs.Open sql, conn, 3, 3
    >
    > The Error is:
    >
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
    > 1.
    > /MyWeb/ERP-IR Distribution.asp, line 23

    >
    > This error only comes when I write query that includes WHERE clause,
    > simple SELECT query runs without any error.
    >
    > Kinldy help, below mention is the code.
    >
    >
    > <%
    > If IsObject(Session("ERP_IR_Distribution_rs")) Then
    > Set rs = Session("ERP_IR_Distribution_rs")
    > Else
    > sql = "SELECT [ERP-IR].ID, [ERP-IR].Department, [ERP-IR].[Logged
    > on], [ERP-IR].Issue, [ERP-IR].Code, [ERP-IR].Status, [ERP-IR].[Action
    > Taken], [ERP-IR].[Recommendations/ Comments], [ERP-IR].[Action By],
    > [ERP-IR].Due, [ERP-IR].[Closed on], [ERP-IR].[Key User], [ERP-IR].[KU
    > Comments] FROM [ERP-IR] WHERE ((([ERP-
    > IR].Department)=""Distribution"")) "
    > Set rs = Server.CreateObject("ADODB.Recordset")
    > rs.Open sql, conn, 3, 3
    > If rs.eof Then
    > rs.AddNew
    > End If
    > Set Session("ERP_IR_Distribution_rs") = rs
    > End If
    > %>

    >
    > Regards,
    > Hasnain Raja


    Before opening the recordset, writhe the contents of sql to Response so you
    can see the actual query being sent to Jet to be executed. You cannot debug
    a sql statement without knowing what it is. Typically, looking at the
    statement in the browser window will make the error obvious. If not, copy
    the statement from the browser window, open your database in Access, create
    a new query in design view, switch to SQL View and paste the statement. Try
    running it. If you've built it correctly, it should run without
    modification. If not, you may get a more helpful error message. If you're
    still stuck, show us the sql statement.

    Looking at your code, I don't see any problems that stick out, so I will
    wait to see the results of your "Response.Write sql" statement.

    Oh, and this:
    Set Session("ERP_IR_Distribution_rs") = rs

    is a horrible idea. Do not save ADO objects in session or application:
    http://classicasp.aspfaq.com/components/should-i-store-objects-in-session/application-scope.html

    You might want to consider turning that sql statement into a saved query
    once you get it running in Access. Perhaps save it as "DistributionIR".
    Doing so will allow you to run it as simply as:

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

    Here are some posts I've made about using parameterized saved queries:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    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], Dec 3, 2007
    #2
    1. Advertising

  3. Nano

    Nano Guest

    On Dec 3, 9:20 pm, "McKirahan" <> wrote:
    > "Nano" <> wrote in message
    >
    > news:...
    >
    >
    >
    >
    >
    > > I have created ASP file from MS Access. It has the following Code. But
    > > it gives an error at:

    >
    > > rs.Open sql, conn, 3, 3

    >
    > > The Error is:

    >
    > > Error Type:
    > > Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    > > [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
    > > 1.
    > > /MyWeb/ERP-IR Distribution.asp, line 23

    >
    > > This error only comes when I write query that includes WHERE clause,
    > > simple SELECT query runs without any error.

    >
    > > Kinldy help, below mention is the code.

    >
    > >
    > > <%
    > > If IsObject(Session("ERP_IR_Distribution_rs")) Then
    > > Set rs = Session("ERP_IR_Distribution_rs")
    > > Else
    > > sql = "SELECT [ERP-IR].ID, [ERP-IR].Department, [ERP-IR].[Logged
    > > on], [ERP-IR].Issue, [ERP-IR].Code, [ERP-IR].Status, [ERP-IR].[Action
    > > Taken], [ERP-IR].[Recommendations/ Comments], [ERP-IR].[Action By],
    > > [ERP-IR].Due, [ERP-IR].[Closed on], [ERP-IR].[Key User], [ERP-IR].[KU
    > > Comments] FROM [ERP-IR] WHERE ((([ERP-
    > > IR].Department)=""Distribution"")) "
    > > Set rs = Server.CreateObject("ADODB.Recordset")
    > > rs.Open sql, conn, 3, 3
    > > If rs.eof Then
    > > rs.AddNew
    > > End If
    > > Set Session("ERP_IR_Distribution_rs") = rs
    > > End If
    > > %>

    >
    > > Regards,
    > > Hasnain Raja

    >
    > Use "Response.Write sql" before the "rs.Open" statement.
    >
    > Here's how I broke it down for readability:
    >
    > sql = "SELECT
    > [ERP-IR].ID,
    > [ERP-IR].Department,
    > [ERP-IR].[Logged on],
    > [ERP-IR].Issue,
    > [ERP-IR].Code,
    > [ERP-IR].Status,
    > [ERP-IR].[Action Taken],
    > [ERP-IR].[Recommendations/ Comments],
    > [ERP-IR].[Action By],
    > [ERP-IR].Due,
    > [ERP-IR].[Closed on],
    > [ERP-IR].[Key User],
    > [ERP-IR].[KU Comments]
    > FROM [ERP-IR]
    > WHERE ((([ERP-IR].Department)=""Distribution""))"
    >
    > Perhaps your "WHERE" clause should be:
    >
    > FROM [ERP-IR] WHERE [ERP-IR].Department = 'Distribution' "- Hide quoted text -
    >
    > - Show quoted text -


    Thanks Guys, you people have always been so helpful. Thanks for the
    best practices too, I am new to all this and am learning by time :)
     
    Nano, Dec 4, 2007
    #3
  4. Nano

    Nano Guest

    This works fine but what if I have to choose criteria from the FORUM.
    I have got the data in a variable via querystring, now when I execute
    it using that variable in WHERE clause it gives the same error.

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected
    1.

    Error comes at this line:

    rs.Open sql, conn, 3, 3

    Kindly advise.
     
    Nano, Dec 4, 2007
    #4
  5. Nano wrote:
    > This works fine but what if I have to choose criteria from the FORUM.
    > I have got the data in a variable via querystring, now when I execute
    > it using that variable in WHERE clause it gives the same error.
    >
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
    > [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    > Expected
    > 1.
    >
    > Error comes at this line:
    >
    > rs.Open sql, conn, 3, 3
    >
    > Kindly advise.


    Please. Don't ask us to debug a sql statement without showing it to us. You
    must write it to response and show us the result of your vbscript code that
    is supposed to generate the statement

    --
    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], Dec 4, 2007
    #5
    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. Learner
    Replies:
    1
    Views:
    998
    Marina Levit [MVP]
    Jan 30, 2006
  2. Eric Nelson
    Replies:
    5
    Views:
    1,538
    Alexey Smirnov
    Feb 4, 2009
  3. Guy Hocking

    ASP / SQL Query - Conditional SELECT Statement

    Guy Hocking, Jan 21, 2004, in forum: ASP General
    Replies:
    7
    Views:
    205
    Bob Barrows
    Jan 21, 2004
  4. Roland Hall

    ASP LIKE query using parameterized query

    Roland Hall, Jan 12, 2007, in forum: ASP General
    Replies:
    8
    Views:
    225
    Roland Hall
    Jan 13, 2007
  5. palmiere
    Replies:
    1
    Views:
    417
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page