Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Unanswered: 80020009 error accessing an Access 2000 database from a classic asp webpage

    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.

    My asp code is posted below:

    Thanks for your assistance in advance.

    ASP code:


    Code:
    <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>
    Last edited by gvee; 09-08-14 at 06:47. Reason: [code] tags added

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Doesn't look like you are checking for the existence of records in your recordset before looping through them?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, you mention you can resolve in your test environment data but don't want to touch live. Can you tell us what you changed to fix?
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •