asked on

BOF EOF Error

Hi,
I am developing a website that access's a Microsoft Access database. This error occurs when i try to update the database. This is included as part of a modify. So the form comes up with what was previoulsy in the database for a particular name and then changes are made and it is submitted.

ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

The strange thing is that i am using nearly the exact same code for the add function and that is working fine. It is really annoing me. Any help would be greatly appreciated.

Thanks,
Jonathan

rdivilbiss

Please post you code

screach

ASKER

Hi,
The three pages are modify_access_select.asp, modify_access_form.asp and modify_access_entry.asp. I don't think there is any problem with select just the link between the form and modify entry.

Thanks,

MODIFY_ACCESS_SELECT

<%
'If the session variable is False or does not exsist then redirect the user to the unauthorised user page
If Session("blnIsUserGood_1") = False or IsNull(Session("blnIsUserGood_1")) = True then
      'Redirect to unathorised user page
      Response.Redirect"unauthorised_user_page_modify.htm"
End If
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>modify_access_select</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<h1 align="center">Modify Select</h1>

<%
'Dimension variables
Dim adoCon                   'Holds the Database Connection Object
Dim rsAccess                  'Holds the recordset for the records in the database
Dim strSQL                  'Holds the SQL query for the database
Dim filePath
Dim docHolder

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
filePath = Server.MapPath("database.mdb")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=letmein; DBQ=" & filePath

'Create an ADO recordset object
Set rsAccess = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT DoorAccess.* FROM DoorAccess;"

'Open the recordset with the SQL query
rsAccess.Open strSQL, adoCon
%>

<table width="778" border="1" cellspacing="0" cellpadding="1" bgcolor="#ffffff" background="">
<tr bgcolor="#4060a0">
<th width="118" align="left"><font face="Verdana" color="#ffffff" size="2">NAME </align></th>
<th width="318" align="left"><font face="Verdana" color="#ffffff" size="2">DOOR1      </align>
</th>
<th width="328" align="left"><font face="Verdana" color="#ffffff" size="2">
<div align="center">DOOR2 </align> </div></th>

</tr>
<%
'Loop through the recordset
Do While not rsAccess.EOF
%>
<tr>
      <td bgcolor="#fff7de"><% Response.Write("<a href=""modify_access_form.asp?ID="& rsAccess("ID")&""">")%>
                               <% Response.Write(rsAccess("Name"))%>
                                    <% Response.Write("</a>")%> </td>
                        <td bgcolor="#fff7de"><% Response.Write(rsAccess("Door1"))%></td>
                        <td bgcolor="#fff7de"><% Response.Write(rsAccess("Door2"))%></td>

                                    </tr>

<%
'Loop
      rsAccess.MoveNext
Loop
%>
</table>

<%
'Reset server objects
rsAccess.Close
Set rsAccess = Nothing
Set adoCon = Nothing
%>
<TABLE WIDTH="968" BORDER="0" CELLPADDING="0" CELLSPACING="0" ID="Table5">
                  <TR>

<TD COLSPAN="3" BGCOLOR="#FFFFFF"><img src="./gfx/spacer.gif" width="780" height="56" /></TD>
                  </TR>

                  <TR>

<TD><IMG SRC="./gfx/vbl_02.gif" WIDTH="907" HEIGHT="12" /></TD>
                  </TR>

                  <TR>
                        <TD COLSPAN="3" BGCOLOR="#FFFFFF" WIDTH="968" HEIGHT="42">

                              <TABLE WIDTH="967" BORDER="0" CELLPADDING="0" CELLSPACING="0" ID="Table6">
<tr>
                                          <td width="967" align="middle"><div align="center"><span class="text">
 <span class="text"><a class="surround" href="./main.asp" onmouseover="window.status='Go to start of iButton Site';return true;" onmouseout="window.status='';return true;">Home</a></span>
                   | <a class="surround" href="/main_modify.asp" onmouseover="window.status='Main iButton Website';return true;" onmouseout="window.status='';return true;">Modify Home</a> 
                   | <a class="surround" href="mailto:0128686@student.ul.ie.ie" onmouseover="window.status='Click here to contact WebSite Administrator';return true;" onmouseout="window.status='';return true;">Contact
Administrator</a>  | <a class="surround" href="http://www.iButton.com" onmouseover="window.status='Main iButton Website';return true;" onmouseout="window.status='';return true;">iButton
Information</a>  | <a class="surround" style="cursor:hand" href="#" onclick="this.style.behavior='url(#default#homepage)';this.setHomePage(window.location);" onmouseover="window.status='Make this browser start page';return true;" onmouseout="window.status='';return true;">Make
this my Home Page</a> </span> </div></td>
                                    </tr>
                              </TABLE>
                        </TD>
                  </TR>
            </TABLE>
</body>
</html>

MODIFY_ACCESS_FORM.ASP

<%
'If the session variable is False or does not exsist then redirect the user to the unauthorised user page
If Session("blnIsUserGood_1") = False or IsNull(Session("blnIsUserGood_1")) = True then
      'Redirect to unathorised user page
      Response.Redirect"unauthorised_user_page_modify.htm"
End If
%>

<% 'Dimension variables
Dim adoCon                                      'Holds the Database Connection Object
Dim rsModifyEntry                   'Holds the recordset for the record modified
Dim strSQL                                     'Holds the SQL query to query the database
Dim IngRecordNo                         'Holds the record number to be deleted
Dim filePath

'Read in the Name to be modified
IngRecordNo = CLng(Request.QueryString("ID"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
filePath = Server.MapPath("database.mdb")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=letmein; DBQ=" & filePath

'Create and ADO recordset object
Set rsModifyEntry = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT DoorAccess.* FROM DoorAccess WHERE ID= " & IngRecordNo

'Open the recordset with the SQL query
rsModifyEntry.Open strSQL, adoCon
%>

<html>
<head>
<title>Modify Access</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body bgcolor="#FFFFFF" text="#000000">
<div align="center">
<table width="518" border="0" cellspacing="0" cellpadding="0" align="center">
<tr>
<td align="center"> <h1 align="center"> Modify Access</h1></td>
</tr>
</table>
<p> </p></div>
</head>

<script language="JavaScript" type="text/javascript">

</script>

<body link="#FFFFFF" vlink="#FF00FF">



<table border="0" cellspacing="0" cellpadding="0">
<tr valign="top">

<td><img src="gfx/helpers/spacer.gif" width="30" height="5"></td>

<td>

<table width="54%" border=0 align="center" cellpadding=0 cellspacing=0 bgcolor=#0c2d83>
<tbody>
<tr valign=top align=left>
<td valign=top align=left> </td>
</tr>
<tr valign=top align=left>
<td valign=top align=left bgcolor=#FFFFFF> <table width="99%" height="187" border=0 align="center" cellpadding=5 cellspacing=1 bgcolor=#0c2d83 >
<tbody>
<tr>
<td class=BodyTextBoldWhite bgcolor=#7f9bc7 colspan=2> <div align="center"><b>
MODIFY ACCESS</b> </div></td>
</tr>
                         <form ACTION="modify_access_entry.asp" METHOD="POST" NAME="MyForm" onSubmit="return validate(MyForm)">

              <TR class=TreeSubBlue vAlign=Default align=Default>
       <TD class=TreeSubBlue bgColor=#ffffff width=100><font face="Verdana"><b>Name</b>                         </TD>
       <TD class=TreeSubBlue bgColor=#ffffff width=100><input type ="text" name="Name" maxlength="50" value="<% =rsModifyEntry("Name")%>"> </TD>
       </TR>


       <TR class=TreeSubBlue vAlign=Default align=Default>
       <TD class=TreeSubBlue bgColor=#ffffff width=100><font face="Verdana"><b>Door 1 Serial Number</b>                         </TD>
       <TD class=TreeSubBlue bgColor=#ffffff width=100><input type ="text" name="Door1" maxlength="64" value="<% =rsModifyEntry("Door1")%>"> </TD>
       </TR>

       <TR class=TreeSubBlue vAlign=Default align=Default>
       <TD class=TreeSubBlue bgColor=#ffffff width=100><font face="Verdana"><b>Door 2 Serial Number</b>                         </TD>
       <TD class=TreeSubBlue bgColor=#ffffff width=100><input type ="text" name="Door2" maxlength="64" value="<% =rsModifyEntry("Door2")%>"> </TD>
       </TR>


                   <tr class=TreeSubBlue valign=Default align=Default>
<td class=TreeSubBlue bgcolor=#ffffff colspan=2> <input type="submit" name="submit" value="Submit">
<img src="gfx/helpers/spacer.gif" width="382" height="8">
<input type="reset" name="Reset" value="Reset"> </td>
</tr>
</tbody>
                   </form>
</table></td>
</tr>
</tbody>
</table>

<TABLE WIDTH="968" BORDER="0" CELLPADDING="0" CELLSPACING="0" ID="Table5">
                  <TR>

<TD COLSPAN="3" BGCOLOR="#FFFFFF"><img src="./gfx/spacer.gif" width="780" height="56" /></TD>
                  </TR>

                  <TR>

<TD><IMG SRC="./gfx/vbl_02.gif" WIDTH="907" HEIGHT="12" /></TD>
                  </TR>

                  <TR>
                        <TD COLSPAN="3" BGCOLOR="#FFFFFF" WIDTH="968" HEIGHT="42">

                              <TABLE WIDTH="967" BORDER="0" CELLPADDING="0" CELLSPACING="0" ID="Table6">
<tr>
                                          <td width="967" align="middle"><div align="center"><span class="text">
 <span class="text"><a class="surround" href="./main.asp" onmouseover="window.status='Go to start of iButton Site';return true;" onmouseout="window.status='';return true;">Home</a></span>
                   | <a class="surround" href="/main_modify.asp" onmouseover="window.status='Main iButton Website';return true;" onmouseout="window.status='';return true;">Modify Home</a> 
                   | <a class="surround" href="mailto:0128686@student.ul.ie.ie" onmouseover="window.status='Click here to contact WebSite Administrator';return true;" onmouseout="window.status='';return true;">Contact
Administrator</a>  | <a class="surround" href="http://www.iButton.com" onmouseover="window.status='Main iButton Website';return true;" onmouseout="window.status='';return true;">iButton
Information</a>  | <a class="surround" style="cursor:hand" href="#" onclick="this.style.behavior='url(#default#homepage)';this.setHomePage(window.location);" onmouseover="window.status='Make this browser start page';return true;" onmouseout="window.status='';return true;">Make
this my Home Page</a> </span> </div></td>
                                    </tr>
                              </TABLE>
                        </TD>
                  </TR>
            </TABLE>
</body>
</html>

MODIFY_ACCESS_ENTRY.ASP

<%
'If the session variable is False or does not exsist then redirect the user to the unauthorised user page
If Session("blnIsUserGood_1") = False or IsNull(Session("blnIsUserGood_1")) = True then
      'Redirect to unathorised user page
      Response.Redirect"unauthorised_user_page_modify.htm"
End If
%>

<% 'Dimension variables
Dim adoCon                                      'Holds the Database Connection Object
Dim rsUpdateEntry                   'Holds the recordset for the record to be modified
Dim strSQL                                     'Holds the SQL query to query the database
Dim RecMod                         'Holds the record number to be modified
Dim filePath

'Read in the ID to be Modified
RecMod = CLng(Request.QueryString("ID"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
filePath = Server.MapPath("database.mdb")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=letmein; DBQ=" & filePath

'Create and ADO recordset object
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT DoorAccess.* FROM DoorAccess WHERE ID= " & RecMod

'Set the lock type so that the record is locked by ADO when it is deleted
rsUpdateEntry.LockType = 3

'Open the recordset with the SQl query
rsUpdateEntry.Open strSQL, adoCon

'Add a new record to the recordset
rsUpdateEntry.Fields("Name") = Request.Form("Name")
rsUpdateEntry.Fields("Door1") = Request.Form("Door1")
rsUpdateEntry.Fields("Door2") = Request.Form("Door2")

'Write the updated recordset to the database
rsUpdateEntry.Update

'Reset server objects
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
Set adoCon = Nothing

'Redirect to the modify_access_form.asp page
Response.Redirect "modify_access_form.asp"
%>

ASKER CERTIFIED SOLUTION

rdivilbiss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Samer Chidiac

That's a valid answer rdivilbiss ..

you might also do the Testing just b4 your loop

like

if not rst.eof then

do while not rst.eof

......

rst.movenext
loop

else

response.write "No records"
response.end

end if

Cheers,

SC

----------
Samer Chidiac
Microsoft MVP - ASP / ASP.net

screach

ASKER

I still couldn't find the problem so i instead of using update i just deleted and add a new record and that works fine.
Thanks,
Screach