Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Unable to delete records - Classic ASP

Hi Experts,
I'm trying to delete some records from two different tables.
This is my form:
<form action="DeleteRecord.asp" method="POST">
<input type="hidden" name="Emp_Id" value="<%=rs1("Emp_Id")%>">
<input type="submit" name="Submit" value="Delete" />

After I submit I get the following error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid column name 'B4537'.

B4537 is the employee number I'm trying to delete from both tables. This is the code of DeleteRecord.asp

Dim conn
Dim strSQL
Dim myId

myId = Request.Form("Emp_Id")

strSQL = "DELETE FROM Employees1 WHERE Emp_Id=" & myId
conn.Execute strSQL

strSQL = "DELETE FROM Training_Completion1 WHERE Emp_Id=" & myId
conn.Execute strSQL

Set conn = Nothing

Response.Redirect "EmployeeSearch.asp"

Open in new window

I would appreciate your help
1 Solution
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
your code there is fine, I suspect the error is coming from somewhere else. what is the EXACT error you're getting on the screen?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
WHERE Emp_Id=" & myId
Is not correct as empid is not numerical field
This will fix the issue
WHERE Emp_Id='" & myId  & "'"

After this, I warmly recommend you read up about sql injection, and as main solutions to validate user input + using parameterized sql
hmm don't you have to select the Employee column first?
is there more code?

this link shows a complete example of deleting

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

romsomAuthor Commented:
Thank you for your quick response
The problem is that you need to encapsulate your Emp_ID field with single quotes (as it is a string value and not numeric), HOWEVER I would also recommend you do at least some rudimentary stripping of possible sql injection characters at the same time.

Here is a staple that i always use:

1. Place this function in your connection include file (if you have one) or just in your ASp page somewhere:

      'SQL injection stopper (replace ' and ; and surround with '')
      If IsEmpty(SQLCMD) Or IsNull(SQLCMD) Then
            CSQL = "''"
            CSQL = "'" & Trim(Replace(Replace(Replace(SQLCMD, "'", "''"), chr(34), "''"),";","")) & "'"
      End If
End Function

2. Use this syntax for your delete query rather than what you have now:

strSQL = "DELETE FROM Employees1 WHERE Emp_Id=" & CSQL(myId)

That's it, you're done!
romsomAuthor Commented:
worthyking1 - Thank you very much; I've tried your code and it's also working.
I'm sorry, but I read it only after I closed this question.
NP, glad I could help :)

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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