problem with recordset

[bdc101]

Hello all:

I am trying to run a query against a database in an asp page, and I am getting an empty recordset returned. I know there is data that should be returned by the database, and I know the query works because I can run it against the database manually with success. I cannot find what could be causing the problem. The actual error I get is 800a0009 when I try to do a ubound against the array I am putting the data in. I know that means it cannot find an upper bound which indicates an empty array. Here is the code that runs the query and puts it in arrays:

'Get Info from Previous ASP Pages

'Declare Variables
Dim strConnect, conn, salesRep, fullName, states, sqlQuotes, sqlOrders, rsQuotes, rsOrders
Dim startMonth, endMonth, currStartYear, currEndYear, currStartDate, currEndDate
Dim prevStartYear, prevEndYear, prevStartDate, prevEndDate

strConnect = "Main"
set conn=server.createobject("adodb.connection")
conn.open strConnect

states = request.querystring("statecd")
salesRep = request.querystring("salesrep")
startMonth = CDbl(FormatNumber(request.querystring("repmonth")))
currStartYear = CDbl(FormatNumber(request.querystring("repyear")))

'Get Salesrep Name
sql = "select concat(firstname, ' ', lastname) as fullname from users where id = " &salesRep
Dim rsRep
set rsRep=conn.execute(sql)
fullName = rsRep("fullname")
rsRep.close
set rsRep=nothing

'Set Up Dates to Use for Queries
endMonth = startMonth + 2
currEndYear = currStartYear
if endMonth > 12 then
endMonth = endMonth - 12
currEndYear = currStartYear + 1
end if
prevEndYear = currEndYear - 1
prevStartYear = currStartYear - 1

currStartDate = ""& currStartYear &"-"& startMonth &"-01"
currEndDate = ""& currEndYear &"-"& endMonth &"-"& LastDay(endMonth,currEndYear)

prevStartDate = ""& prevStartYear &"-"& startMonth &"-01"
prevEndDate = ""& prevEndYear &"-"& endMonth &"-"& LastDay(endMonth,prevEndYear)

-----------------------------------------------------------
code for Quote info which works
-----------------------------------------------------------
'Pull Order Data From Database
sqlOrders = ""
sqlOrders = "SELECT i.stateCD AS State, IFNULL(SUM(i.netamt),0) AS netAmt, IFNULL(COUNT(i.inv),0) AS OrderCount, MAX(YEAR(i.dtordrec2)) AS Year "
sqlOrders = sqlOrders & "FROM invoice AS i "
sqlOrders = sqlOrders & "WHERE i.inv <> '62483' "
sqlOrders = sqlOrders & " AND i.cust <> '38282' "
sqlOrders = sqlOrders & " AND i.cust <> '36034' "
sqlOrders = sqlOrders & " AND i.cust <> '43304' "
sqlOrders = sqlOrders & " AND i.cust <> '46391' "
sqlOrders = sqlOrders & " AND i.stateCD IN ("& states &") "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('165%') AND i.ZipCode NOT LIKE ('16401%') AND i.ZipCode NOT LIKE ('16403%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16407%') AND i.ZipCode NOT LIKE ('16410%') AND i.ZipCode NOT LIKE ('16411%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16412%') AND i.ZipCode NOT LIKE ('16413%') AND i.ZipCode NOT LIKE ('16415%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16417%') AND i.ZipCode NOT LIKE ('16421%') AND i.ZipCode NOT LIKE ('16423%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16426%') AND i.ZipCode NOT LIKE ('16427%') AND i.ZipCode NOT LIKE ('16428%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16430%') AND i.ZipCode NOT LIKE ('16434%') AND i.ZipCode NOT LIKE ('16435%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16438%') AND i.ZipCode NOT LIKE ('16441%') AND i.ZipCode NOT LIKE ('16442%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16443%') AND i.ZipCode NOT LIKE ('16444%') AND i.ZipCode NOT LIKE ('16475%') "
sqlOrders = sqlOrders & " AND i.dtordrec2 BETWEEN '"& prevStartDate &"' AND '"& prevEndDate &"' "
sqlOrders = sqlOrders & "GROUP BY State "
sqlOrders = sqlOrders & "UNION "
sqlOrders = sqlOrders & "SELECT St AS State, IFNULL(SUM(nAmt),0) AS netAmt, IFNULL(SUM(OrdCount),0) AS OrderCount, Yr AS Year "
sqlOrders = sqlOrders & "FROM (SELECT i.stateCD AS St, IFNULL(SUM(i.netamt),0) AS nAmt, IFNULL(COUNT(i.inv),0) AS OrdCount, MAX(YEAR(i.dtordrec2)) AS Yr "
sqlOrders = sqlOrders & " FROM invoice AS i "
sqlOrders = sqlOrders & " WHERE i.inv <> '62483' "
sqlOrders = sqlOrders & " AND i.cust <> '38282' "
sqlOrders = sqlOrders & " AND i.cust <> '36034' "
sqlOrders = sqlOrders & " AND i.cust <> '43304' "
sqlOrders = sqlOrders & " AND i.cust <> '46391' "
sqlOrders = sqlOrders & " AND i.stateCD IN ("& states &") "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('165%') AND i.ZipCode NOT LIKE ('16401%') AND i.ZipCode NOT LIKE ('16403%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16407%') AND i.ZipCode NOT LIKE ('16410%') AND i.ZipCode NOT LIKE ('16411%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16412%') AND i.ZipCode NOT LIKE ('16413%') AND i.ZipCode NOT LIKE ('16415%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16417%') AND i.ZipCode NOT LIKE ('16421%') AND i.ZipCode NOT LIKE ('16423%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16426%') AND i.ZipCode NOT LIKE ('16427%') AND i.ZipCode NOT LIKE ('16428%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16430%') AND i.ZipCode NOT LIKE ('16434%') AND i.ZipCode NOT LIKE ('16435%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16438%') AND i.ZipCode NOT LIKE ('16441%') AND i.ZipCode NOT LIKE ('16442%') "
sqlOrders = sqlOrders & " AND i.ZipCode NOT LIKE ('16443%') AND i.ZipCode NOT LIKE ('16444%') AND i.ZipCode NOT LIKE ('16475%') "
sqlOrders = sqlOrders & " AND i.dtordrec2 BETWEEN '"& currStartDate &"' AND '"& currEndDate &"' "
sqlOrders = sqlOrders & " GROUP BY St "
sqlOrders = sqlOrders & " UNION "
sqlOrders = sqlOrders & " SELECT o.billSt AS St, IFNULL(SUM(o.totalAMT - o.shipAMT - o.taxAMT),0) AS nAmt, IFNULL(COUNT(o.orderID),0) AS OrdCount, MAX(YEAR(o.startdate1)) AS Yr "
sqlOrders = sqlOrders & " FROM orderdata AS o "
sqlOrders = sqlOrders & " WHERE o.orderid <> '62483' "
sqlOrders = sqlOrders & " AND o.cust <> '38282' "
sqlOrders = sqlOrders & " AND o.cust <> '36034' "
sqlOrders = sqlOrders & " AND o.cust <> '43304' "
sqlOrders = sqlOrders & " AND o.cust <> '46391' "
sqlOrders = sqlOrders & " AND o.billSt IN ("& states &") "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('165%') AND o.billZip NOT LIKE ('16401%') AND o.billZip NOT LIKE ('16403%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16407%') AND o.billZip NOT LIKE ('16410%') AND o.billZip NOT LIKE ('16411%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16412%') AND o.billZip NOT LIKE ('16413%') AND o.billZip NOT LIKE ('16415%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16417%') AND o.billZip NOT LIKE ('16421%') AND o.billZip NOT LIKE ('16423%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16426%') AND o.billZip NOT LIKE ('16427%') AND o.billZip NOT LIKE ('16428%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16430%') AND o.billZip NOT LIKE ('16434%') AND o.billZip NOT LIKE ('16435%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16438%') AND o.billZip NOT LIKE ('16441%') AND o.billZip NOT LIKE ('16442%') "
sqlOrders = sqlOrders & " AND o.billZip NOT LIKE ('16443%') AND o.billZip NOT LIKE ('16444%') AND o.billZip NOT LIKE ('16475%') "
sqlOrders = sqlOrders & " AND o.startdate1 BETWEEN '"& currStartDate &"' AND '"& currEndDate &"' "
sqlOrders = sqlOrders & " GROUP BY St "
sqlOrders = sqlOrders & " ORDER BY St, Yr) AS t2 "
sqlOrders = sqlOrders & "GROUP BY State "
sqlOrders = sqlOrders & "ORDER BY State, Year"
set rsOrders = conn.execute(sqlOrders)
Dim orderState(), orderDollars(), orderCount(), orderYear()
i = 0
While Not rsOrders.eof
Redim preserve orderState(i)
Redim preserve orderDollars(i)
Redim preserve orderCount(i)
Redim preserve orderYear(i)
orderState(i) = CStr(rsOrders("State"))
If (IsNumeric(FormatNumber(rsOrders("netAmt")))) Then
orderDollars(i) = CDbl(FormatNumber(rsOrders("netAmt")))
Else
response.write("<br />Recordset("& (i) &") "& rsOrders("netAmt") &" is not a number and was not added to the array.")
response.write("<br />Field Data type is: "& TypeName(rsOrders("netAmt")) &".")
End If
If (IsNumeric(FormatNumber(rsOrders("OrderCount")))) Then
orderCount(i) = CDbl(FormatNumber(rsOrders("OrderCount")))
Else
response.write("<br />Recordset("& (i) &") "& rsOrders("OrderCount") &" is not a number and was not added to the array.")
response.write("<br />Field Data type is: "& TypeName(rsOrders("OrderCount")) &".")
End If
If (IsNumeric(FormatNumber(rsOrders("Year")))) Then
orderYear(i) = CDbl(FormatNumber(rsOrders("Year")))
Else
response.write("<br />Recordset("& (i) &") "& rsOrders("Year") &" is not a number and was not added to the array.")
response.write("<br />Field Data type is: "& TypeName(rsOrders("Year")) &".")
End If
rsOrders.movenext
i = i + 1
Wend
rsOrders.close
set rsOrders=nothing
conn.close

Does anyone see a glaring problem with any of this code? I cannot find the problem. Any help would be greatly appreciated.

 

[DotNetGnat]

hmm...thats a big query..i would suggest you to do a response.write your query and see if it works in query analyzer...

also Year is a keyword so dont use it as a field name or alias...

-DNG

 

[bdc101]

Hi DNG:

Thanks, I did not catch that keyword. Changing the keyword did not fix the problem though. I ran this query in Navicat against the dB which is MySQL, and it works fine. Any other ideas?

---
BDC

 

[DotNetGnat]

what is the exact error that you are getting...

-DNG

 

[bdc101]

Microsoft VBScript runtime error '800a0009'
Subscript out of range: 'UBound'
/reports/outside-sales-reports/quarterly-report/generate-report.asp, line 552

This line is well below the lines I posted and is simply:

For j = 0 to ubound(orderState)

I ran some response.write commands that indicated the record set was null after the query is run. When I run the exact same query in Navicat it returns the records I am looking for. It has me quite confused.

 

[DotNetGnat]

i dont see the For loop in the code you provided here...

-DNG

 

[bdc101]

Here is the for loop it starts on line 552 and ends on line 560. the query section I displayed earlier is on lines 159-263. The error occurs on the first line I try to access one of the arrays created in the earlier code.

j = 0
For j = 0 to ubound(orderState)
compareYear = CDbl(orderYear(j))
compareState = CStr(orderState(j))
If (CDbl(compareYear) = CDbl(prevEndYear) AND CStr(compareState) = CStr(state(o))) Then
prevYearOrderDollars = CDbl(orderDollars(j))
prevYearOrderCount = CDbl(orderCount(j))
prevYearOrderDollarsTot = prevYearOrderDollarsTot + prevYearOrderDollars
prevYearOrderCountTot = prevYearOrderCountTot + prevYearOrderCount
End If
Next

Hope this helps.

---BDC

 

[Sheco]

Did you use Response.Write to check the value of your counter variable i ?

Also you might try testing one of your arrays with IsArray() to make sure it is actually an array.

 

[bdc101]

Thanks for all the suggestions, and I apologize for not including the info I have gotten from responses already. I have used response.writes to glean the following information:

1. rsOrders.eof is true after I execute the query.
2. rsOrders.recordcount is -1
3. Since the recordset is at eof, the while loop for populating the arrays is never entered. This means the arrays have been declared but have not been populated with data.
4. The connection to the dB is still open.
5. The query and code for getting the quote info and putting it in arrays is working.

The only thing I can conclude at this point is either the connection is causing a problem or the DSN. I cannot understand why either of those would be causing a problem, especially because this is not the first query run in this asp page(query for order info is working.) I am completely stumped. Thanks again for the input.

---

BDC

 

[Tarwn]

Other things that may give you some info:
1) If you have not done so already, run a simple query from an ASP page against that database and make sure you get results - this should confirm that the driver is working correctly (as well as connectivity, etc)
2) Run a simple UNION statement against the MySQL db from an ASP page - while I believe your working on version 4 or 5 of MySQL (if I remember correctly 3 didn't have UNIONs - I had an issue with that at one point), running a UNIONs might make an issue in the driver show up
3) Response.Write your query to the screen than take it apart and run pieces of it at a time to ensure that data is coming back correctly. Break apart the unions and run each query independantly

Optional) Post a Response.Written copy of the SQL statement here, we may see something in the final statement that isn't obvious in it's current form. Additionally we may be able to help you simplify it a little

-T

 

[bdc101]

I am using MySQL 5.0. I have run several simpler queries to test the DSN and conn and they run fine which is why I am so confused. Here is the response.written query:

SELECT i.stateCD AS State, IFNULL(SUM(i.netamt),0) AS netAmt, IFNULL(COUNT(i.inv),0) AS OrderCount, MAX(YEAR(i.dtordrec2)) AS OrderYear FROM invoice AS i WHERE i.inv <> '62483' AND i.cust <> '38282' AND i.cust <> '36034' AND i.cust <> '43304' AND i.cust <> '46391' AND i.stateCD IN ('OH','PA') AND i.ZipCode NOT LIKE ('165%') AND i.ZipCode NOT LIKE ('16401%') AND i.ZipCode NOT LIKE ('16403%') AND i.ZipCode NOT LIKE ('16407%') AND i.ZipCode NOT LIKE ('16410%') AND i.ZipCode NOT LIKE ('16411%') AND i.ZipCode NOT LIKE ('16412%') AND i.ZipCode NOT LIKE ('16413%') AND i.ZipCode NOT LIKE ('16415%') AND i.ZipCode NOT LIKE ('16417%') AND i.ZipCode NOT LIKE ('16421%') AND i.ZipCode NOT LIKE ('16423%') AND i.ZipCode NOT LIKE ('16426%') AND i.ZipCode NOT LIKE ('16427%') AND i.ZipCode NOT LIKE ('16428%') AND i.ZipCode NOT LIKE ('16430%') AND i.ZipCode NOT LIKE ('16434%') AND i.ZipCode NOT LIKE ('16435%') AND i.ZipCode NOT LIKE ('16438%') AND i.ZipCode NOT LIKE ('16441%') AND i.ZipCode NOT LIKE ('16442%') AND i.ZipCode NOT LIKE ('16443%') AND i.ZipCode NOT LIKE ('16444%') AND i.ZipCode NOT LIKE ('16475%') AND i.dtordrec2 BETWEEN '2004-7-01' AND '2004-9-30' GROUP BY State UNION SELECT St AS State, IFNULL(SUM(nAmt),0) AS netAmt, IFNULL(SUM(OrdCount),0) AS OrderCount, Yr AS OrderYear FROM (SELECT i.stateCD AS St, IFNULL(SUM(i.netamt),0) AS nAmt, IFNULL(COUNT(i.inv),0) AS OrdCount, MAX(YEAR(i.dtordrec2)) AS Yr FROM invoice AS i WHERE i.inv <> '62483' AND i.cust <> '38282' AND i.cust <> '36034' AND i.cust <> '43304' AND i.cust <> '46391' AND i.stateCD IN ('OH','PA') AND i.ZipCode NOT LIKE ('165%') AND i.ZipCode NOT LIKE ('16401%') AND i.ZipCode NOT LIKE ('16403%') AND i.ZipCode NOT LIKE ('16407%') AND i.ZipCode NOT LIKE ('16410%') AND i.ZipCode NOT LIKE ('16411%') AND i.ZipCode NOT LIKE ('16412%') AND i.ZipCode NOT LIKE ('16413%') AND i.ZipCode NOT LIKE ('16415%') AND i.ZipCode NOT LIKE ('16417%') AND i.ZipCode NOT LIKE ('16421%') AND i.ZipCode NOT LIKE ('16423%') AND i.ZipCode NOT LIKE ('16426%') AND i.ZipCode NOT LIKE ('16427%') AND i.ZipCode NOT LIKE ('16428%') AND i.ZipCode NOT LIKE ('16430%') AND i.ZipCode NOT LIKE ('16434%') AND i.ZipCode NOT LIKE ('16435%') AND i.ZipCode NOT LIKE ('16438%') AND i.ZipCode NOT LIKE ('16441%') AND i.ZipCode NOT LIKE ('16442%') AND i.ZipCode NOT LIKE ('16443%') AND i.ZipCode NOT LIKE ('16444%') AND i.ZipCode NOT LIKE ('16475%') AND i.dtordrec2 BETWEEN '2005-7-01' AND '2005-9-30' GROUP BY St UNION SELECT o.billSt AS St, IFNULL(SUM(o.totalAMT - o.shipAMT - o.taxAMT),0) AS nAmt, IFNULL(COUNT(o.orderID),0) AS OrdCount, MAX(YEAR(o.startdate1)) AS Yr FROM orderdata AS o WHERE o.orderid <> '62483' AND o.cust <> '38282' AND o.cust <> '36034' AND o.cust <> '43304' AND o.cust <> '46391' AND o.billSt IN ('OH','PA') AND o.billZip NOT LIKE ('165%') AND o.billZip NOT LIKE ('16401%') AND o.billZip NOT LIKE ('16403%') AND o.billZip NOT LIKE ('16407%') AND o.billZip NOT LIKE ('16410%') AND o.billZip NOT LIKE ('16411%') AND o.billZip NOT LIKE ('16412%') AND o.billZip NOT LIKE ('16413%') AND o.billZip NOT LIKE ('16415%') AND o.billZip NOT LIKE ('16417%') AND o.billZip NOT LIKE ('16421%') AND o.billZip NOT LIKE ('16423%') AND o.billZip NOT LIKE ('16426%') AND o.billZip NOT LIKE ('16427%') AND o.billZip NOT LIKE ('16428%') AND o.billZip NOT LIKE ('16430%') AND o.billZip NOT LIKE ('16434%') AND o.billZip NOT LIKE ('16435%') AND o.billZip NOT LIKE ('16438%') AND o.billZip NOT LIKE ('16441%') AND o.billZip NOT LIKE ('16442%') AND o.billZip NOT LIKE ('16443%') AND o.billZip NOT LIKE ('16444%') AND o.billZip NOT LIKE ('16475%') AND o.startdate1 BETWEEN '2005-7-01' AND '2005-9-30' GROUP BY St ORDER BY St, Yr) AS t2 GROUP BY State ORDER BY State, OrderYear

I have copied this to Navicat and ran it against the dB without any problems. This is part of why I am so confused.

Another part of the problem is that this company changed from one application for processing orders to a custom built one, and the data from the old app was never made to fit the new application's database. It is all sitting in tables and that are filled with data errors. Every time a report needs data dated earlier than Jan 17, 2005 I have to use unions and extra conditions designed to catch data errors to get all the information.

Any help on making the query better or solving the problem will be greatly appreciated.

---
BDC