asked on

Customize database results editor

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.

dtolo

ASKER

Here is an example of 1 of the pages:


<%
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 %>


<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>


<% 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
%>



      <tr>
<td bgcolor="#C8C8D2" align="center">
<%=FP_FieldVal(fp_rs,"EmployeeNumber")%></td>
<td bgcolor="#C8C8D2" align="center">
<%=FP_FieldVal(fp_rs,"FirstName")%></td>
<td bgcolor="#C8C8D2" align="center">
<%=FP_FieldVal(fp_rs,"LastName")%></td>
<td bgcolor="#C8C8D2" align="center">
<%=FP_FieldVal(fp_rs,"ClassName")%></td>
<td bgcolor="#C8C8D2" align="center">
<%=FP_FieldVal(fp_rs,"DaysAndTimes")%></td>
<td bgcolor="#C8C8D2" align="center">
<%=FP_FieldVal(fp_rs,"Email")%></td>

      </tr>


      </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">

</script>


<% 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

%>

<p></p>

</body>

</html>

hhammash

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

dtolo

ASKER

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

rcmb

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

dtolo

ASKER

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.

dtolo

ASKER

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

%>

dtolo

ASKER

Do I have to put like a get form tag on this or something?

hhammash

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

ASKER CERTIFIED SOLUTION

rcmb

membership

This solution is only available to members.

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

Start Free Trial

dtolo

ASKER

Thanks Curtis,

As always you are right, problem solved.

Thank You,
~David

rcmb

:-)