How can display the sql before the error occurs?

Discussion in 'ASP General' started by Big Moxy, Nov 29, 2007.

  1. Big Moxy

    Big Moxy Guest

    I'm getting this error on my asp/access page. It's a sql error
    triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
    user input on a search page.

    ADODB.Recordset error '800a0bb9'
    Arguments are of the wrong type, are out of acceptable range, or are
    in conflict with one another.

    The problem I'm having is getting a Response.Write(sql) to actually
    display the sql code before the open statement.

    Can someone please help me display my generated sql code?

    Thank you!
     
    Big Moxy, Nov 29, 2007
    #1
    1. Advertising

  2. "Big Moxy" <> wrote in message
    news:...
    > I'm getting this error on my asp/access page. It's a sql error
    > triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
    > user input on a search page.
    >
    > ADODB.Recordset error '800a0bb9'
    > Arguments are of the wrong type, are out of acceptable range, or are
    > in conflict with one another.
    >
    > The problem I'm having is getting a Response.Write(sql) to actually
    > display the sql code before the open statement.
    >
    > Can someone please help me display my generated sql code?
    >



    Try using a Response.End directly after the Response.Write sql

    --
    Anthony Jones - MVP ASP/ASP.NET
     
    Anthony Jones, Nov 29, 2007
    #2
    1. Advertising

  3. Big Moxy

    Big Moxy Guest

    On Nov 29, 3:26 pm, "Anthony Jones" <> wrote:
    > "Big Moxy" <> wrote in message
    >
    > news:...
    >
    > > I'm getting this error on my asp/access page. It's a sql error
    > > triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
    > > user input on a search page.

    >
    > > ADODB.Recordset error '800a0bb9'
    > > Arguments are of the wrong type, are out of acceptable range, or are
    > > in conflict with one another.

    >
    > > The problem I'm having is getting a Response.Write(sql) to actually
    > > display the sql code before the open statement.

    >
    > > Can someone please help me display my generated sql code?

    >
    > Try using a Response.End directly after the Response.Write sql
    >
    > --
    > Anthony Jones - MVP ASP/ASP.NET


    I'm sorry to say that it doesn't stop at the Response.End(). The code
    continues until the error occurs.
     
    Big Moxy, Nov 29, 2007
    #3
  4. Big Moxy wrote:
    > On Nov 29, 3:26 pm, "Anthony Jones" <> wrote:
    >> "Big Moxy" <> wrote in message
    >>
    >> news:...
    >>
    >>> I'm getting this error on my asp/access page. It's a sql error
    >>> triggered by Recordset1.open sql,conn,1,1. The sql is constructed
    >>> from user input on a search page.

    >>
    >>> ADODB.Recordset error '800a0bb9'
    >>> Arguments are of the wrong type, are out of acceptable range, or are
    >>> in conflict with one another.

    >>
    >>> The problem I'm having is getting a Response.Write(sql) to actually
    >>> display the sql code before the open statement.


    The error you are getting is unlikely to be caused by the sql string you are
    using. It is more likely to be a problem with the arguments used for the
    Open statement.

    >>
    >>> Can someone please help me display my generated sql code?

    >>
    >> Try using a Response.End directly after the Response.Write sql
    >>


    > I'm sorry to say that it doesn't stop at the Response.End(). The code
    > continues until the error occurs.


    And you have the Response.Write statement BEFORE the Open statement? You are
    wasting your time and ours by failing to show us the code that's giving you
    the problem.

    --
    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], Nov 30, 2007
    #4
  5. Big Moxy

    Big Moxy Guest

    On Nov 29, 4:05 pm, "Bob Barrows [MVP]" <>
    wrote:
    > Big Moxy wrote:
    > > On Nov 29, 3:26 pm, "Anthony Jones" <> wrote:
    > >> "Big Moxy" <> wrote in message

    >
    > >>news:...

    >
    > >>> I'm getting this error on my asp/access page. It's a sql error
    > >>> triggered by Recordset1.open sql,conn,1,1. The sql is constructed
    > >>> from user input on a search page.

    >
    > >>> ADODB.Recordset error '800a0bb9'
    > >>> Arguments are of the wrong type, are out of acceptable range, or are
    > >>> in conflict with one another.

    >
    > >>> The problem I'm having is getting a Response.Write(sql) to actually
    > >>> display the sql code before the open statement.

    >
    > The error you are getting is unlikely to be caused by the sql string you are
    > using. It is more likely to be a problem with the arguments used for the
    > Open statement.
    >
    >
    >
    > >>> Can someone please help me display my generated sql code?

    >
    > >> Try using a Response.End directly after the Response.Write sql

    >
    > > I'm sorry to say that it doesn't stop at the Response.End(). The code
    > > continues until the error occurs.

    >
    > And you have the Response.Write statement BEFORE the Open statement? You are
    > wasting your time and ours by failing to show us the code that's giving you
    > the problem.
    >
    > --
    > 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"- Hide quoted text -
    >
    > - Show quoted text -


    This is the code that builds the SQL.

    Response.Write(Request.ServerVariables("REQUEST_METHOD") & "<br>")
    If (Request.ServerVariables("REQUEST_METHOD") = "POST") Then
    If Request.Form("ctlSearchField") <> "" Then
    Dim strWhere, strQuote
    If (IsNumeric(ctlSearchFor) AND ctlSearchOption <> "LIKE" AND
    ctlSearchOption <> "HAS") Then
    strQuote = ""
    Else
    strQuote = "'"
    End If
    Response.Write("strQuote=" & strQuote & "x<br>")

    If ctlSearchOption = "LIKE" Then
    ctlSearchFor = ctrlSearchFor & "*"
    End If
    If ctlSearchOption = "HAS" Then
    ctlSearchOption = "LIKE"
    ctlSearchFor = "*" & ctrlSearchFor & "*"
    End If
    Response.Write("ctlSearchOption=" & ctlSearchOption & "+<br>")
    Response.Write("ctlSearchFor=" & ctlSearchFor & "+<br>")
    If ctlSearchField = "*" Then
    strWhere = "ID " & ctlSearchOption & " " & strQuote & ctlSearchFor &
    strQuote & " OR " &_
    "[Equipment Description] " & ctlSearchOption & " " & strQuote &
    ctlSearchFor & strQuote & " OR " &_
    "Day " & ctlSearchOption & " " & strQuote & ctlSearchFor &
    strQuote & " OR " &_
    "Week " & ctlSearchOption & " " & strQuote & ctlSearchFor &
    strQuote & " OR " &_
    "Month " & ctlSearchOption & " " & strQuote & ctlSearchFor &
    strQuote & " "
    Else
    strWhere = "WHERE " & ctlSearchField & " " & ctlSearchOption & " " &
    strQuote & ctlSearchFor & strQuote & " "
    End If
    Response.Write("strWhere=" & strWhere & "+<br>")
    Response.End()
    sql = "SELECT * FROM [" & tables(tableID) & "] " &_
    "WHERE " & strWhere &_
    "ORDER BY ID ASC"
    Response.Write(sql & "<br>")
    Response.End()

    End If
    Else
    sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
    Response.Write(sql & "<br>")
    End If

    Recordset1.open sql,conn,1,1

    If not Recordset1.eof then
    Recordset1_total = Recordset1.recordcount
    Else
    Recordset1_total = 0
    End if

    Recordset1_numRows = Recordset1_total

    This is the seach form:

    <form action="db.asp?ID=<%=tableID%>" method="post" name="search"
    target="_self">
    <TABLE width="500" align="center" border="1">
    <TBODY>
    <TR>
    <TD vAlign="middle" align="center"><h3><B>Search for: </B></h3></TD>
    </TR>
    <TR>
    <TD align="left">
    <select id="ctlSearchField">
    <option value="*">Any field</option>
    <option value="ID">ID</option>
    <option value='Equipment Description'>Equipment Description</option>
    <option value='Day'>Day</option>
    <option value='Week'>Week</option>
    <option value='Month'>Month</option>
    </select>
    </TD>
    </TR>
    <TR>
    <TD align="left">
    <select id="ctlSearchOption">
    <option value="HAS">Contains</option>
    <option value="=">Equals</option>
    <option value="LIKE">Starts with ...</option>
    <option value=">">More than ...</option>
    <option value="<">Less than ...</option>
    <option value=">=">Equal or more than ...</option>
    <option value="<=">Equal or less than ...</option>
    </select>
    </TD>
    </TR>
    <TR>
    <TD align="left"><INPUT name="ctlSearchFor" type="text"
    id="ctlSearchFor" size="40">
    </TD>
    </TR>
    <TR>
    <TD align="left"><INPUT type="submit" value="Search">&nbsp;<INPUT
    type="submit" value="Show all">
    </TD>
    </TR>
    </TBODY>
    </TABLE>
    </form>
     
    Big Moxy, Nov 30, 2007
    #5
  6. Big Moxy wrote:
    > sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
    > Response.Write(sql & "<br>")
    > End If


    You need the Response.End right here, immediately before the Open statement

    >
    > Recordset1.open sql,conn,1,1
    >
    > If not Recordset1.eof then
    > Recordset1_total = Recordset1.recordcount
    > Else
    > Recordset1_total = 0
    > End if
    >
    > Recordset1_numRows = Recordset1_total
    >


    --
    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], Nov 30, 2007
    #6
  7. Big Moxy

    Big Moxy Guest

    On Nov 29, 4:47 pm, "Bob Barrows [MVP]" <>
    wrote:
    > Big Moxy wrote:
    > > sql = "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC"
    > > Response.Write(sql & "<br>")
    > > End If

    >
    > You need the Response.End right here, immediately before the Open statement
    >
    >
    >
    > > Recordset1.open sql,conn,1,1

    >
    > > If not Recordset1.eof then
    > > Recordset1_total = Recordset1.recordcount
    > > Else
    > > Recordset1_total = 0
    > > End if

    >
    > > Recordset1_numRows = Recordset1_total

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


    I placed it where I did because the initial page display uses sql =
    "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
    just fine. I will never get the search option if the Response.End is
    placed where you suggest.
     
    Big Moxy, Nov 30, 2007
    #7
  8. Big Moxy wrote:

    >
    > I placed it where I did because the initial page display uses sql =
    > "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
    > just fine. I will never get the search option if the Response.End is
    > placed where you suggest.


    I don't get you. if you want to troubleshoot the sql statement, then you
    have to print it out before it is executed ...
    --
    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], Nov 30, 2007
    #8
  9. Big Moxy

    Big Moxy Guest

    On Nov 29, 5:10 pm, "Bob Barrows [MVP]" <>
    wrote:
    > Big Moxy wrote:
    >
    > > I placed it where I did because the initial page display uses sql =
    > > "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
    > > just fine. I will never get the search option if the Response.End is
    > > placed where you suggest.

    >
    > I don't get you. if you want to troubleshoot the sql statement, then you
    > have to print it out before it is executed ...
    > --
    > 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"


    Perhaps you are not understanding the sequence of events.

    The page with the problem serves two purposes.

    1. It displays a tabular list of data
    2. Underneath the list is a search option -

    -Select a field to search on
    -Select the type of search
    -Click the search button

    The initial display is generated by an HTTP GET so the initial SQL is
    "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" and that
    ALWAYS works. The problem is with the SQL generated from the search
    form.

    A search results display is generated by an HTTP POST of the search
    form so it follows the IF "POST" Then logic.

    There is ONE open statement for both page entry points. Besides what
    is wrong is conditionally executing the Response.End? Having the
    Response.End inside of an If statement should work. The concept works
    in other languages I have coded in. Is VBscript an exception?

    If <some condition> Then
    build sql statement
    response.write(sql)
    response.end
    Else
    build sql statement
    End If
    Recordset.open...

    So as you can see if I place the Response.End after the End If and
    before the Open I will never get a display.
     
    Big Moxy, Nov 30, 2007
    #9
  10. Big Moxy

    Big Moxy Guest

    On Nov 29, 5:36 pm, Big Moxy <> wrote:
    > On Nov 29, 5:10 pm, "Bob Barrows [MVP]" <>
    > wrote:
    >
    > > Big Moxy wrote:

    >
    > > > I placed it where I did because the initial page display uses sql =
    > > > "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which works
    > > > just fine. I will never get the search option if the Response.End is
    > > > placed where you suggest.

    >
    > > I don't get you. if you want to troubleshoot the sql statement, then you
    > > have to print it out before it is executed ...
    > > --
    > > 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"

    >
    > Perhaps you are not understanding the sequence of events.
    >
    > The page with the problem serves two purposes.
    >
    > 1. It displays a tabular list of data
    > 2. Underneath the list is a search option -
    >
    > -Select a field to search on
    > -Select the type of search
    > -Click the search button
    >
    > The initial display is generated by an HTTP GET so the initial SQL is
    > "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" and that
    > ALWAYS works. The problem is with the SQL generated from the search
    > form.
    >
    > A search results display is generated by an HTTP POST of the search
    > form so it follows the IF "POST" Then logic.
    >
    > There is ONE open statement for both page entry points. Besides what
    > is wrong is conditionally executing the Response.End? Having the
    > Response.End inside of an If statement should work. The concept works
    > in other languages I have coded in. Is VBscript an exception?
    >
    > If <some condition> Then
    > build sql statement
    > response.write(sql)
    > response.end
    > Else
    > build sql statement
    > End If
    > Recordset.open...
    >
    > So as you can see if I place the Response.End after the End If and
    > before the Open I will never get a display.


    NEVER MIND. I WILL SIMPLY SPLIT THE PAGES AND TROUBLESHOOT THE SEARCH
    RESULTS SEPARATELY.
     
    Big Moxy, Nov 30, 2007
    #10
  11. Big Moxy wrote:
    > On Nov 29, 5:10 pm, "Bob Barrows [MVP]" <>
    > wrote:
    >> Big Moxy wrote:
    >>
    >>> I placed it where I did because the initial page display uses sql =
    >>> "SELECT * FROM [" & tables(tableID) & "] ORDER BY ID ASC" which
    >>> works just fine. I will never get the search option if the
    >>> Response.End is placed where you suggest.

    >>
    >> I don't get you. if you want to troubleshoot the sql statement, then
    >> you have to print it out before it is executed ...
    >> --
    >> 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"

    >
    > Perhaps you are not understanding the sequence of events.


    It's irrelevant. Your goal at this point is not to make the page run to
    completion. Your goal at this point is to discover what the sql string
    contains (at least, that was the goal you stated in your opening post).
    Nothing else that happens after the sql string is built is relevant until
    you determine that your sql statement is correct. Once you have determined
    that the sql statement is what you expect it to be, then you can start
    worrying about getting the rest of the page to run.

    --
    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], Nov 30, 2007
    #11
  12. Big Moxy wrote:

    > So as you can see if I place the Response.End after the End If and
    > before the Open I will never get a display.


    Oh damn - my previous repoly was bollocks.
    I need to look at this again tomorrow

    --
    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], Nov 30, 2007
    #12
  13. Big Moxy wrote:
    > NEVER MIND. I WILL SIMPLY SPLIT THE PAGES AND TROUBLESHOOT THE SEARCH
    > RESULTS SEPARATELY.


    Oh shoot - you got to this too quick. You're right - my previous reply was
    crap - I need to shut the computer off ... now.
    --
    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], Nov 30, 2007
    #13
  14. Big Moxy

    p byers Guest

    Just a thought
    Why not use "On Error" to control the Response.Write and Response.End

    ByTheWay, you used "Response.End()" - I dont us the brackets for this
    statement

    Pete (Northolt UK)

    Big Moxy wrote:

    > I'm getting this error on my asp/access page. It's a sql error
    > triggered by Recordset1.open sql,conn,1,1. The sql is constructed from
    > user input on a search page.
    >
    > ADODB.Recordset error '800a0bb9'
    > Arguments are of the wrong type, are out of acceptable range, or are
    > in conflict with one another.
    >
    > The problem I'm having is getting a Response.Write(sql) to actually
    > display the sql code before the open statement.
    >
    > Can someone please help me display my generated sql code?
    >
    > Thank you!
     
    p byers, Nov 30, 2007
    #14
  15. Big Moxy wrote:

    >
    > NEVER MIND. I WILL SIMPLY SPLIT THE PAGES AND TROUBLESHOOT THE SEARCH
    > RESULTS SEPARATELY.


    Sorry about yesterday. Are you still here? Do you still need help with
    this?

    --
    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], Nov 30, 2007
    #15
    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. Lloyd Sheen
    Replies:
    3
    Views:
    353
    Lloyd Sheen
    Mar 7, 2004
  2. =?Utf-8?B?UlVTU0VMTCBNQ0dJTk5JUw==?=

    Can you intercept the url request before a 404 error occurs?

    =?Utf-8?B?UlVTU0VMTCBNQ0dJTk5JUw==?=, Jul 14, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    656
    Scott Allen
    Jul 14, 2004
  3. Andy G

    Send email when error occurs

    Andy G, Mar 18, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    565
    Andy G
    Mar 18, 2005
  4. Marek Kurowski
    Replies:
    1
    Views:
    997
    Mark A. Odell
    Aug 18, 2004
  5. Geek
    Replies:
    3
    Views:
    146
Loading...

Share This Page