Avatar of georgehayes3
georgehayes3 asked on

Recordset refresh problem

I have 2 problems...

1. I have a page with a recordset from an MS access database displaying fields in a table in an iframe

in the table is a delete link.  When the delete link is clicked another page opens in the same frame with the record id passed. Everything works fine and the db record is deleted.  I then reopen the original page and the deleted record is still showing in the page.  It appears the recordset or table contents is not refreshing.

here is the code:

<html>
<head>
<%
set asplObj=Server.CreateObject("ASPL.Login5")

'You can protect this page to a number of different users
'asplObj.User("user1")
'asplObj.User("user2")
'asplObj.User("user3")

'Or, you can protect this page to a number of different groups
'asplObj.Group("group1")
'asplObj.Group("group2")
'asplObj.Group("group3")

asplObj.Protect


Dim selectedid
selectedid = Request.Querystring("idpass")

%>
<BODY>

<%
    Dim connStr
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Data\Compliance\FileDB.mdb")
    Set rs = Server.CreateObject("ADODB.Recordset")
    ShowSQL = "SELECT * FROM qryFiles WHERE [ID] =" & selectedid
            
    ' opening connection
    rs.Open ShowSQL, connStr, 3, 4


%>
<BR>
<%response.write "Standard: " & rs("Standard")%>

<%response.write "-" & rs("SubStd")%>
<BR>
<%response.write "File: " & rs("File Name")%>
<BR>
<BR>
<BR>
<%
   rs.Close
   Set rs = Nothing
   
   
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("Data\Compliance\FileDB.mdb")
    Set rs = Server.CreateObject("ADODB.Recordset")
    ShowSQL = "SELECT * FROM qryFiles WHERE [ID] =" & selectedid
            
    ' opening connection
    rs.Open ShowSQL, connStr, 1, 3
    rs.delete
    rs.update
    rs.Close
    Set rs = Nothing
   
   response.write "File Deleted..."
   %>
<BR>
<a href="show.asp?stdpass=1.1.1">Back</a>
</BODY>

2. When the browser is completely shutdown, the ldb file does not go away.  I cant seem to close the connection using connStr.close because the browser then yells at me like this:

Microsoft VBScript runtime error '800a01a8'

Object required: 'Provider=Microsoft.J'

/testpd/Y2008/CFileDel.asp, line 59


if i take it out it works fine.  but the connection doesnt get closed nicely like it should.
Visual Basic ClassicASPVB Script

Avatar of undefined
Last Comment
purplepomegranite

8/22/2022 - Mon
purplepomegranite

Try the following.

You are not specifically declaring a connection object, the Close method won't work on a string.

An advantage in this case of using the connection object is that you can use the same connection for both queries.
<html>
<head>
<%
set asplObj=Server.CreateObject("ASPL.Login5")
 
'You can protect this page to a number of different users
'asplObj.User("user1")
'asplObj.User("user2")
'asplObj.User("user3")
 
'Or, you can protect this page to a number of different groups
'asplObj.Group("group1")
'asplObj.Group("group2")
'asplObj.Group("group3")
 
asplObj.Protect
 
 
Dim selectedid
selectedid = Request.Querystring("idpass")
 
%>
<BODY>
 
<%
    Dim conn, strDB
	 strDB=Server.MapPath("Data\Compliance\FileDB.mdb")
	 set conn=Server.CreateObject("ADODB.Connection")
	 conn.Provider="Microsoft.Jet.OLEDB.4.0"
	 conn.Open strDB
    Set rs = Server.CreateObject("ADODB.Recordset")
    ShowSQL = "SELECT * FROM qryFiles WHERE [ID] =" & selectedid
            
    ' opening connection
    rs.Open ShowSQL, conn, 3, 4
 
 
%>
<BR>
<%response.write "Standard: " & rs("Standard")%>
 
<%response.write "-" & rs("SubStd")%>
<BR>
<%response.write "File: " & rs("File Name")%>
<BR>
<BR>
<BR>
<%
   rs.Close
   Set rs = Nothing
   
   
    Set rs = Server.CreateObject("ADODB.Recordset")
    ShowSQL = "SELECT * FROM qryFiles WHERE [ID] =" & selectedid
            
    ' opening connection
    rs.Open ShowSQL, conn, 1, 3
    rs.delete
    rs.update
    rs.Close
    Set rs = Nothing
	 conn.Close ' Finished with the connection now, so close it
   
   response.write "File Deleted..."
   %>
<BR>
<a href="show.asp?stdpass=1.1.1">Back</a>
</BODY>

Open in new window

ASKER
georgehayes3

part two is solved but

the record still shows when i hit the back button even tho it is deleted
ASKER
georgehayes3

Is the page still in memory? Why doesnt it requery the db?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
purplepomegranite

If you do a <SHIFT> and then press refresh in the browser, is the page refreshed correctly?

It does sound like you are looking at a browser cache of the page.

Try adding the attached header code to your page that displays the recordset (it tells the browser not to cache the page).
<META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE">

Open in new window

purplepomegranite

Wait...
When you hit the BACK button?!

This is definitely in cache!!  However, the header above should overcome this by not allowing the cache - it should be requeried when you hit Back.

This is entirely expected behaviour if you are using the back button though...
ASKER
georgehayes3

no i meant the back link which is shown above, recalls the page using an href.


I will try your suggestion and hopefully it works! THANKS.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
georgehayes3

that did not work... here is the code of the page we start with

when we hit delete iframe changes to a page shows record details and deletes record from database, user then hits back to return to the list of records

the records dont reflect the deleted item.  here is the page we start with


<html>
<head>
<META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE">
 

   <title>Compliance</title>
   <style>
      body, input, td { font-family:verdana,arial; font-size:10pt; }
   </style>
</head>
<script>
function refresh()
{
    window.location.reload();
}
</script>
<script>function goToNewPage(dropdownlist) { var url = dropdownlist.options(dropdownlist.selectedIndex).value; if (url != "") { window.open(url, "content3"); } }</script>

<%
set asplObj=Server.CreateObject("ASPL.Login5")

'You can protect this page to a number of different users
'asplObj.User("user1")
'asplObj.User("user2")
'asplObj.User("user3")

'Or, you can protect this page to a number of different groups
'asplObj.Group("group1")
'asplObj.Group("group2")
'asplObj.Group("group3")

asplObj.Protect

%>
<%
   ' -- show.asp --
   ' Generates a list of uploaded files
   
   Response.Buffer = True
   
   ' Connection String
   Dim connStr
      connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
         Server.MapPath("Data\Compliance\FileDB.mdb")
         
' Build Drop Down
    Set rsStandards = Server.CreateObject("ADODB.Recordset")
    SNsql = "SELECT * FROM qryStandards"
    rsStandards.open SNsql, Application("strConn"), 1, 1
    rsStandards.movefirst
   
    WHILE NOT rsStandards.EOF
    IF rsStandards("Stdid") = "1.1.1" THEN
    myStandard = myStandard & "<option selected value=show.asp?stdpass=" & rsStandards("Stdid") & ">" & rsStandards("Stdid") & "</option>"
    ELSE
    myStandard = myStandard & "<option value=show.asp?stdpass=" & rsStandards("Stdid") & ">" & rsStandards("Stdid") & "</option>"
    END IF

    rsStandards.MoveNext
    WEND
   
    rsStandards.Close
    set rsStandards = Nothing

%>
<body>
   
   <p align="center">
      <b>Showing Files from the Database</b><br><br>
   </p>
   <form name="dropdown" method="POST">
    <p align=center>
    <select name="list">
    <%=myStandard%>
    <select> <input type=button value="Refresh" onclick="goToNewPage(document.dropdown.list)">
    </p>
   </form>
       
   <table width="700" border="1" align="center">
<%
   ' Recordset Object
   Dim rs
      Dim selectedstd
      selectedstd = Request.Querystring("stdpass")

      Set rs = Server.CreateObject("ADODB.Recordset")
      ShowSQL = "select [ID],[Standard],[SubStd],[File Name],[File Size],[Content Type]," & _
            "[First Name],[Last Name],[PDate] from qryFiles WHERE [Standard] ='" & selectedstd & "'"
            
      ' opening connection
      rs.Open ShowSQL, connStr, 3, 4
      rs.requery    
      If Not rs.EOF Then
         Response.Write "<tr><td colspan=""9"" align=""center""><i>"
         Response.Write "No. of records : " & rs.RecordCount
         Response.Write ", Table : Files</i><br>"
         Response.Write "</td></tr>"
   
         While Not rs.EOF
            Response.Write "<tr><td>"
            Response.Write rs("Standard") & "</td><td>"
            Response.Write rs("SubStd") & "</td><td>"
            Response.Write "<a href=""file.asp?ID=" & rs("ID") & """ target='_blank'>"
            Response.Write rs("File Name") & "</a></td><td>"
            Response.Write rs("File Size") & "</td><td>"
            Response.Write rs("Content Type") & "</td><td>"
            Response.Write rs("First Name") & "</td><td>"
            Response.Write rs("Last Name") & "</td><td>"
            Response.Write rs("PDate") & "</td><td>"
            Response.Write "<a href='CFileDel.asp?idpass=" & rs("ID") & "'>Delete</a>"
            Response.Write "</td></tr>"
            rs.MoveNext
         Wend
      Else
         Response.Write("No Record Found for: " & selectedstd)
      End If
     
      rs.Close
      Set rs = Nothing
%>
   </table>
   
</body>
</html>
ASKER
georgehayes3

a few other things to note

Adding a record shows instantly after a refresh

Logging out and logging back in shows the correct information

its definitely cacheing that query info and not refreshing it but only after a delete.

I should have been a pool cleaner.. my pool cleaner makes about 250 bux an hour.
ASKER CERTIFIED SOLUTION
purplepomegranite

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question