Subscript out of range: '3' Need Help

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

  1. 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"
     
    , Jan 11, 2005
    #1
    1. Advertising

  2. <> wrote in message
    news:...
    > I am getting the following error and I do not know why. Can anyone
    > help?


    > Microsoft VBScript runtime error '800a0009'
    >
    > Subscript out of range: '3'


    > strSQL = "..." & _
    > "..." & _
    > "..." & _


    You have VBScript code in your SQL string here
    VVVVVVVVVVVVVVVVVVVV
    > "WHERE (for i=0 to lenoTitle) " & _


    And here
    VVVV
    > "...next " & _


    In quotes? No index here, oTitle(number)
    VVVVVVVVVVVVVVVVVVVVV VVVVVV
    > "queryStr = queryStr & AND tblauctionitems.Title LIKE '%" & oTitle &
    > "%' ORDER BY AID"


    Ray at work
     
    Ray Costanzo [MVP], Jan 11, 2005
    #2
    1. Advertising

  3. 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. McKirahan Guest

    <> wrote in message
    news:...
    > 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"
    >



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



    <> wrote in message
    news:...
    > 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"
    >
     
    Bob Lehmann, Jan 11, 2005
    #5
  6. McKirahan Guest

    "McKirahan" <> wrote in message
    news:D...
    > <> wrote in message
    > news:...
    > > 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"
    > >

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


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

    "Andy" <> wrote in message
    news:...
    > Curt
    >
    > Can you show me what you mean. I understand what you are saying, but
    > just not sure how to implement. Thank you
    >


    <> wrote in message
    news:...
    > 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"
    >
     
    Mark Schupp, Jan 11, 2005
    #7
  8. 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. Andy wrote:
    > 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


    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
    --
    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], Jan 11, 2005
    #9
  10. McKirahan Guest

    "Andy" <> wrote in message
    news:...
    > 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
    >


    I forgot the apostrophes.

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

    McKirahan

    I thank you very much for helping with this problem. It works great!
    Andy
     
    Andy, Jan 12, 2005
    #11
  12. 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. McKirahan Guest

    "Andy" <> wrote in message
    news:...
    > 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
    >


    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 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. Andy wrote:
    > 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 = 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


    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
    --
    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], Jan 12, 2005
    #15
  16. McKirahan Guest

    "Andy" <> wrote in message
    news:...
    > 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.
    >


    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 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. Andy wrote:
    > 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


    You may need to find a MySQL newsgroup or forum to find out the proper
    syntax for passing datetime values.

    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], Jan 12, 2005
    #18
  19. McKirahan Guest

    "Andy" <> wrote in message
    news:...
    > 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
    >


    Originally you had:
    ORDER BY AID
    not
    GROUP BY AID
     
    McKirahan, Jan 12, 2005
    #19
    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. Richard Delorme

    out of range array subscript

    Richard Delorme, May 3, 2004, in forum: C Programming
    Replies:
    5
    Views:
    485
    Chris Torek
    May 15, 2004
  2. Andy
    Replies:
    6
    Views:
    717
    James Kanze
    May 11, 2007
  3. Han
    Replies:
    4
    Views:
    8,029
  4. yogi_bear_79
    Replies:
    11
    Views:
    976
    James Kanze
    Mar 16, 2008
  5. Lukelrc

    Subscript out of range error

    Lukelrc, May 19, 2004, in forum: ASP General
    Replies:
    2
    Views:
    219
    Mark Schupp
    May 19, 2004
Loading...

Share This Page