Unable to delete records - Classic ASP

Posted on 2014-08-15
Last Modified: 2014-08-15
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
Question by:romsom
    LVL 32

    Expert Comment

    by:Big Monty
    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?
    LVL 142

    Accepted Solution

    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
    LVL 28

    Expert Comment

    hmm don't you have to select the Employee column first?
    is there more code?

    this link shows a complete example of deleting

    Author Closing Comment

    Thank you for your quick response
    LVL 6

    Expert Comment

    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:

    Function CSQL(SQLCMD)
          '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!

    Author Comment

    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.
    LVL 6

    Expert Comment

    NP, glad I could help :)

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now