?
Solved

Creating an Update Record Form?

Posted on 2004-11-26
11
Medium Priority
?
137 Views
Last Modified: 2010-04-25
Let me tell you the scenerio.  For an intranet site management wants to let employees input referrals they make into a database.  No problem that works fine.  Now management wants to go in to an admin site and "approve" whether a refferal is accepted or not.  I figured the best way to do it is to create a check box in my Access Database and add it to a recordset and build an Update record form.  But I'm getting all kinds of errors that makes me think there is a better way to do this, ... any ideas.  I am willing to try anything, but am pretty new at web building so be simple.
0
Comment
Question by:ibtaya
  • 4
  • 4
  • 2
10 Comments
 
LVL 33

Expert Comment

by:humeniuk
ID: 12682036
I think you're on the right track.  Add that column to your db table - approved, or something like that.  In your initial form, create a hidden field (approved) and give it a value of '0' - not approved.  The employee submits the form and the record is created.  However, it doesn't appear on the site, because you configure your recordset to only shows entries with an value of '1' for the approved variable.

Create your admin section page which is another form populated with your not-yet-approved entries (you can use Repeat Region to show them all).  The management can review the submitted referrals and either accept the referral (which updates the approved value to '1' so that it appears on the site) or reject the entry and delete the record.

If this is essentially what you've done, what errors are you encountering?
0
 
LVL 2

Author Comment

by:ibtaya
ID: 12682419
Two things, I didn't have the hidden field in the submit page.  I can get the admin site to work if I leave the table alone using the Update Record Wizard.  But when I try to rearrange the table it gives me errors.  I'll let you know the errors in a minute.
0
 
LVL 2

Author Comment

by:ibtaya
ID: 12682484
This error comes at the bottom of the screen when I view the site.  It is showing the repeated fields like I hoped it would but this is listed at the bottom..

ADODB.Field error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted.  Requested opration requires a current record.
/incentives/depositadmintest.asp, line 201

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 33

Accepted Solution

by:
humeniuk earned 750 total points
ID: 12682603
The hidden field will input the 'not-approved' status into the 'approved' column of the database.  It's not necessary if you use '0' as the default for that column when no entry is provided.

How are you modifying the form (ie. what modifications cause the errors)?  I would use a Radio Group for this with two buttons -> Approved (value - 1) and Not Approved (value - 2), like this:

    <label><input type="radio" name="approved" value="1">Approved</label><br>
    <label><input type="radio" name="approved" value="0">Not Approved</label>

The Radio Group should be dynamically populated with the values from the 'approved' column in your database/table.  Therefore, it will appear with 'Not Approved' checked.  If the management user changes this to 'Approved' and submits the form, it will update the 'approved' column in the db and the record will appear on the site thereafter.

You may have to post the section of the code that contains the error (line 201) so we can see what the problem is.  I'm not very familiar with ASP (I use PHP), but "requested operation requires a current record" implies that your admin form isn't being populated with data from the database.  
0
 
LVL 2

Author Comment

by:ibtaya
ID: 12682624
In access, should the field be a regular text field or a checkbox.  Or does it matter?  Sorry for my unintelligence.
0
 
LVL 33

Expert Comment

by:humeniuk
ID: 12682684
Hmmm, not 100% sure (as I use MySQL instead of Access), but a text field should work just fine with the form example I gave above.  A checkbox field in Access might require something different in the form.
0
 
LVL 2

Author Comment

by:ibtaya
ID: 12682688
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/inc.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_inc_STRING
  MM_editTable = "IncentivesTable"
  MM_editColumn = "TypeofIncentive"
  MM_recordId = "'" + Request.Form("MM_recordId") + "'"
  MM_editRedirectUrl = "Depositapproved.htm"
  MM_fieldsStr  = "TypeofIncentive|value|EmployeeName|value|CustomerFirstName|value|CustomerLastName|value|Approved|value"
  MM_columnsStr = "TypeofIncentive|',none,''|EmployeeName|',none,''|CustomerFirstName|',none,''|CustomerLastName|',none,''|Approved|none,1,0"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
 
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
%>
<%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  Next
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_inc_STRING
Recordset1.Source = "SELECT EmployeeName, TypeofIncentive, CustomerFirstName, CustomerLastName, Approved FROM IncentivesTable"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<p>&nbsp;</p>

<form method="post" action="<%=MM_editAction%>" name="form1">
  <table width="700" align="center">
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td>&nbsp;      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
    <tr valign="baseline">
      <td nowrap align="right"><input type="checkbox" name="Approved" value=1 >      </td>
      <td>        <input type="text" name="CustomerLastName" value="<%=(Recordset1.Fields.Item("CustomerLastName").Value)%>" size="32">
      </td>
      <td><input type="text" name="CustomerFirstName" value="<%=(Recordset1.Fields.Item("CustomerFirstName").Value)%>" size="32"></td>
      <td><input type="text" name="TypeofIncentive" value="<%=(Recordset1.Fields.Item("TypeofIncentive").Value)%>" size="32"></td>
      <td><input type="text" name="EmployeeName" value="<%=(Recordset1.Fields.Item("EmployeeName").Value)%>" size="32"></td>
    </tr>
    <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend
%>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Update record">
      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
  <input type="hidden" name="MM_update" value="form1">
  <input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("ID").Value %>">
</form>
<p>&nbsp;</p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
0
 
LVL 33

Expert Comment

by:humeniuk
ID: 12683031
Can you specify which is line 201?

Hopefully someone here can help with the ASP.  You may also want to post a pointer question in the ASP TA.
0
 
LVL 1

Assisted Solution

by:triseros
triseros earned 750 total points
ID: 12696467
Your hidden field - MM_recordId isoutside your repeating region which means that when it tries to get the recordset info  forit, it is at the end of the recordset and doesn't exist and therefore returns the error

try this... All I have done is move the MM_recordId hidden field inside the repeat region so that each row has it's own record id (which it needs)

 <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/inc.asp" -->
<%
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
%>
<%
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_inc_STRING
  MM_editTable = "IncentivesTable"
  MM_editColumn = "TypeofIncentive"
  MM_recordId = "'" + Request.Form("MM_recordId") + "'"
  MM_editRedirectUrl = "Depositapproved.htm"
  MM_fieldsStr  = "TypeofIncentive|value|EmployeeName|value|CustomerFirstName|value|CustomerLastName|value|Approved|value"
  MM_columnsStr = "TypeofIncentive|',none,''|EmployeeName|',none,''|CustomerFirstName|',none,''|CustomerLastName|',none,''|Approved|none,1,0"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
 
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  Next

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
    Else
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
%>
<%
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  Next
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_inc_STRING
Recordset1.Source = "SELECT EmployeeName, TypeofIncentive, CustomerFirstName, CustomerLastName, Approved FROM IncentivesTable"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<p>&nbsp;</p>

<form method="post" action="<%=MM_editAction%>" name="form1">
  <table width="700" align="center">
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td>&nbsp;      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
    <tr valign="baseline">
      <td nowrap align="right"><input type="checkbox" name="Approved" value=1 >      </td>
      <td>        <input type="text" name="CustomerLastName" value="<%=(Recordset1.Fields.Item("CustomerLastName").Value)%>" size="32">
      </td>
      <td><input type="text" name="CustomerFirstName" value="<%=(Recordset1.Fields.Item("CustomerFirstName").Value)%>" size="32"></td>
      <td><input type="text" name="TypeofIncentive" value="<%=(Recordset1.Fields.Item("TypeofIncentive").Value)%>" size="32"></td>
      <td><input type="text" name="EmployeeName" value="<%=(Recordset1.Fields.Item("EmployeeName").Value)%>" size="32"></td>
      <input type="hidden" name="MM_recordId" value="<%= Recordset1.Fields.Item("ID").Value %>">
 
    </tr>
    <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Recordset1.MoveNext()
Wend



%>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
        <input type="hidden" name="MM_update" value="form1">
      <td><input type="submit" value="Update record">
      </td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
  </table>
</form>
<p>&nbsp;</p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
0
 
LVL 1

Expert Comment

by:triseros
ID: 13150163
Awaiting confirmation from ibtaya that this has resolved the issue - certainly appeared to in testing environment
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question