Solved

Customize database results editor

Posted on 2004-10-18
11
151 Views
Last Modified: 2013-12-24
Hello,

I have been customizing the database results editor somewhat, but I am getting a little lost with this one.  The delete piece, my database is based on employeenumber, when I delete a record with a particular employee number all the records that reference that employee Number get deleted.  I want it to just delete that one record only.  this can happen from a few different pages.
0
Comment
Question by:dtolo
  • 6
  • 3
  • 2
11 Comments
 
LVL 2

Author Comment

by:dtolo
Comment Utility
Here is an example of 1 of the pages:

<!--#include File='login.asa'-->
<%
    If Session(SiteID) <> true Then
        Response.Redirect("Login.asp?requester=delete.asp")
    End If
%>


<% Response.Buffer = True %>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Register -- Delete</title>
<meta name="GENERATOR" content="Microsoft FrontPage 6.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body bgcolor="#0454AC">

<%    If Request("btnAction") = "New" Then
       Response.Redirect ""
    End If
%>
<%    If Request.QueryString("FP_Num_Items") = "" Then
      fp_sMyQry = ""      
            If Request.Form("EmployeeNumber") <> "" Then
                  nCount = 1
                  fp_sMyQry = "&Row0=" & Server.UrlEncode(Request.Form("EmployeeNumber"))
              else
              nCount = 0
              For Each Field In Request.Form
            If Request.Form(Field) = "ON" Then
                    fp_sMyQry = fp_sMyQry & "&Row" & nCount & "='" & Field & "'"
               
                nCount = nCount + 1
            End If
        Next 'Field
      End If
        If Len(fp_sMyQry) > 0 Then
            sRedirect = "delete.asp?FP_Num_Items=" & nCount & fp_sMyQry
            Response.Redirect sRedirect
        End If
    End If
%>

<% if Len(Request("ConfirmDelete")) = 0 then %>

<!--webbot bot="PurpleText" PREVIEW="-Important-  If you modify this Database Results region using the Database Results Wizard, then your Database Editor will no longer work.  If you accidentally open the Database Results Wizard, simply click Cancel to exit without regenerating the Database Results region." -->
<table border="0">
      <thead>
      <tr>
<td bgcolor="black" align="center"><font color="white" size="2"><b>EmployeeNumber</b></font></td>
<td bgcolor="black" align="center"><font color="white" size="2"><b>FirstName</b></font></td>
<td bgcolor="black" align="center"><font color="white" size="2"><b>LastName</b></font></td>
<td bgcolor="black" align="center"><font color="white" size="2"><b>ClassName</b></font></td>
<td bgcolor="black" align="center"><font color="white" size="2"><b>DaysAndTimes</b></font></td>
<td bgcolor="black" align="center"><font color="white" size="2"><b>Email</b></font></td>

      </tr>
      <thead>

      <tbody>
      
<!--#include file="../../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Register WHERE EmployeeNumber = ::Row0:: "
fp_iItems = CInt(Request("FP_Num_Items"))
fp_iTemp = 1
do while(fp_iTemp < fp_iItems)
    fp_sQry = fp_sQry & " OR EmployeeNumber = ::Row" & fp_iTemp & "::"
    fp_iTemp = fp_iTemp + 1
loop
fp_sNoRecords="<tr><td colspan=4 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="TA-UPDATE"
fp_iMaxRecords=256
fp_iCommandType=1
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sColTypes="&EmployeeNumber=200&FirstName=202&LastName=202&ClassName=202&DaysAndTimes=135&Email=202"
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>



<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->

      <tr>
<td bgcolor="#C8C8D2" align="center">
<!--webbot bot="DatabaseResultColumn" s-columnnames="EmployeeNumber,FirstName,LastName,ClassName,DaysAndTimes,Email" s-column="EmployeeNumber" b-tableformat="TRUE" b-hasHTML="FALSE" clientside startspan b-makelink b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;EmployeeNumber&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"EmployeeNumber")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="41846" --></td>
<td bgcolor="#C8C8D2" align="center">
<!--webbot bot="DatabaseResultColumn" s-columnnames="EmployeeNumber,FirstName,LastName,ClassName,DaysAndTimes,Email" s-column="FirstName" b-tableformat="TRUE" b-hasHTML="FALSE" clientside startspan b-makelink b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;FirstName&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"FirstName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="18712" --></td>
<td bgcolor="#C8C8D2" align="center">
<!--webbot bot="DatabaseResultColumn" s-columnnames="EmployeeNumber,FirstName,LastName,ClassName,DaysAndTimes,Email" s-column="LastName" b-tableformat="TRUE" b-hasHTML="FALSE" clientside startspan b-makelink b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LastName&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"LastName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14417" --></td>
<td bgcolor="#C8C8D2" align="center">
<!--webbot bot="DatabaseResultColumn" s-columnnames="EmployeeNumber,FirstName,LastName,ClassName,DaysAndTimes,Email" s-column="ClassName" b-tableformat="TRUE" b-hasHTML="FALSE" clientside startspan b-makelink b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ClassName&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"ClassName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="18883" --></td>
<td bgcolor="#C8C8D2" align="center">
<!--webbot bot="DatabaseResultColumn" s-columnnames="EmployeeNumber,FirstName,LastName,ClassName,DaysAndTimes,Email" s-column="DaysAndTimes" b-tableformat="TRUE" b-hasHTML="FALSE" clientside startspan b-makelink b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;DaysAndTimes&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"DaysAndTimes")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="29731" --></td>
<td bgcolor="#C8C8D2" align="center">
<!--webbot bot="DatabaseResultColumn" s-columnnames="EmployeeNumber,FirstName,LastName,ClassName,DaysAndTimes,Email" s-column="Email" b-tableformat="TRUE" b-hasHTML="FALSE" clientside startspan b-makelink b-MenuFormat preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Email&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"Email")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="8328" --></td>

      </tr>
      
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
      </tbody>
</table>

<center>
<form method="POST">      
        <%select case fp_iCount %>
             <%case 0:%>
            <p><b><font size="4"><span style="background-color: #C8C8D2">Click OK to refresh.</span></font></b></p>
            <p><input type="submit" value="OK" name="ConfirmDelete"></p>
        <%case 1:%>
            <p><b><font size="4"><span style="background-color: #C8C8D2">Are you sure you want to delete this record?</span></font></b></p>
            <p><input type="submit" value="     OK     " name="ConfirmDelete"> <input type="submit" value="Cancel" name="ConfirmDelete"></p>
        <%case else:%>
            <p><b><font size="4"><span style="background-color: #C8C8D2">Are you sure you want to delete these records?</span></font></b><span style="background-color: #C8C8D2">
                  </span>
            <p><input type="submit" value="     OK     " name="ConfirmDelete"> <input type="submit" value="Cancel" name="ConfirmDelete"></p>
        <%end select%></form>

</center>

<%
        Response.end
    ElseIf Request("ConfirmDelete") = "Cancel" Then
        Response.Clear
        Response.Redirect "detail.asp"
    End If
%>

<script Language="JavaScript">
<!--
      top.list.location.href = top.list.location.href;
// -->
</script>





<!--#include file="../../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="DELETE FROM Register WHERE EmployeeNumber = ::Row0:: "
fp_iItems = CInt(Request("FP_Num_Items"))
fp_iTemp = 1
do while(fp_iTemp < fp_iItems)
    fp_sQry = fp_sQry & " OR EmployeeNumber = ::Row" & fp_iTemp & "::"
    fp_iTemp = fp_iTemp + 1
loop
fp_sDefault=""
fp_sNoRecords="Record deleted from table."
fp_sDataConn="TA-UPDATE"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&EmployeeNumber=200&FirstName=202&LastName=202&ClassName=202&DaysAndTimes=135&Email=202"
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID

%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<p><!--webbot bot="PurpleText" PREVIEW="This is the DELETE query." --></p>
<!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
</body>

</html>
0
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
Hi,

In your Access Database, you have a relation between the table that has EmployeeID and another table or tables which have other details for the employee.  It is like Employees, Vacations, Reports. Vacations and Reports have details related to that Employee and the relation has refrential integrity with Cascade Delete and Update.

When you delete an EmployeeID,  all related records and details on the other tables get deleted.  If you want to delete the EmployeeID without deleting everything related to it.  Just go to Access database and remove the relation.

I think this way you will be able to delete the EmployeeID Record in that table only.

Regards
hhammash
0
 
LVL 2

Author Comment

by:dtolo
Comment Utility
hhammash,

It is sql and I need the relationships to enforce data integrity.  It is actually only deleteing the records from one table. Employee Number is not the primary key. the primary key is regid.  I think it has to be in the code, no?  maybe somwere around here?

<%    If Request.QueryString("FP_Num_Items") = "" Then
      fp_sMyQry = ""      
            If Request.Form("EmployeeNumber") <> "" Then
                  nCount = 1
                  fp_sMyQry = "&Row0=" & Server.UrlEncode(Request.Form("EmployeeNumber"))
              else
              nCount = 0
              For Each Field In Request.Form
            If Request.Form(Field) = "ON" Then
                    fp_sMyQry = fp_sMyQry & "&Row" & nCount & "='" & Field & "'"
               
                nCount = nCount + 1
            End If
        Next 'Field
      End If
        If Len(fp_sMyQry) > 0 Then
            sRedirect = "delete.asp?FP_Num_Items=" & nCount & fp_sMyQry
            Response.Redirect sRedirect
        End If
    End If
0
 
LVL 12

Expert Comment

by:rcmb
Comment Utility
From what I can see you are just using the standard wizard that comes with FrontPage. What I recommend is that you just use your own page. All you will need to do is create a link from your display to a confirm page and then have the confirm page ask if you desire to delete the record. If you select yes then the record will get deleted and if you say no if will pass you back to the previous page. I avoid creating pages that allow me to delete multiple records at once because it invites problems.

From what I remember you are not using Access correct?

First thing is create a page to display the records from the database.
Now pick something in the display (like last name or employeeID) and create a hyperlink on that field. When creating the hyperlink insert a parameter to EmployeeID. Have the hyperlink point to a page called confirm_delete.asp

You hyperlink will look like: confirm_delete.asp?EmployeeID=<%=fp_rs("EmployeeID")%>


Now create another page to display a single record from a database. In criteria select EmployeeID = EmployeeID. Select paragraph or table view for displaying one record at a time.

Now you can create a hyperlink to delete_member.asp?EmployeedID=<%=fp_rs("EmployeeID")%>

I will create a few pages and post them here. So you can get a better understanding of what to do.

Curtis
0
 
LVL 2

Author Comment

by:dtolo
Comment Utility
Curtis the previous page has a submit button, so I don't think I need to do it in the url, one issue is that employeeNumber may show up a few times so how do you know you got the right one.  How about this

<td>
      <form method="POST" action="delete.asp">
      <font face="Tahoma">
      <input type="hidden" name="EmployeeNumber" value="'<%=FP_FieldHTML(fp_rs,"EmployeeNumber")%>'">
      <input type="hidden" name="DaysAndTimes" value="'<%=FP_FieldHTML(fp_rs,"DaysAndTime")%>'">
<input type="hidden" name="ClassName" value="'<%=FP_FieldHTML(fp_rs,"ClassName")%>'">

      </font>
      <p><font face="Tahoma"><input type="submit" value="Delete" name="B1"></font></p>

this page posts to the page your creating.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:dtolo
Comment Utility
Then I have a delete query with something like:

<%
fp_sQry="DELETE FROM Register WHERE EmployeeNumber = ::EmployeeNumber:: & DaysAndTimes = ::DaysAndTimes:: & Classname = ::ClassName:: "
fp_iItems = CInt(Request("FP_Num_Items"))
fp_sNoRecords="Record deleted from table."
fp_sDataConn="TA-UPDATE"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&EmployeeNumber=200&FirstName=202&LastName=202&ClassName=202&DaysAndTimes=135&Email=202"
fp_fCustomQuery=True
BOTID=1
fp_iRegion=BOTID

%>
0
 
LVL 2

Author Comment

by:dtolo
Comment Utility
Do I have to put like a get form tag on this or something?
0
 
LVL 14

Expert Comment

by:hhammash
Comment Utility
Hi,

This is how you make cascade delete in sql. This will delete the record from all tables when it is there and share the same id.

http://www.sqlteam.com/item.asp?ItemID=8595

The example in the page is what you need.

Regards
hhammash
0
 
LVL 12

Accepted Solution

by:
rcmb earned 500 total points
Comment Utility
David,

One of the issues you may be facing is that you are passing the employee number vice the recordid. If you pass the employee number to the delete query it deletes everything associated with that employee number. If you pass the recordid it will then just delete that record.

As always if you need more help just let me know.

Curtis
0
 
LVL 2

Author Comment

by:dtolo
Comment Utility
Thanks Curtis,

As always you are right, problem solved.

Thank You,
~David
0
 
LVL 12

Expert Comment

by:rcmb
Comment Utility
:-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now