UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error 80020009 Accesing An Access 2000 Database From A Classic Asp Webpage    
 
   
jaysfan77
post Jun 3 2014, 10:56 AM
Post#1



Posts: 3
Joined: 3-June 14



I am running a query from a classic asp webpage to a Microsoft Access 2000 database table to return a query of names and to perform a calculation. The database has been running for years, and all queries in the past have been succesful. All of a sudden the past few months, some of the names and calculations are displayed, the following error appears: error 80020009. On the bottom of the displayed query, a person's name is displayed, but the calculation field is not displayed; it is blank. The calculation feild is using a count option in classic asp, and is a text data type in Microsoft Access. As the error message never appeared until the last few months, this is very strange. I was able to find the records in Microsoft Access table (in my development environment) which was causing the errors for a specific month, but I don't want to have to delete the records in a production environment. If I run a SQL query in Microsoft Access, all of the records in a given month in the table (where the issues apepars) are fine, and no errors appear. I have exported and imported the Access table, but the 8002009 error still appears.
Has anyone experienced an issue like this? Does it sound like a classic asp coding issue, or an issue with the Microsoft Access 2000 database table itself.
I will post the asp classic code below.
Thanks for your assistance in advance.
<div>
<br />
<h1 class="main">CO Report</h1>
<hr class="main">
<blockquote>
<form method="POST" action="os.asp" name="form2">
<p>Month to Query:&nbsp;
<select size="1" name="ddmonth">
<%
dim i
for i = 1 to 12
if i = month(date) then
%> <option selected value="<%=i%>"><%=monthname(i)%></option>
<% 'select the current month
else
%> <option value="<%=i%>"><%=monthname(i)%></option>
<%
end if
next
%></select> <select size="1" name="ddyear">
<%
dim intyear
for intyear = 2001 to year(date)
response.write ("<option selected>" & intyear & "</option>")
next
%></select>&nbsp;&nbsp; <input type="submit" value="Query CS" name="btnQUERY"></p>
</form>
<hr /><br />
<%
if request.form("btnQUERY") <> "" then
'display the table based on the selected month and year
'setup query
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\store\stats\data\pros.mdb"
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open strConn
'open the recordset
Set rstData = Server.CreateObject("ADODB.Recordset")
rstDATA.open "SELECT * FROM [tv] ORDER BY toc", cnn, adOpenKeyset
%>
<table border="1" width="100%" id="AutoNumber2" height="18" cellspacing="1">
<caption>CU-Out Results for <%=monthname(Request.form("ddmonth"))%>, <%=request.form("ddyear")%></caption>
<tr>
<th>Officer's Name</th>
<th>Minutes Achieved - CUW</th>
<th>Net Minutes Worked (hours worked - exception)</th>
<th>% of Productivity</th>
</tr>
<% 'set up date values
dim year2, day2, month2
year2 = Trim(Request.form("ddyear"))
month2 = Trim(Request.form("ddmonth"))
'open up the cuot data for the month
Set rstData1 = Server.CreateObject("ADODB.Recordset") 'cuot
'open up the table to grab the names of those who entered data during that month selected
rstDATA1.open "SELECT DISTINCT [name] FROM cuot WHERE month(date)= '" & month2 & "' and year(date) ='" & year2 & "' ORDER BY [name]", cnn, adOpenKeyset
Set rstData3 = Server.CreateObject("ADODB.Recordset") 'hours worked
Set rstData4 = Server.CreateObject("ADODB.Recordset") 'exception
do until rstDATA1.eof = true
%><tr>
<td><%=rstDATA1("name")%>&nbsp;</td>
<% 'do the math calculations for minute achieved
Set rstData0 = Server.CreateObject("ADODB.Recordset") 'cuot
rstDATA0.open "SELECT count(toc) as COUNT1, toc FROM cuot WHERE [name] = '" & replace(rstDATA1("name"), "'", "''") & "' and month(date)= '" & month2 & "' and year(date) ='" & year2 & "' GROUP BY toc ORDER BY toc", cnn, adOpenKeyset
rstDATA.movefirst 'tv table to get "minutes achieved"
rstDATA0.movefirst 'cuot list
CUachieved = 0
do until rstDATA0.eof = true
'loop through rstDATA.toc until match found for rstDATA0.toc
do until trim(rstDATA("toc")) = trim(rstDATA0("toc"))
rstDATA.movenext
loop
if rstDATA("per_day") <> "0" then CUachieved = cDBL(CUachieved) + (cDBL(rstDATA0("COUNT1"))/cDBL(rstDATA("per_day"))*450)
rstDATA.movenext
rstDATA0.movenext
loop
%><td><%=round(CUachieved)%>&nbsp;</td>
<% 'open up the hoursworked table
rstDATA3.open "Select * FROM monthlyhr WHERE [name] = '" & replace(rstDATA1("name"), "'", "''") & "' and month(date) ='" & month2 & "' and year(date) = '" & year2 & "'", cnn, adOpenKeyset
'should only ever be one record, so only check if there are no records
if rstDATA3.eof = true then
%><td>N/A</td>
<% 'net minutes worked
%><td>N/A</td>
<% '% of productivity
else
'open up the exception table
'get approved exception
rstDATA4.open "Select [name], sum(approval) as SUM1 from [exception] where [name] = '" & replace(rstDATA1("name"), "'", "''") & "' and month(date) ='" & month2 & "' and year(date) = '" & year2 & "' GROUP BY [name]", cnn, adOpenKeyset
if rstDATA4.eof = true then
exception2 = 0
else
exception2 = rstDATA4("sum1")
end if
rstDATA4.close
'do the math for actual (hours worked + overtime - leave -exception)
hoursworked2 = (cDBL(rstDATA3("hours")) + cDBL(rstDATA3("ot")) - cDBL(rstDATA3("leave")) - cDBL(exception2))
%><td><%=hoursworked2*60%>&nbsp;</td>
<%
'calculate percentage of csu+ops/minutes worked
percentTOTAL = ROUND(cDBL(CUachieved)/(cDBL(hoursworked2)*60)*100) 'percent
%><td><%=percentTOTAL%>&nbsp;</td>
</tr>
<tr>
<%
end if
rstDATA1.movenext
rstDATA3.close
loop
rstDATA.close 'close all databases
set rstDATA = nothing
rstDATA1.close
set rstDATA1 = nothing
set rstDATA3 = nothing
set rstDATA4 = nothing
cnn.close
set cnn=nothing
%>
</tr>
</table>
<%
end if 'end query%>
</blockquote>
</div>
<!-- InstanceEndEditable --></div>
<!-- End of div.blackborder -->
</div>
Go to the top of the page
 
Doug Steele
post Jun 3 2014, 06:54 PM
Post#2


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Both the Month and Year functions return numeric values, not text. Try removing the quotes.
lternatively, try something like this (since the Format function returns a string):
CODE
"SELECT DISTINCT [name] FROM cuot WHERE Format(date, ""mmyyyy"")= '" & month2 & year2 & "' ORDER BY [name]"

You might consider renaming some of your fields. Name and Date are both reserved words, and using reserved words can lead to problems. For a comprehensive list of names to avoid (as well as a link to a free utility to check your application for compliance), check what Allen Browne has at Problem names and reserved words in Access
Go to the top of the page
 
jaysfan77
post Jun 4 2014, 08:00 AM
Post#3



Posts: 3
Joined: 3-June 14



Thank you for your suggestions. I will give this a try. Appreciate the help.
Go to the top of the page
 
jaysfan77
post Jun 12 2014, 10:01 AM
Post#4



Posts: 3
Joined: 3-June 14



I was able to resolve the Microsoft Access error message. In my coding, there is a connection to a 2nd database which lists headings. A few months ago I created new headings, and because I wanted them to appear all the top of the row, I indented them by adding a space. Once I removed the sapce for all of the new headings, the error message for the past few months disappeared, and the reports displayed perfectly.
Thank you to everyone for your suggestions.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 06:24 AM