Delete Record via .asp Page

[chridd]

Hi,

I am currently working on a simple program that displays records from a MS Access database. I want the user to be able to delete a individual record if they choose to. I get stuck at the point where I need to delete the record. Here is the code I have so far. Any advice would be appreciated.


<--Code Posted Below-->

<p align="center" style="margin-top: 1px; margin-bottom: 1px">
<font color="#FF0000" face="Arial"><b>Telephone Application Maintenance</b></font></p>
<p align="center" style="margin-top: 1px; margin-bottom: 1px"><b>
<font face="Arial" color="#FF0000" size="1">**Once the account has been deleted
it is not retrievable**</font></b></p>
<p align="center">&nbsp;</p>

<%
SQL = "Select ID, f01, officer, stampdate, custstatus From depapps Order By stampdate"
Set RS = MyConn.Execute(SQL)

Response.Write "<center>"

While Not RS.EOF
Response.Write "<form name=""Update"" method=""Post"">"
Response.Write "<table border=""1"" bgcolor=""#c0c0c0"">"

%>
<div align="center">
<center>
<table border="1" cellpadding="3" width="604">
<tr>
<td width="121" align="left"><b><font face="Arial" size="2">Customer Name</font></b></td>
<td width="122" align="left"><b><font face="Arial" size="2">Account Type</font></b></td>
<td width="99" align="left"><b><font face="Arial" size="2">Opened By</font></b></td>
<td width="99" align="left"><b><font size="2" face="Arial">Date Opened</font></b></td>
<td width="98" align="left"><b><font size="2" face="Arial">Action</font></b></td>
</tr>
<tr>
<td width="121"><input type="hidden" name="id" value="<%=RS("ID")%>"></td>
<td width="122"></td>
<td width="99"></td>
<td width="99">&nbsp;</td>
<td width="98"></td>
</tr>
<tr>
<td width="121">
<input type="text" name="ca01" size="20" value="<%=RS("f01")%>"></td>
<td width="122">
<input type="text" name="ca07" size="20" value="<%=RS("custstatus")%>"></td>
<td width="99">
<input type="text" name="appdate" size="10" value="<%=RS("officer")%>"></td>
<td width="99">
<input type="text" name="appdate" size="10" value="<%=RS("stampdate")%>"></td>
<td width="98">
<div align="center">
<table border="0" cellpadding="0" cellspacing="0" width="95%">
<tr>
<td width="100%"><input type="submit" value="Delete" (((((**HERE IS MY PROBLEM** onClick="this.form.action='do something.asp?method=Delete';"></td>)))))
</tr>
</table>

 

[damber]

Why not just use a simple query string generated from ASP...

response.write "<a href=""doSomething.asp?method=delete&id=" & RS("ID") & """>Delete me</a>"

This can then be created instead of the submit button - (you can use a button/image instead of text if you like)

The target page could then read this var and delete as appropriate.

If you still want to use the form route, then post some details on the error - i.e. does it give you an error message.. if so what is it ?

A smile is worth a thousand kind words. So smile, it's easy!

 

[chridd]

Damber:

I tried the code that you sent me and I tested it and for some reason It does not delete the recor. I don't receive any errors, it just refreshes the page with the same record I tried to delete.. Did I do something wrong?? Below is the syntax I used.

<%
SQL = "Select ID, f01, officer, stampdate, custstatus From depapps1 Order By F01"
Set RS = MyConn.Execute(SQL)

While Not RS.EOF
'Response.Write "<form name=""Update"" method=""Post"">"
'Response.Write "<table border=""1"" bgcolor=""#c0c0c0"">"

%>
<div align="center">
<center>
<table border="1" cellpadding="3" width="628">
<tr>
<td width="121" valign="bottom" bgcolor="#808080"><b><font face="Arial" size="2">Customer Name</font></b></td>
<td width="122" valign="bottom" bgcolor="#808080"><b><font face="Arial" size="2">Account Type</font></b></td>
<td width="99" valign="bottom" bgcolor="#808080"><b><font face="Arial" size="2">Opened By</font></b></td>
<td width="99" valign="bottom" bgcolor="#808080"><b><font size="2" face="Arial">Date Opened</font></b></td>
<td width="98" valign="bottom" bgcolor="#808080"><b><font size="2" face="Arial">Action</font></b></td>
</tr>
<tr>
<td width="121" valign="bottom">
&nbsp;<input type="hidden" name="id" value="<%=RS("ID")%>"></td>
<td width="122" valign="bottom">
&nbsp;</td>
<td width="99" valign="bottom">
&nbsp;</td>
<td width="99" valign="bottom">
&nbsp;</td>
<td width="98" valign="bottom">
&nbsp;</td>
</tr>
<tr>
<td width="121" valign="bottom">
<input type="text" name="ca01" size="20" value="<%=RS("f01")%>"></td>
<td width="122" valign="bottom">
<input type="text" name="ca07" size="20" value="<%=RS("custstatus")%>"></td>
<td width="99" valign="bottom">
<input type="text" name="appdate" size="10" value="<%=RS("officer")%>"></td>
<td width="99" valign="bottom">
<input type="text" name="appdate" size="10" value="<%=RS("stampdate")%>"></td>
<td width="98" valign="bottom">
<div align="center">
<%Response.write "<a href=""DELAPPS2.asp?method=delete & ID=" & RS("ID") & """>Delete</a>"%>

<table border="0" cellpadding="0" cellspacing="0" width="95%">
<tr>
<td width="100%" align="right" valign="bottom">&nbsp;</td>

</tr>
</table>
</div>
</td>
</tr>
</table>
</center>
</div>
<%
Response.Write "</table>"
'Response.Write "</form>"
RS.MoveNext
Wend

Response.Write "</center>"

CleanUp(RS)

 

[damber]

That was just to send the request to a page that would deal with the deletion.

You need to check for this querystring value in the target page and run the appropriate SQL

  if request.querystring("method") = "delete" then
     'In this next line you need to sanitise the user input
     ' A very simple example would be: replace(sID,"'","")
     ' This is for security reasons.. search google for "SQL Injection" - you'll find a lot of examples
     sID = request.querystring("id")
     sSQL = "DELETE FROM depapps1 WHERE ID = " & sID
     'The following line depends on how you've setup your connection.. but the idea is just to execute the SQL without returning a recordset.
     MyConn.Execute sSQL
  end if

  'Continue with code for rest of the page

This is written on the fly and is not cut and paste material, as there are certain details you omit from your post. Though it should be an easy enough example for you to identify what the logic is.

Oh, and you need to remove the spaces before and after the & in the line:
<%Response.write "<a href=""DELAPPS2.asp?method=delete & ID=" & RS("ID") & """>Delete</a>"%>
It Should read:
<%Response.write "<a href=""DELAPPS2.asp?method=delete&ID=" & RS("ID") & """>Delete</a>"%>

You should notice the url in the address bar changes to reflect this query string, and if you run the select statement after the delete you will find it reflects the changes in the list of records.

Hope this helps.

A smile is worth a thousand kind words. So smile, it's easy!