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
romsomIT DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter HartCommented:
hmm don't you have to select the Employee column first?
is there more code?

this link shows a complete example of deleting

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

romsomIT DeveloperAuthor 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!
romsomIT DeveloperAuthor 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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.