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"
20  5610 
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"
<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
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"
"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)
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
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"
"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)
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"
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 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.
"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) & "%'"
McKirahan
I thank you very much for helping with this problem. It works great!
Andy
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" <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.
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" <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?
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"
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 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"
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jv |
last post by:
I have a form that is used every day to create quotes. However, every 2
weeks or so a user would randomly get an error 9 - subscript out range
message. I'm not using any array in this form. Does...
|
by: VB Programmer |
last post by:
I'm acutally using VB6, not VB.NET, but I couldn't find the newsgroup for
version 6....
I need help for something that should be simple. I keep getting a
"subscript out of range" error and...
|
by: Pedro Graca |
last post by:
I run into a strange warning (for me) today (I was trying to improve
the score of the UVA #10018 Programming Challenge).
$ gcc -W -Wall -std=c89 -pedantic -O2 10018-clc.c -o 10018-clc...
|
by: wennerd |
last post by:
I am getting a Subscript out of range after executing the following command
Set oRstPick = connTemp.Execute(sSQL)
It is a simple query:
sSQL = "SELECT idCategory, categoryDesc, idParentCategory...
|
by: josh |
last post by:
Hi I've a dubt!
when we have overloaded functions the compiler chooses the right
being based on the argument lists...but when we have two subscript
overloaded functions it resolves them being...
|
by: Andy |
last post by:
Hi all,
I started developing a little app on my Mac using XCode some month
ago. The app is running fine on my mac like a sharm. Now I am nearly
ready and yesterday I moved the whole source code...
|
by: DThreadgill |
last post by:
Hi again -
I have a text file that is received on a daily basis and I use VBA to break down each field and add to an existing table. There are 50 fields in the text file (separated by semicolon)...
|
by: Han |
last post by:
when I exe my project in vs.net2005,I got the error following:
Debug Assertion Failed!
Program:........
File:c:\program files\microsoft visual studio 8\vc\include\vector
Line:756
...
|
by: brandon01 |
last post by:
Keep getting "subscript out of range" any idea why? This function is looped btw.. Thx...
Private Sub getBday()
bDay = List1.List(nextBday)
bDayArr = Split(bDay, " - ")
Text1.Text =...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
