Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

How to delete a specific record on a results page

This results page contains information about a worker. I added a DELETE button where if the supervisor wants to delete the record, they have the ability to do so.

I am getting the following error after the DELETE button is pressed:

DELETE TPACM WHERE ID = 257

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

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

The error message also mentions line 77 as the area where the issue is occuring (rs.Execute sql ).

<%@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_boss") & "" <> "" Then
  sql=sql & ",boss='" & Request.Form("up_boss") & "'"
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("up_OT") & "" <> "" Then
sql=sql & ",OT='" & Request.Form("up_OT") & "'"
End IF
If Request.Form("up_leave") & "" <> "" Then
sql=sql & ",Leave='" & Request.Form("up_leave") & "'"
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


Why is this not working? Updates on the same page work without issue. But if I want to delete the record, I cannot.  

Any assistance is greatly appreciated.

Thank you.
0
arendt73
Asked:
arendt73
1 Solution
 
pteranodon72Commented:
Try changing the sql to result in:
DELETE * FROM TPACM WHERE ID = 257


sql="DELETE * FROM TPACM"
sql=sql & " WHERE ID = " & pIDX & ""
response.write sql
rs.Execute sql


HTH,
pT72
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now