Subscript out of range: '3' Need Help

Discussion in 'ASP General' started by andy.rich, Jan 11, 2005.

  1. andy.rich

    andy.rich Guest

    I am getting the following error and I do not know why. Can anyone
    help?

    --------------------------------------------------------
    this is what appears on the screen
    --------------------------------------------------------
    2Sports 'trouble shooting
    illustrated 'trouble shooting
    Newsstand 'trouble shooting

    Microsoft VBScript runtime error '800a0009'

    Subscript out of range: '3'

    /Lockerroom/search/searchfound.asp, line 69

    -------------------------------------------------------
    End of screen
    -------------------------------------------------------

    oTitle1 = "Sports illustrated Newsstand"


    oTitle = Split(oTitle1," ")
    lenoTitle = ubound(oTitle,1)
    Response.Write(lenoTitle) 'trouble shooting

    for i=0 to lenoTitle 'trouble shooting
    Response.Write(oTitle(i)) & "<BR>" 'trouble shooting
    next 'trouble shooting

    --------------------------------------------------------
    Line 69 starts here
    --------------------------------------------------------
    strSQL = "SELECT max(tblauctionbids.WinPrice) as highestPrice,
    count(tblauctionbids.AID) as BidCount, tblauctionitems.Title,
    tblauctionitems.MinPrice, tblauctionitems.IID, tblauctionitems.AID,
    tableauctions.StartDate, tableauctions.EndDate " & _
    "FROM tblauctionitems " & _
    "left outer join tableauctions on (tblauctionitems.AID =
    tableauctions.AID) " & _
    "left outer join tblauctionbids on (tblauctionitems.AID =
    tblauctionbids.AID) " & _
    "WHERE (for i=0 to lenoTitle) " & _
    "queryStr = queryStr & Title LIKE '%" & oTitle(i) & "%' OR Title LIKE
    '%" & oTitle(i) & "%' OR" & _
    "next " & _
    "queryStr = left(queryStr, Len(queryStr)-3) " & _
    "queryStr = queryStr & AND tblauctionitems.Title LIKE '%" & oTitle &
    "%' ORDER BY AID"
     
    andy.rich, Jan 11, 2005
    #1
    1. Advertisements

  2. You have VBScript code in your SQL string here
    VVVVVVVVVVVVVVVVVVVV
    And here
    VVVV
    In quotes? No index here, oTitle(number)
    VVVVVVVVVVVVVVVVVVVVV VVVVVV
    Ray at work
     
    Ray Costanzo [MVP], Jan 11, 2005
    #2
    1. Advertisements

  3. andy.rich

    Andy Guest

    Curt

    Can you show me what you mean. I understand what you are saying, but
    just not sure how to implement. Thank you
     
    Andy, Jan 11, 2005
    #3
  4. andy.rich

    McKirahan Guest


    "WHERE (for i=0 to lenoTitle) " -- I don't think so!

    You can't build a WHERE clause that way.

    Also, where does "queryStr" come from and/or how are you trying to use it?

    After "strSQL" is built add this statement and post the results:
    Response.Write(strSQL)
     
    McKirahan, Jan 11, 2005
    #4
  5. andy.rich

    Bob Lehmann Guest

    To solve the out of range error....
    Change this -
    lenoTitle = ubound(oTitle,1)

    To this -
    lenoTitle = ubound(oTitle,1) - 1

    As others have mentioned, you are likely to run into other problems.

    Bob Lehmann
     
    Bob Lehmann, Jan 11, 2005
    #5
  6. andy.rich

    McKirahan Guest

    Perhaps you want something like:

    strSQL = "SELECT"
    strSQL = strSQL & " max(tblauctionbids.WinPrice) as highestPrice,"
    strSQL = strSQL & " count(tblauctionbids.AID) as BidCount,"
    strSQL = strSQL & " tblauctionitems.Title,"
    strSQL = strSQL & " tblauctionitems.MinPrice,"
    strSQL = strSQL & " tblauctionitems.IID,"
    strSQL = strSQL & " tblauctionitems.AID,"
    strSQL = strSQL & " tableauctions.StartDate,"
    strSQL = strSQL & " tableauctions.EndDate"
    strSQL = strSQL & " FROM (tblauctionitems"
    strSQL = strSQL & " LEFT OUTER JOIN tableauctions"
    strSQL = strSQL & " ON (tblauctionitems.AID = tableauctions.AID))"
    strSQL = strSQL & " LEFT OUTER JOIN tblauctionbids"
    strSQL = strSQL & " ON (tblauctionitems.AID = tblauctionbids.AID)"
    strSQL = strSQL & " WHERE Title LIKE %" & oTitle(0) & "%"
    For i = 1 To UBound(oTitle)
    strSQL = strSQL & " OR Title LIKE %" & oTitle(i) & "%"
    Next
    strSQL = strSQL & " ORDER BY AID"

    Response.Write(strSQL)
     
    McKirahan, Jan 11, 2005
    #6
  7. andy.rich

    Mark Schupp Guest

    The out of range error is being caused by the way you are trying to build
    the where clause. It makes no sense.

    "WHERE (for i=0 to lenoTitle) " & _
    queryStr = queryStr & Title LIKE '%" & oTitle(i) & "%' OR Title LIKE '%" &
    oTitle(i) & "%' OR" & _
    "next " & _

    This embeds the for loop in the sql statement. The loop control variable is
    not being sequenced between 0 and 2 as it should for a 3-element array.
    Instead, it contains the value from the exit condition of the last for loop
    (3). That is why you are getting the subscript out of range error.

    I think what you want is

    strSQL = "SELECT max(tblauctionbids.WinPrice) as highestPrice,
    count(tblauctionbids.AID) as BidCount, tblauctionitems.Title,
    tblauctionitems.MinPrice, tblauctionitems.IID, tblauctionitems.AID,
    tableauctions.StartDate, tableauctions.EndDate " & _
    "FROM tblauctionitems " & _
    "left outer join tableauctions on (tblauctionitems.AID = tableauctions.AID)
    " & _
    "left outer join tblauctionbids on (tblauctionitems.AID =
    tblauctionbids.AID) " & _
    "WHERE "

    queryStr = ""
    for i=0 to lenoTitle
    queryStr = queryStr & " Title LIKE '%" & oTitle(i) & "%' OR Title LIKE
    '%" & oTitle(i) & "%' OR"
    next
    queryStr = left(queryStr, Len(queryStr)-3)
    queryStr = queryStr & AND tblauctionitems.Title LIKE '%" & oTitle & "%' "

    strSQL = strSQL & queryStr & "ORDER BY AID"

    I'm still not sure if that will get what you want because the sql code looks
    peculiar. Be sure to write out the SQL to see if you are getting what you
    want and adjust accordingly.

    On other thing. You need to account for the possibility that the user might
    embed single quotes in input (either inadvertantly or as an attempted hack)
    All input needs to be passed through a filter that will correct for this by
    making replacing single quotes with 2 single quotes..

    Replace( oTitle(i), "'", "''" )

    --
    --Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com
     
    Mark Schupp, Jan 11, 2005
    #7
  8. andy.rich

    Andy Guest

    McKirahan

    Your suggestion has come the closest to working

    strSQL = "SELECT"
    strSQL = strSQL & " max(tblauctionbids.WinPrice) as highestPrice,"
    strSQL = strSQL & " count(tblauctionbids.AID) as BidCount,"
    strSQL = strSQL & " tblauctionitems.Title,"
    strSQL = strSQL & " tblauctionitems.MinPrice,"
    strSQL = strSQL & " tblauctionitems.IID,"
    strSQL = strSQL & " tblauctionitems.AID,"
    strSQL = strSQL & " tableauctions.StartDate,"
    strSQL = strSQL & " tableauctions.EndDate"
    strSQL = strSQL & " FROM (tblauctionitems"
    strSQL = strSQL & " LEFT OUTER JOIN tableauctions"
    strSQL = strSQL & " ON (tblauctionitems.AID = tableauctions.AID))"
    strSQL = strSQL & " LEFT OUTER JOIN tblauctionbids"
    strSQL = strSQL & " ON (tblauctionitems.AID = tblauctionbids.AID)"
    strSQL = strSQL & " WHERE Title LIKE %" & oTitle(0) & "%"

    For i = 1 To UBound(oTitle)

    strSQL = strSQL & " OR Title LIKE %" & oTitle(i) & "%"

    Next
    strSQL = strSQL & " ORDER BY AID"

    Response.Write(strSQL)


    But I am getting the following error any suggestions

    SELECT max(tblauctionbids.WinPrice) as highestPrice,
    count(tblauctionbids.AID) as BidCount, tblauctionitems.Title,
    tblauctionitems.MinPrice, tblauctionitems.IID, tblauctionitems.AID,
    tableauctions.StartDate, tableauctions.EndDate FROM (tblauctionitems
    LEFT OUTER JOIN tableauctions ON (tblauctionitems.AID =
    tableauctions.AID)) LEFT OUTER JOIN tblauctionbids ON
    (tblauctionitems.AID = tblauctionbids.AID) WHERE Title LIKE %Sports% OR
    Title LIKE %illustrated% OR Title LIKE %Newsstand% ORDER BY AID

    Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

    [TCX][MyODBC]You have an error in your SQL syntax near '%Sports% OR
    Title LIKE %illustrated% OR Title LIKE %Newsstand% ORDER BY AID' at
    line 1

    /Lockerroom/search/searchfound.asp, line 124
     
    Andy, Jan 11, 2005
    #8
  9. You have to concatenate the quotes around your string criteria:
    strSQL = strSQL & " WHERE Title LIKE '%" & oTitle(0) & "%'"

    Your goal is to create a sql statement that will run as-is in your
    database's query execution tool.

    Bob Barrows
     
    Bob Barrows [MVP], Jan 11, 2005
    #9
  10. andy.rich

    McKirahan Guest

    I forgot the apostrophes.

    Change both references from
    Title LIKE %" & oTitle(i) & "%"
    to
    Title LIKE '%" & oTitle(i) & "%'"
     
    McKirahan, Jan 11, 2005
    #10
  11. andy.rich

    Andy Guest

    McKirahan

    I thank you very much for helping with this problem. It works great!
    Andy
     
    Andy, Jan 12, 2005
    #11
  12. andy.rich

    Andy Guest

    McKirahan

    The tableauctions.EndDate > '" & oNow & "' in the beginning of where
    clause is not working. All the other syntax is working correctly. I am
    not sure if I have it setup correctly. Can you help?

    strSQL = "SELECT"
    strSQL = strSQL & " max(tblauctionbids.WinPrice) as highestPrice,"
    strSQL = strSQL & " count(tblauctionbids.AID) as BidCount,"
    strSQL = strSQL & " tblauctionitems.Title,"
    strSQL = strSQL & " tblauctionitems.MinPrice,"
    strSQL = strSQL & " tblauctionitems.IID,"
    strSQL = strSQL & " tblauctionitems.AID,"
    strSQL = strSQL & " tableauctions.StartDate,"
    strSQL = strSQL & " tableauctions.EndDate"
    strSQL = strSQL & " FROM tblauctionitems"
    strSQL = strSQL & " LEFT OUTER JOIN tableauctions"
    strSQL = strSQL & " ON (tblauctionitems.AID = tableauctions.AID)"
    strSQL = strSQL & " LEFT OUTER JOIN tblauctionbids"
    strSQL = strSQL & " ON (tblauctionitems.AID = tblauctionbids.AID)"

    strSQL = strSQL & " WHERE tableauctions.EndDate > '" & oNow & "' AND

    (Title LIKE '%" & oTitle(0) & "%'"

    For i = 1 To UBound(oTitle)

    strSQL = strSQL & " OR Title LIKE '%" & oTitle(i) & "%'"

    Next
    strSQL = strSQL & ") GROUP BY AID"

    -------------------------------------------------
    Results
    -------------------------------------------------

    SELECT max(tblauctionbids.WinPrice) as highestPrice,
    count(tblauctionbids.AID) as BidCount, tblauctionitems.Title,
    tblauctionitems.MinPrice, tblauctionitems.IID, tblauctionitems.AID,
    tableauctions.StartDate, tableauctions.EndDate FROM tblauctionitems
    LEFT OUTER JOIN tableauctions ON (tblauctionitems.AID =
    tableauctions.AID) LEFT OUTER JOIN tblauctionbids ON
    (tblauctionitems.AID = tblauctionbids.AID) WHERE tblauctionitems.Title
    LIKE '%Sports%' OR Title LIKE '%Magazine%' OR Title LIKE
    '%illustrated%' OR Title LIKE '%NL%' AND EndDate > '1/11/2005 11:05:29
    PM' GROUP BY AID
     
    Andy, Jan 12, 2005
    #12
  13. andy.rich

    McKirahan Guest

    How is the EndDate defined in the database table -- as Date or DateTime?

    If Date then use "Date()"; it DateTime then use "Now()"

    (Presuming that you currently set "oNow = Now()".)

    Also change the SQL from:
    strSQL = strSQL & " WHERE tableauctions.EndDate > '" & oNow & "' AND
    to
    strSQL = strSQL & " WHERE tableauctions.EndDate > #" & oNow & "# AND

    Let us know if that works.
     
    McKirahan, Jan 12, 2005
    #13
  14. andy.rich

    Andy Guest

    The EndDate field is a DateTime field and oNow does = Now(). When I add
    # around the oNow variable like you suggested, I get a syntax error. My
    original sql line looks like this and the EndDate works great:

    WHERE tblauctionitems.Title LIKE '%" & oTitle & "%' AND EndDate > Now()
    GROUP BY AID


    But for some reason in the new strSQL line it is not working. I can not
    figure it out.
     
    Andy, Jan 12, 2005
    #14
  15. I haven't seen where you've told us what database you are using. Please
    supply database type and version. It's always relevant, even more so in this
    case.

    Again, your goal is to build a sql statement that will run as-is in your
    database's native query execution tool. if the sql string you've built is
    correct, you should be able to copy and paste the result of your
    response.write into your database's query tool and run it. Usually, you will
    get a better error message than the one supplied by ADO.

    The corrolary to this of course is: all queries should be designed, built
    and tested in your database's native environment, where you can take
    advantage of any query optimization tools provided by your rdbms. Once you
    have a query that works, using criteria such as those that will be supplied
    by your aplication, you will now have a starting point as to what needs to
    be built in your application: make your code generate the same sql statement
    that works in your database's query tool (unless you are using wildcards in
    Access, which requires * an ? as the wildcards in its query tools. Those
    wildcards need to be replaced by % and _ when you run the queries via ADO).

    As for passing datetime values to your database, you need to read this:
    http://www.aspfaq.com/show.asp?id=2313 vbscript
    http://www.aspfaq.com/show.asp?id=2040 help with dates
    http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion

    Bob BArrows
     
    Bob Barrows [MVP], Jan 12, 2005
    #15
  16. andy.rich

    McKirahan Guest

    Add a Response.Write(strSQL) statement after strSQL is built but before it's
    used.

    Cut-and-paste that into your database's query tool and try to run it.

    Fix it there then uses the fixes you found to modify the building of the
    strSQL statement.

    What database (and version) are you using?
     
    McKirahan, Jan 12, 2005
    #16
  17. andy.rich

    Andy Guest

    MySQL 3.23.52

    I am not having to much luck in the DB's query tool. I will still
    continue to figure it out in the DB query tool, if you have any
    suggestions please post them. The Response.Write(strSQL) statement
    after strSQL is

    SELECT max(tblauctionbids.WinPrice) as highestPrice,
    count(tblauctionbids.AID) as BidCount, tblauctionitems.Title,
    tblauctionitems.MinPrice, tblauctionitems.IID, tblauctionitems.AID,
    tableauctions.StartDate, tableauctions.EndDate FROM tblauctionitems
    LEFT OUTER JOIN tableauctions ON (tblauctionitems.AID =
    tableauctions.AID) LEFT OUTER JOIN tblauctionbids ON
    (tblauctionitems.AID = tblauctionbids.AID) WHERE tblauctionitems.Title
    LIKE '%Sports%' AND EndDate > '1/12/2005 8:07:50 AM' GROUP BY AID
     
    Andy, Jan 12, 2005
    #17
  18. You may need to find a MySQL newsgroup or forum to find out the proper
    syntax for passing datetime values.

    Bob Barrows
     
    Bob Barrows [MVP], Jan 12, 2005
    #18
  19. andy.rich

    McKirahan Guest

    Originally you had:
    ORDER BY AID
    not
    GROUP BY AID
     
    McKirahan, Jan 12, 2005
    #19
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.