By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Subscript out of range: '3' Need Help

P: n/a
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"

Jul 22 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
0 based, not 1 based.

--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
<an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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"

Jul 22 '05 #2

P: n/a

<an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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
Jul 22 '05 #3

P: n/a
Curt

Can you show me what you mean. I understand what you are saying, but
just not sure how to implement. Thank you

Jul 22 '05 #4

P: n/a
<an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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)
Jul 22 '05 #5

P: n/a
I was thinking that the problem is

for i=0 to lenoTitle

what is lenoTitle's value? Does it need a (lenoTitle -1) instead?
--
Curt Christianson
Site & Scripts: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Andy" <an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Curt

Can you show me what you mean. I understand what you are saying, but
just not sure how to implement. Thank you

Jul 22 '05 #6

P: n/a
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

<an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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"

Jul 22 '05 #7

P: n/a
"McKirahan" <Ne**@McKirahan.com> wrote in message
news:Dt********************@comcast.com...
<an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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)
Jul 22 '05 #8

P: n/a
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" <an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Curt

Can you show me what you mean. I understand what you are saying, but
just not sure how to implement. Thank you

<an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com... 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"


Jul 22 '05 #9

P: n/a
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

Jul 22 '05 #10

P: n/a
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.
Jul 22 '05 #11

P: n/a
"Andy" <an*******@earthlink.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
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) & "%'"
Jul 22 '05 #12

P: n/a
McKirahan

I thank you very much for helping with this problem. It works great!
Andy

Jul 22 '05 #13

P: n/a
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

Jul 22 '05 #14

P: n/a
"Andy" <an*******@earthlink.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
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.
Jul 22 '05 #15

P: n/a
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.

Jul 22 '05 #16

P: n/a
"Andy" <an*******@earthlink.net> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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?
Jul 22 '05 #17

P: n/a
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"
Jul 22 '05 #18

P: n/a
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

Jul 22 '05 #19

P: n/a
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"
Jul 22 '05 #20

P: n/a
"Andy" <an*******@earthlink.net> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
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
Jul 22 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.