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