[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to delete a record on results page

Posted on 2011-03-24
12
Medium Priority
?
457 Views
Last Modified: 2012-05-11
I have attached a results page where the search page contains the names of employees and report dates.  After a name and date is selected, the user is redirected to the attached page.

The attached page is working without issue. However, I want to add a DELETE RECORD button where if the viewer wants to delete the record, they have the ability to do so.

How can this be accomplished? Please review the code and make any suggested adjustments as needed.

Thank you.

<%@LANGUAGE="VBSCRIPT"%>
<%
Dim rs
Dim rs_numRows

pID=Request.Querystring("ID")
pIDX=request.form("IDX")

Set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

If Request.Form("ReportDate2")&"" = "" then
rs.Source = "SELECT * FROM TPACM WHERE Employee = '" & Replace(Request.Form("Employee"),"'","''") & "' " & pID
else
rs.Source = "SELECT * FROM TPACM WHERE ReportDate = #" & Request.Form("ReportDate2") & "# AND Employee = '" & Replace(Request.Form("Employee"),"'","''") & "' " & pID
End If

rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0

if request("Update") = "Update" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

sql=""
If Request.Form("up_Manager") & "" <> "" Then
  sql=sql & ",Manager='" & Request.Form("up_Manager") & "'"
End IF
If Request.Form("up_reportdate") & "" <> "" Then
  sql=sql & ",ReportDate='" & Request.Form("up_reportdate") & "'"
End IF
If Request.Form("up_docket") & "" <> "" Then
  sql=sql & ",Docket='" & Request.Form("up_docket") & "'"
End IF
If Request.Form("up_claims") & "" <> "" Then
sql=sql & ",Claims='" & Request.Form("up_claims") & "'"
End IF
If Request.Form("up_processed") & "" <> "" Then
sql=sql & ",Processed='" & Request.Form("up_processed") & "'"
End IF
If Request.Form("up_paper") & "" <> "" Then
sql=sql & ",Paper='" & Request.Form("up_paper") & "'"
End IF
If Request.Form("AddNote2") & "" <> "" Then
sql=sql & ",AddNote2='" & Request.Form("AddNote2") & "'"
End IF
If Request.Form("Note2") & "" <> "" Then
sql=sql & ",Note2='" & Request.Form("Note2") & "'"
End IF

sql="UPDATE TPACM SET " & Mid(sql, 2, 10000)  ' leave out first comma
sql=sql & " WHERE ID = " & pIDX& "" 
response.write sql 
rs.Execute sql
       
response.redirect "thankyou1.htm"   

end if

%>

<html>
<head>
<title>Review Area</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../styles/stylesheet.css" rel="stylesheet" type="text/css">
<script language="javascript" type="text/javascript" src="../scripts/additional2.js"></script>
<script language="javascript" type="text/javascript" src="../scripts/datetimepicker.js"></script>
</head>

<body bgcolor="#EFEFEF" leftmargin="0" rightmargin="0" topmargin="0">
<div align="center">
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td height="60" bgcolor="#003366">&nbsp; <span class="text-large"><strong><font color="#FFFFFF"> 
        REVIEW AREA</font></strong></span></td>
    </tr>
  </table>
  <table width="560" border="0" align="left" cellpadding="0" cellspacing="0" class="text-medium">
    <tr>
      <td width="10">&nbsp;</td>
      <td><table width="550" border="0" align="left" cellpadding="0" cellspacing="0" class="text-medium">
          <tr> 
            <td width="10">&nbsp;</td>
            <td>&nbsp;</td>
          </tr>
          <tr> 
            <td>&nbsp;</td>
            <td><% If Not rs.EOF Or Not rs.BOF Then %>
              <form name="form2" method="post" action="">
                <table width="550" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                  <tr> 
                    <td height="30" valign="bottom"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="125" valign="bottom" class="text-medium"><strong>Employee 
                            : </strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Employee").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="300" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="30" valign="bottom"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="125" valign="bottom" class="text-medium"><strong>Manager 
                            : </strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Manager").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"> <table width="650" height="40" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="125" valign="bottom"><font color="#333333"> 
                            <select name="up_Manager" class="box-grey" id="up_Manager">
                              <option value="">No Change</option>
                              <option value="JK">Knoxville</option>
                              <option value="KM">May</option>
                              <option value="DM">Mattingly</option>
                              <option value="DA">Adams</option>
                              <option value="MW">Williamson</option>
                            </select>
                            </font></td>
                          <td valign="bottom"><font color="#333333"><span class="text-xsmall">If 
                            adjustment/change is needed, click on menu</span></font></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="300" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="30" valign="bottom"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="125" valign="bottom"><span class="text-medium"><strong>Report 
                            Date </strong></span><strong>:</strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("ReportDate").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"> <table width="550" height="50" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="125" valign="bottom" class="text-xsmall"> 
                            <input name="up_reportdate" type="text" class="box-grey-date" id="up_reportdate" readonly="readonly" size="8"> 
                            <font color="#666666"><a href="javascript:NewCal('up_reportdate','mmddyyyy')"><img src="../images/cal.gif" width="16" height="16" border="0" align="bottom"></a>&nbsp; 
                            </font></td>
                          <td valign="bottom" class="text-xsmall"><font color="#333333"><span class="text-xsmall">If 
                            adjustment/change is needed, click on calendar</span></font></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="300" size="1"></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"> <table width="550" height="30" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="305" valign="bottom" class="text-medium"><strong>Last 
                            Completed Activity Report<font color="#333333"> :</font></strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Docket").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="30"><table width="550" height="50" border="0" cellpadding="0" cellspacing="0" class="box-grey-date">
                        <tr> 
                          <td width="125" valign="bottom" class="text-small"><span class="text-xsmall"> 
                            <input name="up_docket" type="text" class="box-grey-date" id="up_docket" size="8">
                            <font color="#666666"><a href="javascript:NewCal('up_docket','mmddyyyy')"><img src="../images/cal.gif" width="16" height="16" border="0" align="bottom"></a>&nbsp;</font></span></td>
                          <td valign="bottom" class="text-small"> <span class="text-xsmall"> 
                            <font color="#666666"> </font><font color="#333333"><span class="text-xsmall">If 
                            adjustment/change is needed, click on calendar</span></font></span></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="300" size="1"></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"> <table width="550" height="50" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="305" valign="bottom" class="text-medium"><strong>Date 
                            of Last Completed Claims Report<font color="#333333"> 
                            :</font></strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Claims").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"><table width="550" height="50" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="125" valign="bottom"><input name="up_claims" type="text" class="box-grey-date" id="up_claims" size="8"> 
                            <font color="#666666"><a href="javascript:NewCal('up_claims','mmddyyyy')"><img src="../images/cal.gif" width="16" height="16" border="0" align="bottom"></a>&nbsp;</font></td>
                          <td valign="bottom"><span class="text-xsmall"><font color="#333333"><span class="text-xsmall">If 
                            adjustment/change is needed, click on calendar</span></font></span></td>
                        </tr>
                      </table>
                      <strong></strong></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="300" size="1"></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"> <table width="550" height="50" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="305" valign="bottom" class="text-medium"><strong>Oldest 
                            Report to be Processed<font color="#333333"> :</font></strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Processed").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="30" valign="bottom"><strong></strong></td>
                  </tr>
                  <tr> 
                    <td height="30"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                        <tr> 
                          <td width="125" valign="bottom" class="text-med-lg"><strong> 
                            <input name="up_processed" type="text" class="box-grey-date" id="up_processed" size="8">
                            <span class="text-xsmall"> <font color="#666666"><a href="javascript:NewCal('up_processed','mmddyyyy')"><img src="../images/cal.gif" width="16" height="16" border="0" align="bottom"></a>&nbsp;</font></span></strong></td>
                          <td valign="bottom"> <span class="text-xsmall"><font color="#666666"> 
                            </font><font color="#333333"><span class="text-xsmall">If 
                            adjustment/change is needed, click on calendar</span></font></span></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="300" size="1"></td>
                  </tr>
                  <tr> 
                    <td valign="bottom"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0">
                        <tr> 
                          <td width="305" valign="bottom" class="text-medium"><strong>Oldest 
                            Paper document to be Processed<font color="#333333"> 
                            :</font></strong></td>
                          <td valign="bottom" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Paper").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td> <table width="550" height="50" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                        <tr> 
                          <td width="127" valign="bottom" class="text-med-lg"><strong> 
                            <input name="up_paper" type="text" class="box-grey-date" id="up_paper" size="8">
                            <span class="text-xsmall"> <font color="#666666"><a href="javascript:NewCal('up_paper','mmddyyyy')"><img src="../images/cal.gif" width="16" height="16" border="0" align="bottom"></a></font></span></strong></td>
                          <td width="423" valign="bottom"> <span class="text-xsmall"><font color="#333333"><span class="text-xsmall">If 
                            adjustment/change is needed, click on calendar</span></font></span></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="250" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="30"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                        <tr> 
                          <td width="125" valign="bottom" class="text-medium"><strong>OT 
                            Hours : </strong></td>
                          <td valign="bottom"><strong><font color="#990000"><%=(rs.Fields.Item("OT").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="250" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="30"><table width="550" height="30" border="0" cellpadding="0" cellspacing="0" class="text-medium">
                        <tr> 
                          <td width="125" valign="bottom" class="text-medium"><strong>On 
                            Leave : </strong></td>
                          <td valign="bottom"><strong><font color="#990000"><%=(rs.Fields.Item("Leave").Value)%></font></strong></td>
                        </tr>
                      </table></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="250" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="30" valign="bottom"><strong>Employee's Comment(s) 
                      :</strong></td>
                  </tr>
                  <tr> 
                    <td height="30" class="text-medium"><strong><font color="#990000"><%=(rs.Fields.Item("Note1").Value)%></font></strong></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="250" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="30" valign="bottom"><strong>Would you like to 
                      add a note to the report?</strong></td>
                  </tr>
                  <tr> 
                    <td height="30" valign="bottom"> <select name="AddNote2" class="box-grey-date" id="AddNote2" onchange="process(this.options[selectedIndex].value)">
                        <option value="">No</option>
                        <option value="Yes">Yes</option>
                      </select> &nbsp; <input name="Note2" type="text" class="text-med-lg" id="Note2" style="display:none;" size="30" maxlength="35"> 
                      <strong> 
                      <input name="IDX" type="hidden" id="IDX" value="<%=(rs.Fields.Item("ID").Value)%>">
                      </strong></td>
                  </tr>
                  <tr> 
                    <td height="25" valign="bottom"> <hr align="left" width="250" size="1"></td>
                  </tr>
                  <tr> 
                    <td height="35" class="text-med-lg"><strong><font color="#003366">If 
                      you made any changes or updates, please click the Update 
                      button to process</font></strong></td>
                  </tr>
                  <tr> 
                    <td height="30"><input name="Update" type="submit" class="text-med-lg" id="Update2" value="Update"> 
                      &nbsp; <input name="Clear" type="reset" class="text-med-lg" id="Clear" value="Clear"> 
                      &nbsp; <input type="button" class="text-med-lg" onClick="location.href='search_supervisor_cm.asp';" value="Go Back"> 
                      &nbsp; <input name="button" type="button" class="text-med-lg" onClick="location.href='search_supervisor_cm.asp';" value="Delete Record"></td>
                  </tr>
                  <tr> 
                    <td>&nbsp;</td>
                  </tr>
                </table>
              </form>
              <% End If ' end Not rs.EOF Or NOT rs.BOF %> </td>
          </tr>
          <tr> 
            <td></td>
          </tr>
        </table></td>
    </tr>
  </table>
  <p align="left" class="box-grey">&nbsp;</p>
</div>
</body>
</html>

Open in new window

0
Comment
Question by:arendt73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 35213650
you just need to execute an sql delete command in one of your sql options:

like this bit:
sql="UPDATE TPACM SET " & Mid(sql, 2, 10000)  ' leave out first comma
sql=sql & " WHERE ID = " & pIDX& "" 
response.write sql
rs.Execute sql

but
sql="DELETE TPACM <or correct tabe name>
sql=sql & " WHERE ID = " & pIDX& "" < or correct where clause if this is not the correct field...
response.write sql
rs.Execute sql
0
 

Author Comment

by:arendt73
ID: 35233470
I added a "Delete Record" button at the bottom of the page as well as entered the suggested code to delete the current record.  When I hit the Delete Record key, I should be redirected to a "thank you" page.  However, the page refreshes and the record still remains in the database.

Attached is the updated code with suggestion from Sudonim.  I'm sure I'm missing a piece of code or a As If and End somewhere.  Please advise any recommendations.  Thank you.
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim rs
Dim rs_numRows

pID=Request.Querystring("ID")
pIDX=request.form("IDX")

Set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

If Request.Form("ReportDate2")&"" = "" then
rs.Source = "SELECT * FROM TPACM WHERE Employee = '" & Replace(Request.Form("Employee"),"'","''") & "' " & pID
else
rs.Source = "SELECT * FROM TPACM WHERE ReportDate = #" & Request.Form("ReportDate2") & "# AND Employee = '" & Replace(Request.Form("Employee"),"'","''") & "' " & pID
End If

rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0

if request("Update") = "Update" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

sql=""
If Request.Form("up_Manager") & "" <> "" Then
  sql=sql & ",Manager='" & Request.Form("up_Manager") & "'"
End IF
If Request.Form("up_reportdate") & "" <> "" Then
  sql=sql & ",ReportDate='" & Request.Form("up_reportdate") & "'"
End IF
If Request.Form("up_docket") & "" <> "" Then
  sql=sql & ",Docket='" & Request.Form("up_docket") & "'"
End IF
If Request.Form("up_claims") & "" <> "" Then
sql=sql & ",Claims='" & Request.Form("up_claims") & "'"
End IF
If Request.Form("up_processed") & "" <> "" Then
sql=sql & ",Processed='" & Request.Form("up_processed") & "'"
End IF
If Request.Form("up_paper") & "" <> "" Then
sql=sql & ",Paper='" & Request.Form("up_paper") & "'"
End IF
If Request.Form("AddNote2") & "" <> "" Then
sql=sql & ",AddNote2='" & Request.Form("AddNote2") & "'"
End IF
If Request.Form("Note2") & "" <> "" Then
sql=sql & ",Note2='" & Request.Form("Note2") & "'"
End IF

sql="UPDATE TPACM SET " & Mid(sql, 2, 10000)  ' leave out first comma
sql=sql & " WHERE ID = " & pIDX& "" 
response.write sql 
rs.Execute sql

End If

if request("Delete") = "Delete" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

sql="DELETE TPACM SET " & Mid(sql, 2, 10000)
sql=sql & " WHERE ID = " & pIDX& "" 
response.write sql 
rs.Execute sql 
      
response.redirect "thankyou1.htm"   

end if

%>

Open in new window

0
 

Author Comment

by:arendt73
ID: 35318980
EE, should I just give up on my request?  I assume this is not possible what i"m trying to accomplish?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 15

Assisted Solution

by:Simon Ball
Simon Ball earned 2000 total points
ID: 35321465
sorry i have been on holiday :)

is this being hit:
if request("Delete") = "Delete" Then

is the response.write sql
appearing on screen?

0
 

Author Comment

by:arendt73
ID: 35323539
Yes, the sql appears along with the error. What do I to avoid the error? Comment out the response.write?

DELETE TPACM SET WHERE ID = 188

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'TPACM SET WHERE ID = 188'.

/statusreports/results-cm2-test.asp, line 71
0
 

Author Comment

by:arendt73
ID: 35323749
I made an error and applied the code as suggested.  I receive this message upon Delete:

DELETE TPACM WHERE ID = 188

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'TPACM WHERE ID = 188'.

/statusreports//results-cm2-test.asp, line 71

I have attached the code for verification.
<%@LANGUAGE="VBSCRIPT"%>
<%
Dim rs
Dim rs_numRows

pID=Request.Querystring("ID")
pIDX=request.form("IDX")

Set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

If Request.Form("ReportDate2")&"" = "" then
rs.Source = "SELECT * FROM TPACM WHERE Employee = '" & Replace(Request.Form("Employee"),"'","''") & "' " & pID
else
rs.Source = "SELECT * FROM TPACM WHERE ReportDate = #" & Request.Form("ReportDate2") & "# AND Employee = '" & Replace(Request.Form("Employee"),"'","''") & "' " & pID
End If

rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0

if request("Update") = "Update" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

sql=""
If Request.Form("up_Manager") & "" <> "" Then
  sql=sql & ",Manager='" & Request.Form("up_Manager") & "'"
End IF
If Request.Form("up_reportdate") & "" <> "" Then
  sql=sql & ",ReportDate='" & Request.Form("up_reportdate") & "'"
End IF
If Request.Form("up_docket") & "" <> "" Then
  sql=sql & ",Docket='" & Request.Form("up_docket") & "'"
End IF
If Request.Form("up_claims") & "" <> "" Then
sql=sql & ",Claims='" & Request.Form("up_claims") & "'"
End IF
If Request.Form("up_processed") & "" <> "" Then
sql=sql & ",Processed='" & Request.Form("up_processed") & "'"
End IF
If Request.Form("up_paper") & "" <> "" Then
sql=sql & ",Paper='" & Request.Form("up_paper") & "'"
End IF
If Request.Form("AddNote2") & "" <> "" Then
sql=sql & ",AddNote2='" & Request.Form("AddNote2") & "'"
End IF
If Request.Form("Note2") & "" <> "" Then
sql=sql & ",Note2='" & Request.Form("Note2") & "'"
End IF

sql="UPDATE TPACM SET " & Mid(sql, 2, 10000)  ' leave out first comma
sql=sql & " WHERE ID = " & pIDX& "" 
response.write sql 
rs.Execute sql

End If

if request("Delete") = "Delete" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\statusreports\db\statusreports.mdb"

sql="DELETE TPACM "
sql=sql & " WHERE ID = " & pIDX& ""
response.write sql 
rs.Execute sql 
      
response.redirect "thankyou1.htm"   

end if

%>

Open in new window

0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35324479
hmm.

can you run that sql in your database server console and see if it does the delete.

might need

delete TPACM from TPACM where id = 188

also,

does the username you are connected to the server with have relevant permissions to delete from the table?
0
 

Author Comment

by:arendt73
ID: 35324618
I am using MS Access.  Not sure how to run the sql on the console.

There are no permission limitations.

How do I add the extra deletion of the table (TPACM)?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35324676
can you write a query in access to make the same delete, then check the sql in the query.

and make your code execute the same code, with the value inserted.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35372774
did you try the query code?
0
 

Author Comment

by:arendt73
ID: 35375709
I am not familiar with writing a query in Access. I'm still trying to figure it out. Once I do, I'll attempt your suggestion.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35376034
ok.

you should be able to go to access create, query... then select the table, select query tyoe delete,

select the column you want for your where clause and write the value in the criteria area.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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